rules repository

G-9601

🆓
Warning

Never use unknown hints.

Hint Check

Reason

Using unknown hints might invalidate all subsequent hints. This happens when you use for example NOLOGGING. That's expected and not a bug. See MOS note 285285.1 or bug 8432870 for details.

Example

Non-Compliant Example

insert --+ nologging append
  into sales_hist
select * from sales;

select * from dbms_xplan.display_cursor(format => 'basic');

/*
-----------------------------------------------
| Id  | Operation                | Name       |
-----------------------------------------------
|   0 | INSERT STATEMENT         |            |
|   1 |  LOAD TABLE CONVENTIONAL | SALES_HIST |
|   2 |   PARTITION RANGE ALL    |            |
|   3 |    TABLE ACCESS FULL     | SALES      |
-----------------------------------------------
*/
Issues
LineColumnMessage
112Using unknown nologging hint.

nologging is not a hint. It does not exist in v$sql_hint. The append hint is ignored. As a result LOAD TABLE CONVENTIONAL is applied.

Compliant Solution - ★★★★★

alter table sales_hist nologging;

insert --+ append
  into sales_hist
select * from sales;

select * from dbms_xplan.display_cursor(format => 'basic');

/*
-------------------------------------------------------
| Id  | Operation                        | Name       |
-------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |
|   1 |  LOAD AS SELECT                  | SALES_HIST |
|   2 |   OPTIMIZER STATISTICS GATHERING |            |
|   3 |    PARTITION RANGE ALL           |            |
|   4 |     TABLE ACCESS FULL            | SALES      |
-------------------------------------------------------
*/

nologging is applied on the table, however this is in most environments overridden by the force logging option on database level. The append hint is used. As a result LOAD AS SELECT is applied.

Parameters

Use parameters to customize the rule to your needs.

ParameterDescriptionDefault Value
KnownHintsComma-separated list of know hints in Oracle AI Database 26ai Free Release 23.26.0.0.0.
force_xml_query_rewrite, dml_update, ordered_predi... (click to expand)
force_xml_query_rewrite, dml_update, ordered_predicates, old_push_pred, star_transformation, rewrite_or_error, no_set_to_join, cube_gb, no_buffer, model_compile_subquery, no_use_hash_aggregation, no_stats_gsets, merge, shared, driving_site, queue_rowp, index, no_index, index_rrs, restrict_all_ref_cons, tracing, av_cache, xml_dml_rwt_stmt, cost_xml_query_rewrite, connect_by_elim_dups, statement_queuing, connect_by_cb_whr_only, no_semi_to_inner, no_pq_replicate, outer_join_to_anti, partial_rollup_pushdown, vector_transform, order_subq, fresh_mv, no_or_expand, no_use_partition_wise_distinct, no_use_partition_wise_gby, no_reorder_wif, no_json_table_transform, analytic_view_sql, order_key_vector_use, no_subsume, cell_trace, no_compress_immediate, no_unpivot_trans, load_method, ivf_iteration, no_ivf_iteration, timehouse_scn, external_table_cache, no_use_band, parallel, append, deref_no_rewrite, sqlldr, vector_read_trace, no_query_transformation, db_version, skip_ext_optimizer, push_pred, no_outer_join_to_inner, no_semijoin, merge_sj, overflow_nomove, connect_by_cost_based, expand_gset_to_union, model_min_analysis, model_no_analysis, use_merge, no_subquery_pruning, pq_map, index_ss, index_join, cluster, skip_unq_unusable_idx, precompute_subquery, outline, no_domain_index_filter, no_xml_dml_rewrite, place_group_by, no_place_group_by, index_rs_desc, no_load, no_connect_by_combine_sw, xmlindex_rewrite_in_select, no_expand_table, pq_replicate, no_full_outer_join_to_outer, px_fault_tolerance, use_hidden_partitions, bitmap_and, disable_parallel_dml, no_gather_optimizer_statistics, vector_transform_dims, no_elim_groupby, no_adaptive_plan, no_inmemory, use_dagg_union_all_gsets, no_push_having_to_gby, oson_get_content, no_subgroup, no_push_gby_into_union_all, end_outline_data, no_sql_tune, vector_read, merge_const_on, table_stats, rule, first_rows, domain_index_no_sort, gby_pushdown, no_expand, antijoin, merge_aj, semijoin, bypass_recursive_check, scn_ascending, model_dynamic_subquery, use_hash_aggregation, cardinality, cache_cb, use_merge_cartesian, index_ss_asc, no_index_ss, include_version, no_cartesian, outline_leaf, dbms_stats, result_cache, no_result_cache, check_acl_rewrite, no_xmlindex_rewrite_in_select, native_full_outer_join, no_use_invisible_indexes, no_transform_distinct_agg, xdb_fastpath_insert, table_lookup_by_nl, no_zonemap, pq_skew, no_auto_reoptimize, adaptive_plan, inmemory_pruning, no_bushy_join, system_stats, current_instance, no_set_gby_pushdown, no_pq_nonleaf_skew, subsume, no_json_qryovergen_rewrite, compress_immediate, unpivot_trans, chunk_pruning, noappend, preserve_oid, opaque_transform, optimizer_features_enable, no_cpu_costing, eliminate_oby, eliminate_join, nl_sj, star, remote_mapped, no_multimv_rewrite, sys_parallel_txn, inline, no_prune_gsets, no_order_rollups, qb_name, full, no_use_hash, no_index_ffs, fact, x_dyn_prune, index_rs_asc, nlj_prefetch, no_nlj_prefetch, retry_on_row_change, no_coalesce_sq, dst_upgrade_insert_conv, no_statement_queuing, xmlindex_sel_idx_tbl, xmltset_dml_enable, place_distinct, use_hash_gby_for_pushdown, data_security_rewrite_limit, use_cube, no_use_cube, cube_sj, semi_to_inner, no_pq_concurrent_union, vector_transform_fact, ansi_rearch, no_inmemory_pruning, bushy_join, containers, use_partition_wise_distinct, use_partition_wise_gby, reorder_wif, json_length, use_scalable_gby_invdist, pq_expand_table, set_gby_pushdown, multi_append, vector_index_scan, load_type, oby_gbyvw_separate, begin_outline_data, use_band, nested_table_get_refs, nested_table_set_setid, ref_cascade_cursor, no_xml_query_rewrite, ignore_where_clause, opaque_xcanonical, ordered, pull_pred, no_eliminate_oby, no_push_subq, semijoin_driver, no_rewrite, no_basetable_multimv_rewrite, hwm_brokered, sys_rid_order, tiv_ssf, save_as_intervals, dynamic_sampling, leading, no_merge, nocache, use_hash, use_nl_with_index, no_use_nl, pq_nomap, and_equal, index_combine, no_parallel_index, no_swap_join_inputs, no_check_acl_rewrite, connect_by_combine_sw, no_xmlindex_rewrite, monitor, no_monitor, no_bind_aware, ignore_row_on_dupkey_index, no_place_distinct, no_use_hash_gby_for_pushdown, cube_aj, batch_table_access_by_rowid, no_px_fault_tolerance, with_plsql, no_ansi_rearch, reservoir_sampling, dist_agg_prollup_pushdown, sql_scope, memoptimize_write, skip_proxy, no_use_scalable_gby_invdist, pq_nonleaf_skew, no_dagg_optim_gsets, no_dr_in_recur, vector_index_transform, plsql_function_dynamic_stats, no_external_table_cache, noparallel_index, nested_table_fast_insert, no_ref_cascade, expr_corr_check, streams, fbtscan, gather_plan_statistics, ignore_optim_embedded_hints, index_stats, column_stats, choose, all_rows, outer_join_to_inner, nl_aj, hash_sj, no_star_transformation, bypass_ujvc, unnest, local_indexes, connect_by_filtering, piv_gb, tiv_gb, buffer, cursor_sharing_exact, mv_merge, no_expand_gset_to_union, no_model_push_ref, queue_curr, use_nl, bitmap_tree, hash, swap_join_inputs, no_access, opt_param, no_native_full_outer_join, nlj_batching, change_dupkey_error_index, transform_distinct_agg, factorize_join, append_values, no_xdb_fastpath_insert, no_table_lookup_by_nl, pq_concurrent_union, no_clustering, zonemap, full_outer_join_to_outer, no_outer_join_to_anti, decorrelate, no_decorrelate, no_batch_table_access_by_rowid, no_pq_skew, no_partial_rollup_pushdown, pq_distribute_window, no_cluster_by_rowid, inmemory, no_eliminate_sq, or_expand, no_use_dagg_union_all_gsets, pdb_local_only, suppress_load, denorm_av, no_oby_gbypd_separate, push_gby_into_union_all, no_join_specific_mv, json_qryovergen_rewrite, no_multi_append, cursor_sharing_force, maintain_rowid_mapping_table, sys_dl_cursor, use_weak_name_resl, hash_aj, rewrite, no_connect_by_cost_based, no_connect_by_filtering, piv_ssf, restore_as_intervals, no_qkn_buff, model_push_ref, use_ttt_for_gsets, gby_conc_rollup, cache, use_anti, subquery_pruning, index_desc, no_partial_commit, rbo_outline, domain_index_filter, num_index_keys, xmlindex_rewrite, no_cost_xml_query_rewrite, coalesce_sq, no_connect_by_cb_whr_only, no_factorize_join, no_substrb_pad, pq_filter, enable_parallel_dml, cluster_by_rowid, no_vector_transform_fact, no_vector_transform_dims, use_vector_aggregation, no_use_vector_aggregation, elim_groupby, eliminate_sq, use_hash_gby_for_daggpshd, no_use_hash_gby_for_daggpshd, use_partition_wise_wif, answer_query_using_stats, no_pq_expand_table, oby_gbypd_separate, json_reference_for_update, no_vector_index_scan, no_monitoring, inline_xmltype_nt, domain_index_sort, cpu_costing, no_push_pred, no_pull_pred, no_eliminate_join, push_subq, no_gby_pushdown, use_concat, no_unnest, materialize, set_to_join, model_dontverify_uniqueness, noparallel, no_parallel, no_use_merge, use_semi, pq_distribute, index_asc, index_ffs, index_ss_desc, parallel_index, no_fact, dynamic_sampling_est_cdn, rowid, opt_estimate, px_join_filter, no_px_join_filter, no_nlj_batching, use_invisible_indexes, bind_aware, no_connect_by_elim_dups, no_dst_upgrade_insert_conv, expand_table, no_data_security_rewrite, partial_join, no_partial_join, clustering, auto_reoptimize, gather_optimizer_statistics, no_vector_transform, data_validate, no_dist_agg_prollup_pushdown, no_use_partition_wise_wif, push_having_to_gby, no_answer_query_using_stats, quarantine, force_json_table_transform, hashset_build, dagg_optim_gsets, subgroup, join_specific_mv, dr_in_recur, no_chunk_pruning, no_vector_index_transform, no_oby_gbyvw_separate
DisableAllQuickFixComma-separated list of rules for which a quick fix should not be applied to all the problems in a file.Core G-3130

References