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