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

1. Hint related to table join methods

1 、 USE_MERGE

USE_MERGE is a Hint for multiple target tables, which means that the optimizer joins multiple tables we specify as driven tables with other tables or result sets. The target table specified in USE_MERGE Hint should be a driven table for sort merge joins, and if the specified table cannot be used as a driven table for sort merge joins, Oracle will either ignore the Hint or ignore the table.

The format is as follows:

/ * + USE_MERGE (target table 1, target table 2... Target Table n) * /

/ * + USE_MERGE (target table 1, target table 2,... , target table n) * /

Examples of use:

Select / * + use_merge (emp) * / *

From emp,dept

Where emp.deptno=dept.deptno

Select / * + use_merge (e j d t) * / e.enamekinj.jobpene.salred.deptno

From emp e,jobs j,dept d,emp_temp t

Where e.empno=j.empno

And e.deptno=d.deptno

And d.lockedCHICAGO'

And e.ename=t.ename

Order by e.ename

The execution plan joins tables EMP, DEPT, JOBS, and EMP_TEMP with sort and merge joins, which indicates that USE_MERGE Hint is already in effect. The table EMP specified in Hint cannot be used as a driven table for sort merge joins, but the other three tables DEPT, JOBS, and EMP_TEMP specified in the above Hint can be used as driven tables for sort merge joins, so Oracle only ignores table EMP, not USE_MERGE Hint.

Precisely because Oracle may ignore USE_MERGE Hint or the driven table specified in it, we usually use LEADING Hint (or ORDERED Hint) with USE_MERGE Hint to get the optimizer out of our desired execution plan.

Select / * + leading (e) use_merge (j d t) * / e.enamerej.jobree.salred.deptno

From emp e,jobs j,dept d,emp_temp t

Where e.empno=j.empno

And e.deptno=d.deptno

And d.lockedCHICAGO'

And e.ename=t.ename

Order by e.ename

2 、 NO_USE_MERGE

NO_USE_MERGE is the Hint for multiple target tables, and it is the antisense Hint of USE_MERGE, which means that the optimizer does not allow the optimizer to sort and merge the multiple tables we specify as driven tables with other tables or result sets.

The target table specified in NO_USE_MERGE Hint should be the driven table that was originally in the sort merge join, otherwise Oracle will either ignore the NO_USE_MERGE Hint or ignore the table. Precisely because Oracle may ignore NO_USE_MERGE Hint or the driven table specified in it, we usually use LEADING Hint (or ORDERED Hint) with NO_USE_MERGE Hint to get the optimizer out of our desired execution plan.

The format is as follows:

/ * + NO_USE_MERGE (target table 1, target table 2... Target Table n) * /

/ * + NO_USE_MERGE (target table 1, target table 2,... , target table n) * /

Examples of use:

Select / * + no_use_merge (emp) * / *

From emp,dept

Where emp.deptno=dept.deptno

Select / * + no_use_merge (e j d t) * / e.enamekinj.jobpene.salred.deptno

From emp e,jobs j,dept d,emp_temp t

Where e.empno=j.empno

And e.deptno=d.deptno

And d.lockedCHICAGO'

And e.ename=t.ename

Order by e.ename

3 、 USE_NL

USE_NL is a Hint for multiple target tables, which means that the optimizer joins multiple tables we specify as drivers with other tables or result sets in a nested loop. The target table specified in USE_NL Hint should be a driven table in a nested loop join, otherwise Oracle will either ignore the USE_NL Hint or ignore the table. Precisely because Oracle may ignore USE_NL Hint or the driven table specified in it, we usually use LEADING Hint (or ORDERED Hint) with USE_NL Hint to get the optimizer out of our desired execution plan.

The format is as follows:

/ * + USE_NL (target table 1, target table 2... Target Table n) * /

/ * + USE_NL (target table 1, target table 2,... , target table n) * /

Examples of use:

Select / * + use_nl (dept) * / *

From emp,dept

Where emp.deptno=dept.deptno

Select / * + use_nl (e j d t) * / e.enamekinj.jobpene.salred.deptno

From emp e,jobs j,dept d,emp_temp t

Where e.empno=j.empno

And e.deptno=d.deptno

And d.lockedCHICAGO'

And e.ename=t.ename

Order by e.ename

Users of USE_NL Hint are used the same way as USE_MERGE Hint.

4 、 NO_USE_NL

NO_USE_NL is a Hint for multiple target tables, which is the antisense Hint of USE_NL, which means that the optimizer does not allow the optimizer to join multiple table joins as driven tables with other tables or result sets as nested loops. The target table specified in NO_USE_NL Hint should be a driven table in a nested loop join, otherwise Oracle will either ignore the NO_USE_NL Hint or ignore the table. Precisely because Oracle may ignore NO_USE_NL Hint or the driven table specified in it, we usually use LEADING Hint (or ORDERED Hint) with NO_USE_NL Hint to get the optimizer out of our desired execution plan.

The format is as follows:

/ * + NO_USE_NL (target table 1, target table 2... Target Table n) * /

/ * + NO_USE_NL (target table 1, target table 2,... , target table n) * /

Examples of use:

Select / * + no_use_nl (dept) * / *

From emp,dept

Where emp.deptno=dept.deptno

Select / * + no_use_nl (e j d t) * / e.enamekinj.jobpene.salred.deptno

From emp e,jobs j,dept d,emp_temp t

Where e.empno=j.empno

And e.deptno=d.deptno

And d.lockedCHICAGO'

And e.ename=t.ename

Order by e.ename

Users of NO_USE_NL Hint are used the same way as NO_USE_MERGE Hint.

5 、 USE_HASH

USE_HASH is a Hint for multiple target tables, which means that the optimizer joins multiple tables we specify as driven tables with other tables or result sets. The target table specified in USE_HASH Hint should be the driven table in the hash join, otherwise Oracle will either ignore the USE_HASH Hint or ignore the table. Precisely because Oracle may ignore USE_HASH Hint or the driven table specified in it, we usually use LEADING Hint (or ORDERED Hint) with USE_HASH Hint to get the optimizer out of our desired execution plan.

The format is as follows:

/ * + USE_HASH (target table 1, target table 2... Target Table n) * /

/ * + USE_HASH (target table 1, target table 2,... , target table n) * /

Examples of use:

Select / * + use_hash (emp) * / *

From emp,dept

Where emp.deptno=dept.deptno

Select / * + use_hash (e j d t) * / e.enamekinj.jobpene.salred.deptno

From emp e,jobs j,dept d,emp_temp t

Where e.empno=j.empno

And e.deptno=d.deptno

And d.lockedCHICAGO'

And e.ename=t.ename

Order by e.ename

Users of USE_HASH Hint are used the same way as USE_MERGE Hint.

6 、 NO_USE_HASH

NO_USE_HASH is the Hint for multiple target tables, and it is the antisense Hint of HASH, which means that the optimizer does not allow the optimizer to hash the multiple tables we specify as driven tables with other tables or result sets. The target table specified in NO_USE_HASH Hint should be the driven table in the hash join, otherwise Oracle will either ignore the NO_USE_HASH Hint or ignore the table. Precisely because Oracle may ignore NO_USE_HASH Hint or the driven table specified in it, we usually use LEADING Hint (or ORDERED Hint) with NO_USE_HASH Hint to get the optimizer out of our desired execution plan.

The format is as follows:

/ * + NO_USE_HASH (target table 1, target table 2... Target Table n) * /

/ * + NO_USE_HASH (target table 1, target table 2,... , target table n) * /

Examples of use:

Select / * + no_use_hash (emp) * / *

From emp,dept

Where emp.deptno=dept.deptno

Select / * + no_use_hash (e j d t) * / e.enamekinj.jobpene.salred.deptno

From emp e,jobs j,dept d,emp_temp t

Where e.empno=j.empno

And e.deptno=d.deptno

And d.lockedCHICAGO'

And e.ename=t.ename

Order by e.ename

Users of NO_USE_NL Hint are used the same way as NO_USE_MERGE Hint.

7 、 MERGE_AJ

MERGE_AJ is a Hint for subqueries, which means that the optimizer performs sorting and disjoins on related target tables.

The format is as follows:

/ * + MERGE_AJ * /

Examples of use:

Select *

From emp

Where deptno not in (select / * + merge_aj * / deptno

From dept

Where loc='CHICAGO')

The meaning of Hint in the above SQL is to have the optimizer sort and disjoin the target tables EMP and DEPT. MERGE_AJ is the Hint for the subquery, so the location of / * + merge_aj * / is in the Query Block where the subquery is located.

It can also be written as

Select / * + merge_aj (@ zhaoxu) * / *

From emp

Where deptno not in (select / * + qb_name (zhaoxu) * / deptno

From dept

Where loc='CHICAGO')

8 、 NL_AJ

NL_AJ is a Hint for subqueries, which means that the optimizer performs nested loop disjoins on related target tables.

The format is as follows:

/ * + NL_AJ * /

Examples of use:

Select *

From emp

Where deptno not in (select / * + nl_aj * / deptno

From dept

Where loc='CHICAGO')

Users of NL_AJ are used the same way as MERGE_AJ Hint.

9 、 HASH_AJ

HASH_AJ is a Hint for subqueries, which means that the optimizer performs hash disjoins on related target tables.

The format is as follows:

/ * + HASH_AJ * /

Examples of use:

Select *

From emp

Where deptno not in (select / * + hash_aj * / deptno

From dept

Where loc='CHICAGO')

Users of HASH_AJ are used the same way as MERGE_AJ Hint.

10 、 MERGE_SJ

MERGE_SJ is a Hint for subqueries, which means that the optimizer performs sorting and merging semi-joins on related target tables.

The format is as follows:

/ * + MERGE_SJ * /

Examples of use:

Select *

From dept d

Where exists (select / * + merge_sj * / 1)

From emp e

Where e.deptno=d.deptno

And e.sal > 800)

The usage of MERGE_SJ Hint is the same as that of MERGE_AJ Hint.

11 、 NL_SJ

NL_SJ is a Hint for subqueries, which means that the optimizer performs nested loop semi-joins on related target tables.

The format is as follows:

/ * + NL_SJ * /

Examples of use:

Select *

From dept d

Where exists (select / * + nl_sj * / 1)

From emp e

Where e.deptno=d.deptno

And e.sal > 800)

The usage of NL_SJ Hint is the same as that of MERGE_AJ Hint.

12 、 HASH_SJ

HASH_SJ is a Hint for subqueries, which means that the optimizer performs a hash semi-join on the related target table.

The format is as follows:

/ * + HASH_SJ * /

Examples of use:

Select *

From dept d

Where exists (select / * + hash_sj * / 1)

From emp e

Where e.deptno=d.deptno

And e.sal > 800)

The usage of HASH_SJ Hint is the same as that of MERGE_AJ Hint.

2. Hint related to parallelism

1 、 PARALLEL

Before Oracle 11gR2, PARALLEL was a Hint for a single target table, which meant that the optimizer accessed the target table in parallel at a specified or system-calculated degree of parallelism. Since Oracle 11gR2, Oracle has introduced automatic parallelism, and accordingly, PARALLEL Hint has changed.

In Oracle 11gR2, the scope and usage of PARALLEL Hint have changed. PARALLEL Hint in Oracle 11gR2 is a Hint for the whole target SQL, which means that the optimizer executes all the execution steps that can be executed in parallel in the execution plan of the target SQL with a specified or system-calculated degree of parallelism. Of course, the old PARALLEL Hint for a single target table can still be used in Oracle 11gR2, but its priority will be lower than the new PARALLEL Hint for the entire target SQL, that is, if both the new and old formats of PARALLEL Hint,Oracle appear in the target SQL, it will choose the new PARALLEL Hint for the entire target SQL and ignore the old PARALLEL Hint for the single target table.

The new PARALLEL Hint for the entire target SQL has the following four formats:

Format 1 / * + PARALLEL * /

Format 2 / * + PARALLEL (AUTO) * /

Format 3 / * + PARALLEL (MANUAL) * /

Format 4 / * + PARALLEL (specified parallelism) * /

The target SQL that uses format 1 is always executed in parallel, and Oracle calculates a degree of parallelism, which is always greater than or equal to 2.

The target SQL,Oracle with format 2 calculates a degree of parallelism, but the calculated degree of parallelism may be 1, so using the target SQL may not always be executed in parallel.

Using the target SQL of format 3, whether it can be executed in parallel depends entirely on the setting of the parallelism of the related objects in the target SQL. For example, if the setting of the parallelism of the target table is greater than 1, the target SQL executes in parallel, and the parallelism of parallel execution is equal to the parallelism setting on the target table. If the target table parallelism is 1, it will be executed in a serial manner.

A target SQL that uses format 4 always executes the target SQL with the degree of parallelism specified in that Hint.

The old PARALLEL Hint for a single target table has the following two formats:

Format 1 / * + PARALLEL (target table specified parallelism) * / or / * + PARALLEL (target table, specified parallelism) * /

Format 2 / * + PARALLEL (target table DEFAULT) * / or / * + PARALLEL (target table, DEFAULT) * /

A target SQL that uses format 1 always accesses the target table at the degree of parallelism specified in that Hint.

The target SQL that uses format 2 always accesses the target table in parallel based on the default parallelism calculated by the relevant system parameters.

An example of using PARALLEL Hint for the entire target SQL:

Select / * + parallel * / ename from emp

Select / * + parallel (auto) * / ename from emp

Select / * + parallel (manual) * / ename from emp

Select / * + parallel (6) * / ename from emp

An example of using PARALLEL Hint for a single target table:

Select / * + parallel (emp 2) * / ename from emp

Select / * + parallel (emp default) * / ename from emp

You can view the details of the parallel child processes used by the current system for parallel execution from V$PQ_SLAVE. The SESSIONS field in the view represents the total number of session using parallel child processes. Even if the same session uses a parallel child process multiple times, the value of the field SESSIONS corresponding to the record of the parallel child process in the view will be incremented. In certain cases, I can analyze the actual parallelism of the execution of the target SQL through this field.

Parallel Hint in Oracle 11gR2 can also be used for global temporary tables.

2 、 NO_PARALLEL

In Oracle 11gR2, like PARALLEL Hint, the scope and usage of NO_PARALLEL Hint have changed. The NO_PARALLEL in Oracle 11gR2 is the Hint for the whole goal composition, and it is the antisense Hint of PARALLEL Hint, which means that the optimizer does not execute the execution steps that can be executed in parallel in the execution plan of the target SQL. Of course, the old NO_PARALLEL Hint for a single target table can still be used in Oracle 11gR2.

The new NO_PARALLEL format for the entire target SQL is as follows:

/ * + NO_PARALLEL * /

The old NO_PARALLEL format for a single target table is as follows

/ * + NO_PARALLEL (target table) * /

An example of NO_PARALLEL usage for the entire target SQL:

Select / * + no_parallel * / * from emp

An example of NO_PARALLEL usage for a single target table:

Select / * + no_parallel (emp) * / * from emp

3 、 PARALLEL_INDEX

PARALLEL_INDEX is a Hint for a single target table, which means that the optimizer performs a parallel index scan on the target partitioned index on the target table with a specified or system-calculated degree of parallelism.

There are five formats to use:

Format 1 / * + PARALLEL_INDEX (the degree of parallelism specified by the target table target partition index) * /

Format 2 / * + PARALLEL_INDEX (target table target partition index DEFAULT) * /

Format 3 / * + PARALLEL_INDEX (target table target partition index 1 target partition index 2... Target partition index n parallelism of target partition index 1 parallelism of target partition index 2. Parallelism of the target partition index n) * /

Format 4 / * + PARALLEL_INDEX (target table target partition index 1 target partition index 2... Target partition index n DEFAULT DEFAULT... DEFAULT) * /

Format 5 / * + PARALLEL_INDEX (target table) * /

The target SQL that uses format 1 always accesses the target partition index on the target table in parallel with the degree of parallelism specified in format 1.

The target SQL that uses format 2 always accesses the target partitioned index on the target table in parallel with the default parallelism calculated based on the relevant system parameters. Here the optimizer may make some adjustments to the calculated default parallelism, even if the actual parallelism of the target SQL in format 2 is not necessarily the default parallelism of the current system.

You can specify multiple target indexes in PARALLEL_INDEX Hint and specify their respective parallelism (format 3) or uniformly specify their corresponding parallelism as the default value calculated by Oracle (format 4) or only the target table (format 5, which means that all existing indexes on the target table are specified at the same time). In this case, Oracle will calculate the cost of parallel scanning for each of them. And select the one with the lowest cost as the target index to be scanned in parallel.

The delimiter in Hint can also be used with ",".

Examples of use:

Select / * + parallel_index (emp_par idx_par 3) * / emp from emp_par

Select / * + parallel_index (emp_par idx_par default) * / emp from emp_par

Select / * + index (emp_par idx_par_1) parallel_index (emp_par idx_par_1 idx_par_2 3 3) * / emp from emp_par

4 、 NO_PARALLEL_INDEX

NO_PARALLEL_INDEX is the Hint for a single target table, and it is the antisense Hint of PARALLEL_INDEX Hint, which means that the optimizer does not allow the optimizer to perform parallel index scans on the target partitioned index specified by Hint.

The format is as follows:

Format 1 / * + NO_PARALLEL_INDEX (target table target partition index) * /

Format 2 / * + NO_PARALLEL_INDEX (target table target partition index 1 target partition index 2... Target partition index n) * /

Format 3 / * + NO_PARALLEL_INDEX (target table) * /

The delimiter can also be used with ",".

Examples of use:

Select / * + no_parallel_index (emp_par idx_par) * / empno from emp_par

Select / * + no_parallel_index (emp_par idx_par1 idx_par_2) * / empno from emp_par

Select / * + no_parallel_index (emp_par) * / empno from emp_par

3. Hint related to query transformation

1 、 USE_CONCAT

USE_CONCAT is the Hint for the entire target SQL, which means that the optimizer uses the IN-List extension (IN-List Expansion) or the OR extension (OR Expansion) on the target SQL.

The format is as follows:

/ * + USE_CONCAT * /

Examples of use:

Select / * + use_concat * / emp,ename from emp where empno in (7654 .7698)

Select / * + use_concat * / * from emp where mgr=7902 and deptno=20

The corresponding keyword in the execution plan is "CONCATENATION".

2 、 NO_EXPAND

NO_EXPAND is the Hint for the entire target SQL, and it is the antisense Hint of USE_CONCAT, which means that the optimizer does not use IN-List or OR extensions on the target SQL.

The format is as follows:

/ * + NO_EXPAND * /

Examples of use:

Select / * + no_expand * / emp,ename from emp where empno in (7654 .7698)

Select / * + no_expand * / * from emp where mgr=7902 and deptno=20

The keyword that does not appear in the execution plan is "CONCATENATION".

3 、 MERGE

MERGE is a Hint for a single target view, which means that the optimizer performs view merging (View Mergeing) on the target view.

Use format:

/ * + MERGE (target view) * /

If the target view is an embedded view, the MERGE Hint can also appear in the Query Block where its view definition statement is located, but the name of the embedded view should no longer be in the Hint and should be in the format / * + MERGE * /

Examples of use:

Select / * + merge (dept_view) * / empno,ename,dname

From emp,dept_view

Where emp.deptno=dept_view.deptno

Select empno,ename,dname

From emp

(select / * + merge * / *

From dept

Where local='CHICAGO') dept_view_inline

Where emp.deptno=dept_view_inline.deptno

4 、 NO_MERGE

NO_MERGE is a Hint for a single target view, which is the antisense Hint of MERGE, meaning that the optimizer does not perform view merging on the target view.

The format is as follows:

/ * + NO_MERGE (target view) * /

If the target view is an embedded view, the MERGE Hint can also appear in the Query Block where its view definition statement is located, but the name of the embedded view should no longer be in the Hint and should be in the format / * + NO_MERGE * /

Examples of use:

Select / * + no_merge (dept_view) * / empno,ename,dname

From emp,dept_view

Where emp.deptno=dept_view.deptno

Select empno,ename,dname

From emp

(select / * + no_merge * / *

From dept

Where local='CHICAGO') dept_view_inline

Where emp.deptno=dept_view_inline.deptno

5 、 UNNEST

UNNEST is a Hinit for a subquery, which means that the optimizer executes subquery deployment (Subquery Unnesting) on the subquery of the target SQL.

The format is as follows:

/ * + UNNEST * /

Examples of use:

Select *

From emp

Where deptno not in (select / * + unnest * / deptno

From dept

Where loc='CHICAGO')

The location of / * + unnest * / should be in the Query Block where the subquery is located.

6 、 NO_UNNEST

NO_UNNEST is a Hint for a subquery, and it is an antisense Hint of UNNEST, which means that the optimizer does not allow the optimizer to use subquery expansion for subqueries in the target SQL.

The format is as follows:

/ * + NO_UNNEST * /

Examples of use:

Select *

From emp

Where deptno not in (select / * + no_unnest * / deptno

From dept

Where loc='CHICAGO')

The location of / * + no_unnest * / is in the Query Block where the subquery is located.

7 、 EXPAND_TABLE

EXPAND_TABLE is a Hint for a single target table, which means that the optimizer performs table extensions on the target table in the target table SQL, regardless of cost.

The format is as follows:

/ * + EXPAND_TABLE (target table) * /

Examples of use:

Select / * + expand_table (T1) * / T2. Custworthy city amount_sold_total sum (t1.amount_sold)

From sales t1,customers t2,products t3,channels t4

Where t1.cust_id=t2.cust_id

And t1.prod_id=t3.prod_id

And t1.channel_id=t4.channel_id

And t2.country_id=52771

And t3.prod_name='Mouse Pad'

And t4. Channelized Internet

And time_id between to_date ('2000-01-01 00 and to_date) and to_date (' 2004-01-01 00 HH24:MI:SS')

8 、 NO_EXPAND_TABLE

NO_EXPAND_TABLE is the Hint for a single target table, which is the antisense Hint of EXPAND_TABLE, which means that the optimizer does not allow the optimizer to use table extensions for the target table in the target SQL.

The format is as follows:

/ * + NO_EXPAND_TABLE (target table) * /

Examples of use:

Select / * + no_expand_table (T1) * / T2. Custworthy city amount_sold_total sum (t1.amount_sold)

From sales t1,customers t2,products t3,channels t4

Where t1.cust_id=t2.cust_id

And t1.prod_id=t3.prod_id

And t1.channel_id=t4.channel_id

And t2.country_id=52771

And t3.prod_name='Mouse Pad'

And t4. Channelized Internet

And time_id between to_date ('2000-01-01 00 and to_date) and to_date (' 2004-01-01 00 HH24:MI:SS')

Refer to "SQL Optimization based on Oracle"

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.