Shulou(Shulou.com)06/01 Report--

Hint related to the optimizer pattern

[plain] view plain copy1.1 / * + all_rows * / Let the optimizer launch CBO1.2 / * + first_rows (n) * / the optimizer starts CBO, and select the execution plan that will quickly return the first n rows of data. Unlike first_rows_n, first_rows_n n can only start RBO for 1Magne10 hint 1000.1.3 / * + rule * /. When used with other hint, other hint will generally fail.

Hint2.1 full (xxx) full table scan associated with table and index access 2.2 index (target table target index 1 target index 2) 2.3 no_index (target table target index 1 target index 2) 2.4 index_desc (target table target index 1 target index 2) lets the optimizer perform a descending scan on the target index, and if the target index is descending, hint scans the target index in ascending order. 2.5 index_ffs (target table target index 1 target index 2) index fast full scan

Hint3.1 ordered, which is related to table join order, allows the optimizer to join in the order in which their where conditions appear in sql when performing multi-table joins. Query transformation may invalidate this hint. 3.2 leading (Table 1, Table 2) asks the optimizer to take the join result of our execution of multiple tables as the result set during the join of the target sql table, and the first table from left to right in hint as the table join driver.

The hint4.1 use_merge associated with the table join method (Table 1, Table 2) allows the optimizer to sort and join multiple tables we specify as driven tables with other tables or result sets. 4.2 no_use_merge (Table 1, Table 2) 4.3 use_nl (Table 1, Table 2) lets the optimizer join multiple tables we specify as driven tables in a nested loop with other tables or result sets. Often used with leading. / * + use_nl (sQuery X) leading X * / 4.4 no_use_merge (Table 1, Table 2) 4.5 use_hash (Table 1, Table 2) lets the optimizer hash the multiple tables we specify as driven tables with other tables or result sets. Often used with leading. 4.6 no_use_merge (Table 1, Table 2) 4.7 merge_aj targets the hint of the subquery, allowing the optimizer to sort and disjoin the target table. Nl_aj directs the hint of the subquery to allow the optimizer to perform nested loop disjoins on the target table. 4.9 hash_aj directs the hint of the subquery to let the optimizer perform hash disjoins on the target table. 4.10 merge_sj targets the hint of the subquery and lets the optimizer perform a sort merge semi-join on the target table. 4.11 nl_sj targets the hint of the subquery and lets the optimizer perform a nested loop semi-join on the target table. 4.12 hash_sj targets the hint of the subquery and lets the optimizer perform a hash semi-join on the target table

The hint5.1 use_concat associated with query transformation is the hint for the target sql, allowing the optimizer to use in_list or or_list extensions on the target sql. 5.2 no_expand is the use_concat antisense hint, which prevents the optimizer from using in_list or or_list extensions for the target sql. 5.3.The merge is a hint for a single target view, which allows the optimizer to perform view merging on the target view (view merging) 5.4. no_merge is an antisense hint for merge, but prevents the optimizer from performing view merging (view merging) 5.5.5.The unnest is an hint for subqueries and allows the optimizer to subquery unnesting subqueries in the target sql. 5.6no_unnest is an hint for unnest antisense. Do not let the optimizer expand the subquery in the target sql (subquery unnesting) 5.7 expand_table (table) Let the optimizer extend the table 5.8 no_expand_table (table) expand_table (table) antisense hint without considering the cost, and do not extend the table

Parallelism-related hint6.1 parallel parallel 6.2 no_parallel hint6.3 parallel_index for the entire target sql (table index-1 index-2. Index-n n n n) 6.4 no_parallel_index (Table index-1 index-2. Index-n)

Other common hint7.1 driving_site lets the optimizer execute the target sql on the node where we specified the target table. Applies only to distributed query statements with dblink. Append lets the optimizer bypass the buffer cache and use direct path insertion when executing the insert of the tape query. 7.3.When append_values lets the optimizer execute insert with values, bypass buffer cache and use direct path insertion. (11R2) 7.4 push_pred lets the optimizer perform join predicate push on the target view for the hint of the target view. No_push_pred prevents the optimizer from performing join predicate push on the target view for the hint of the target view. 7.6 push_subq aims at the hint of the subquery to let the optimizer execute the subquery in the target sql that cannot be expanded by the subquery as soon as possible. 7.7 no_push_subq targets the hint of the subquery so that the optimizer finally executes the subquery that cannot be expanded by the subquery in the target sql. Opt_param for the hint of the target sql is used to modify changes to the finer particles of the target sql than at the system level and session level. 7.9 optimizer_features_enable ('optimizer version number') changes optimizer version 7.10 qb_name assigns a custom name to a query block. 7.11 cardinality is used to set the value of cardinality after scanning the target table for the hint of a single target table. (not valid for unique index scans) 7.12 swap_join_inputs for hint of hash connections, lets the optimizer exchange the order of the driven and driven tables of the original hash connection.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.