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

Hint in Oracle can be used to adjust the execution plan of SQL and improve the efficiency of SQL execution. The following categories describe the Hint that is common in Oracle databases. What is described here is that the Hint in each version of the common Hint,Oracle database in Oracle11gR2 is different, so the Hint described here may not be applicable to earlier versions of Oracle.

1. Hint related to optimizer pattern

1 、 ALL_ROWS

ALL_ROWS is the Hint for the entire target SQL, which means that the optimizer enables CBO and chooses the execution path with the best throughput when getting the execution plan of the target SQL. The "best throughput" here means that the resource consumption (that is, the consumption of hardware resources such as ALL_ROWS Hint O, CPU, etc.) is the lowest, that is, when CBO is in effect, the optimizer will enable CBO and calculate their respective costs based on the resource consumption of each execution path.

The format of ALL_ROWS Hint is as follows:

/ * + ALL_ROWS * /

Examples of use:

Select / * + all_rows * / empno,ename,sal,job from emp where empno=7396

Starting with Oracle10g, ALL_ROWS is the default optimizer mode, and CBO is enabled.

Scott@TEST > show parameter optimizer_modeNAME TYPE VALUE -optimizer_mode string ALL_ROWS

If the target SQL uses other Hint related to execution paths and table joins in addition to ALL_ROWS, the optimizer takes precedence over ALL_ROWS.

2. FIRST_ROWS (n)

FIRST_ROWS (n) is a Hint for the entire target SQL, which means that the optimizer enables the CBO mode, and when getting the execution plan of the target SQL, it selects the execution paths that can return n records with the fastest response time, that is, when the FIRST_ROWS (n) Hint is in effect, the optimizer will enable CBO, and the execution plan of the target SQL will be determined based on the response time of the return n records.

The FIRST_ROWS (n) format is as follows:

/ * + FIRST_ROWS (n) * /

Use example

Select / * + first_rows (10) * / empno,ename,sal,job from emp where deptno=30

/ * + first_rows (10) * / is used in the above SQL, which means to tell the optimizer that we want to return the top 10 records that meet the condition "deptno=30" with the shortest response time.

Note that the FIRST_ROWS (n) Hint and the optimizer pattern FIRST_ROWS_n do not correspond one to one. The n in the optimizer pattern FIRST_ROWS_n can only be 1, 10, 100, 1000. However, n in FIRST_ROWS (n) Hint can also be other values.

Scott@TEST > alter session set optimizer_mode=first_rows_9;ERROR:ORA-00096: invalid value FIRST_ROWS_9 for parameter optimizer_mode, must be from among first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows, all_rows, choose,rulescott@TEST > set autotrace traceonlyscott@TEST > select / * + first_rows (9) * / empno from emp 14 rows selected.Execution Plan---Plan hash value: 179099197Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 9 | 36 | 1 (0) | 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 9 | 36 | 1 (0) | 00:00:01 |- -

If FIRST_ROWS (n) Hint is used in a UPDATE, DELETE, or query statement with the following contents, the Hint is ignored:

Set operations (such as UNION,INTERSACT,MINUS,UNION ALL, etc.)

GROUP BY

FOR UPDATE

Aggregate functions (such as SUM, etc.)

DISTINCT

ORDER BY (no index on the corresponding sorted column)

The optimizer ignores FIRST_ROWS (n) Hint here because for the above type of SQL, Oracle must access all row records before returning the first n rows of records that meet the criteria, that is, in the above cases, there is no point in using the Hint.

3 、 RULE

RULE is the Hint for the entire target SQL, which means that RBO is enabled for the target SQL.

The format is as follows:

/ * + RULE * /

Examples of use:

Select / * + rule * / empno,ename,sal,job from emp where deptno=30

RULE cannot be used with Hint other than DRIVING_SITE, and other Hint may fail when RULE is used with Hint other than DRIVING_SITE; when RULE is combined with DRIVING_SITE, it may fail itself, so RULE Hint is best used alone.

In general, RULE Hint is not recommended. On the one hand, Oracle does not support RBO for a long time, and on the other hand, after enabling RBO, the optimizer will greatly reduce the number of execution paths that the optimizer can choose when executing the target SQL, and many execution paths RBO will not support at all (such as hash connection), which means that the probability of the target SQL running out of the correct execution plan will be greatly reduced after enabling RBO.

Because many execution paths RBO are not supported at all, even if RULE Hint is used in the target SQL, RULE Hint will still be ignored by Oracle if the following occurs (including, but not limited to).

The target SQL uses other Hint (such as DRIVING_SITE) in addition to RULE.

The target SQL uses parallel execution

The objects involved in the target SQL are IOT

The objects involved in the target SQL have partition tables

.

2. Hint related to table access

1 、 FULL

FULL is a Hint for a single target table, which means that the optimizer performs a full table scan on the target table.

The format is as follows:

/ * + FULL (target table) * /

Examples of use:

Select / * + full (emp) * / empno,ename,sal,job from emp where deptno=30

The meaning of Hint in the above SQL is to have the optimizer perform a full table scan on the target table EMP, regardless of any indexes on the table EMP (even if there is a primary key index on the column EMPNO).

2 、 ROIWD

ROIWD is a Hint for a single target table, which means that the optimizer performs an RWOID scan on the target table. ROWID Hint is meaningful only if the where condition with ROWID is used in the target SQL.

The format is as follows:

/ * + ROWID (target table) * /

Examples of use:

Select / * + rowid (emp) * / empno,ename,sal,job from emp where rowid='AAAR3xAAEAAAACXAAA'

Even if ROWID Hint,Oracle is used in Oracle 11gR2, the read blocks will be cached in Buffer Cache.

3. Hint related to index access

1 、 INDEX

INDEX is a Hint for a single target table, which means that the optimizer performs an index scan on the target index of the target table.

The target index in INDEX Hint can be almost any type of index in the Oracle database (including B-tree index, bitmap index, functional index, etc.).

There are four modes of INDEX Hint:

Format 1 / * + INDEX (target table target index) * /

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

Format 3 / * + INDEX (target table (index column name of target index 1) (index column name of target index 2)... (index column name of target index n) * /

Format 4 / * + INDEX (target table) * /

Format 1 means that only one target index on the target table is specified, and the optimizer will only consider performing an index scan on that target index, not a full table scan or an index scan on other indexes on the target table.

Format 2 means that n target indexes on the target table are specified, and the optimizer will only consider performing index scans on these n target indexes, rather than full table scans or other indexes on the target table. Note that when considering these n target indexes, the optimizer may calculate the cost of scanning each target index separately, and then select the index with the lowest cost value, or it may scan two or more indexes in the target index first, and then perform a merge operation on the scan results. Of course, the prerequisite for this latter possibility is that the optimizer calculates that the cost of doing so is the lowest.

Format 3 is also that the table specifies n target indexes on the target table, but at this time the index column name of the specified target index is used instead of the corresponding target index name. If the target index is a composite index, multiple index columns of the target index can also be specified in parentheses used to specify the column name of the index, separated by spaces.

The representation of the format specifies all existing indexes on the target table, and the optimizer will only consider performing an index scan on all existing indexes on the target table, not the full table scan operation. Note that when considering all the existing indexes on the target table, the optimizer may calculate the cost of scanning these indexes separately and then select the index with the lowest cost value; it is also possible to scan two or more of these indexes separately and then perform a merge operation on the scan results. Of course, the prerequisite for this latter possibility is that the optimizer calculates that the cost of doing so is the lowest.

Examples of use:

Select / * + index (emp pk_emp) * / empno,ename,sal,job from emp where empno=7369 and mgr=7902 and deptno=20; select / * + index (emp pk_emp idx_emp_mgr idx_emp_dept) * / empno,ename,sal,job from emp where empno=7369 and mgr=7902 and deptno=20; select / * + index (emp (empno) (mgr) (deptno)) * / empno,ename,sal,job from emp where empno=7369 and mgr=7902 and deptno=20; select / * + index * / empno,ename,sal,job from emp where empno=7369 and mgr=7902 and deptno=20

2 、 NO_INDEX

NO_INDEX is the Hint for a single target table, which is the antisense Hint of INDEX, which means that the optimizer does not perform a scan on the target index on the target table.

The target index in INDEX Hint can also be almost any type of index in the Oracle database (including B-tree index, bitmap index, functional index, etc.).

There are three formats as follows:

Format 1 / * + NO_INDEX (target table target index) * /

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

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

Format 1 means that only one target index on the target table is specified, and the optimizer simply does not consider performing an index scan on that target index. however, it still considers a full table scan or an index scan on other indexes on the target table.

Format 2 means that n target indexes on the target table are specified, and the optimizer simply does not consider performing an index scan on the n target indexes. however, it still considers a full table scan or an index scan operation on other indexes on the target table.

Format 3 means that all existing indexes on the target table are specified, that is, the optimizer does not consider performing an index scan on all existing indexes on the target table, which is equivalent to specifying a full table scan on the target table.

Examples of use:

Select / * + no_index (emp pk_emp) * / empno,ename,sal,job from empwhere empno=7369 and mgr=7902 and deptno=20; select / * + no_index (emp pk_emp idx_emp_mgr idx_emp_dept) * / empno,ename,sal,job from empwhere empno=7369 and mgr=7902 and deptno=20; select / * + no_index * / empno,ename,sal,job from empwhere empno=7369 and mgr=7902 and deptno=20

3 、 INDEX_DESC

INDEX_DESC is a Hint for a single target table, which means that the optimizer performs an index descending scan on the target index on the target table. If the target index is ascending, INDEX_DESC Hint causes Oracle to scan the index in descending order; if the target index is descending, INDEX_DESC Hint causes Oracle to scan the index in ascending order.

There are three formats:

Format 1 / * + INDEX_DESC (target table target index) * /

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

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

The meaning of the above three formats is the same as that of the corresponding format in INDEX.

Examples of use:

Select / * + index_desc (emp pk_emp) * / empno,ename,sal,job from emp where empno=7369 and mgr=7902 and deptno=20; select / * + index_desc (emp pk_emp idx_emp_mgr idx_emp_dept) * / empno,ename,sal,job from emp where empno=7369 and mgr=7902 and deptno=20; select / * + index_desc * / empno,ename,sal,job from emp where empno=7369 and mgr=7902 and deptno=20

Example:

Scott@TEST > select / * + index_desc (emp,pk_emp) * / empno from emp EMPNO- 7934 7902 7900 7876 7844 7839 7788 7782 7698 7654 7566 7521 7499 736914 rows selected.Execution Plan---Plan Hash value: 1838043032-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 10 | 40 | 1 (0) | 00:00:01 | | 1 | INDEX FULL SCAN DESCENDING | PK_EMP | 10 | 40 | 1 (0) | 00:00:01 | -.

4 、 INDEX_COMBINE

INDEX_COMBINE is a Hint for a single target table, which means that the optimizer performs bitmap Boolean operations on multiple target indexes on the target table. There is a mapping function (Mapping Function) in the Oracle database, which can instance the conversion between the ROWID in the B*Tree index and the bitmap in the corresponding bitmap index, so INDEX_COMBINE Hint is not limited to the bitmap index, its function object can also be the B*Tree index.

There are two formats as follows

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

Format 2 / * + INDEX_COMBINE (target table) * /

Format 1 indicates that n target indexes on the target table are specified, and the optimizer considers performing bitmap Boolean operations on two or more of these n target indexes.

Format 2 means that all existing indexes on the target table are specified, and the optimizer considers performing bitmap Boolean operations on two or more of all indexes that already exist on the table.

Examples of use:

Select / * + index_combine (emp pk_emp idx_emp_mgr) * / empno,ename,sal,job from emp where empno=7369 and mgr=7902; select / * + index_combine (emp pk_emp idx_emp_mgr idx_emp_deptno) * / empno,ename,sal,job from emp where empno=7369 and mgr=7902 and deptno=20; select / * + index_combine (emp) * / empno,ename,sal,job from emp where empno=7369 and mgr=7902 and deptno=20

Let's look at an example to create two indexes on the table EMP

Scott@TEST > create index idx_emp_mgr on emp (mgr); Index created.scott@TEST > create index idx_emp_dept on emp (deptno); Index created.scott@TEST > select / * + index_combine (emp pk_emp idx_emp_mgr idx_emp_deptno) * / empno,ename,sal,job 2 from emp 3 where empno=7369 and mgr=7902 and deptno=20 EMPNO ENAME SAL JOB--7369 SMITH 800 CLERKExecution Plan---Plan hash value: 1816402415 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 29 | 2 (0) | | 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS | 3 | BITMAP AND | 4 | BITMAP CONVERSION FROM ROWIDS | * 5 | INDEX RANGE SCAN | PK_EMP | 0 (0) | 00:00:01 | | 6 | BITMAP CONVERSION FROM ROWIDS | * 7 | INDEX RANGE SCAN | IDX_EMP_MGR | 1 (0) | 00:00:01 | 8 | BITMAP CONVERSION FROM ROWIDS | | * 9 | INDEX RANGE SCAN | IDX_EMP_DEPT | 1 (0) | 00:00:01 |- -.

From the above execution plan, you can see the keywords "BITMAP CONVERSION FROM ROWIDS", "BITMAP AND" and "BITMAP CONVERSION TO ROWIDS", which shows that Oracle first converts the ROWID into a bitmap on the B*Tree indexes IDX_EMP_MGR, IDX_EMP_DEPT and PK_EMP of the above three single-key values, respectively, and then performs a BITMAP AND Boolean operation on the converted bitmap. Finally, the result of Boolean operation is converted into ROWID by mapping function again, and the final execution result is obtained by returning to the table. It is obvious that the reason for getting out of such an implementation plan is that INDEX_COMBINE Hint is in effect.

The ROWID is converted into a bitmap with the mapping function, and then the Boolean operation is performed. Finally, the result of the Boolean operation is converted to ROWID with the mapping function again and the final execution result is obtained by returning to the table. The execution efficiency of this process in the actual production environment may be problematic. The implicit parameter _ B_TREE_BITMAP_PLANS can be used to disable the conversion of the ROWID bitmap in the process:

Alter session set "_ b_tree_bitmap_plans" = false

Scott@TEST > alter session set "_ b_tree_bitmap_plans" = false;Session altered.scott@TEST > select / * + index_combine (emp pk_emp idx_emp_mgr idx_emp_deptno) * / empno,ename,sal,job 2 from emp 3 where empno=7369 and mgr=7902 and deptno=20 EMPNO ENAME SAL JOB--7369 SMITH 800 CLERKExecution Plan---Plan hash value: 2949544139 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 29 | 1 (0) | 00:00:01 | | * 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 29 | 1 (0) | 00:00:01 | | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 00:00:01 | -.

You can see from the above execution plan that there is no BITMAP-related keyword, that is, INDEX_COMBINE Hint is ignored by Oracle.

5 、 INDEX_FFS

INDEX_FFS is a Hint for a single target table, which means that the optimizer performs a fast full scan of the index on the target table. Note that the prerequisite for fast full index scanning is that all query columns in the SELECT statement exist in the target index, that is, all query columns can be obtained by scanning the target index without returning to the table.

There are three formats as follows:

Format 1 / * + INDEX_FFS (target table target index) * /

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

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

The meaning of the above three formats is the same as that of the corresponding format in INDEX.

Examples of use:

Select / * + index_ffs (emp pk_emp) * / empno from emp;select / * + index_ffs (emp idx_emp_1 idx_emp_2) * / empno from emp where mgr=7902 and deptno=20;--create index idx_emp_1 on emp (mgr,deptno,1);-- create index idx_emp_2 on emp (mgr,deptno,2); select / * + index_ffs (emp) * / empno from emp

Look at the following example:

Scott@TEST > select empno from emp 14 rows selected.Execution Plan---Plan hash value: 179099197Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 10 | 40 | 1 (0) | 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 10 | 40 | 1 (0) | 00:00:01 |- -.Scott @ TEST > select / * + index_ffs (emp) * / empno from emp 14 rows selected.Execution Plan---Plan hash value: 366039554 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 10 | 40 | 2 (0) | 00:00:01 | | 1 | INDEX FAST FULL SCAN | PK_EMP | 10 | 40 | 2 (0) | 00:00:01 |- -.

6 、 INDEX_JOIN

INDEX_JOIN is a Hint for a single target table, which means that the optimizer performs INDEX JOIN operations on multiple target indexes on the target table. The premise that INDEX JOIN can be established is that all the query columns in the SELECT statement exist in multiple target indexes on the target table, that is, all the query columns can be obtained by scanning these indexes without returning to the table.

The format is as follows:

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

Format 2 / * + INDEX_JOIN * /

The meaning of the above two formats is the same as that of the corresponding format in INDEX_COMBINE Hint.

Examples of use:

Select / * + index_join (emp pk_emp idx_emp_mgr) * / empno,mgr from emp where empno > 7369 and mgr7369 and mgrselect empno,mgr 2 from emp 3 where empno > 7369 and mgrselect / * + index_join (emp) * / empno,mgr 2 from emp 3 where empno > 7369 and mgrselect empno,mgr 2 from emp 3 where deptno=20 and mgr=7902 Execution Plan---Plan hash value: 2059184959 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0) | 00:00:01 | | * 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 11 | 2 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | | IDX_EMP_MGR | 2 | | 1 (0) | 00:00:01 |-| -.Scott @ TEST > select / * + and_equal (emp idx_emp_mgr idx_emp_dept) * / empno Mgr 2 from emp 3 where deptno=20 and mgr=7902 Execution Plan---Plan hash value: 3295440569 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0) | 00:00:01 | | * 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 11 | 3 (0) | 00:00:01 | | 2 | AND- EQUAL | * 3 | INDEX RANGE SCAN | IDX_EMP_MGR | 2 | | 1 (0) | 00:00:01 | | * 4 | INDEX RANGE SCAN | IDX_EMP_DEPT | 5 | | 1 (0) | 00:00:01 |-- -.

4. Hint related to table join order

1 、 ORDERED

ORDERED is a Hint for multiple target tables, which means that when the optimizer performs table join operations on multiple target tables, license them to join from left to right in the order in which they appear in the where condition of the target SQL.

The format is as follows:

/ * + ORDERED * /

Examples of use:

Select / * + ordered * / e.enamerej.jobree.salred.deptno from emp ePapi jobs jdept d where e.empno=j.empno and e.deptno=d.deptno and d.localizationCHICAGO 'order by e.ename

Example:

Scott@TEST > select e.enameforcedj.jobree.salre d.deptno 2 from emp eDifferent d 3 where e.empno=j.empno 4 and e.deptno=d.deptno 5 and d.localizationCHICAGO' 6 order by e.ename 6 rows selected.Execution Plan---Plan hash value: 4113290228 Murray- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 5 | 235 | 9 (23) | 00:00:01 | | 1 | SORT ORDER BY | | | 5 | 235 | 9 (23) | 00:00:01 | | * 2 | HASH JOIN | | 5 | 235 | 8 (13) | 00:00:01 | | 3 | NESTED LOOPS | | 4 | NESTED LOOPS | | | | 5 | 140 | 4 (0) | 00:00:01 | | * 5 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | | * 6 | INDEX RANGE SCAN | IDX_EMP_DEPT | 5 | | 0 (0) | 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | | EMP | 5 | 85 | 1 (0) | 00:00:01 | | 8 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0) | 00:00:01 |-- -- .Scott @ TEST > select / * + ordered * / e.ename J. Jobwage. salred.deptno 2 from emp eDifft d 3 where e.empno=j.empno 4 and e.deptno=d.deptno 5 and d.localizationCHICAGO' 6 order by e.ename 6 rows selected.Execution Plan---Plan hash value: 3031293267Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- | 0 | SELECT STATEMENT | | 5 | 235 | 11 (28) | 00:00:01 | | 1 | SORT ORDER BY | | 5 | 235 | 11 (28) | 00:00:01 | * 2 | | HASH JOIN | | 5 | 235 | 10 (20) | 00:00:01 | 3 | MERGE JOIN | 14 | 504 | 6 (17) | 00:00:01 | 4 | TABLE ACCESS BY INDEX ROWID | EMP | 14 | 238 | 2 (0) | 00:00:01 | 5 | INDEX FULL SCAN | | PK_EMP | 14 | 1 (0) | 00:00:01 | | * 6 | SORT JOIN | | 14 | 266 | 4 (25) | 00:00:01 | 7 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0) | 00:00:01 | * 8 | TABLE ACCESS FULL | DEPT | | | 1 | 11 | 3 (0) | 00:00:01 |-.Scott @ TEST > select / * + ordered * / e.ename | Job j 3 where e.empno=j.empno 4 and e.deptno=d.deptno 5 and d.locale records CHICAGO'6 order by e.ename 6 rows selected.Execution Plan---Plan hash value: 1175157407 Murray- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 5 | 235 | 11 (28) | 00:00:01 | | 1 | SORT ORDER BY | | 5 | | 235 | 11 (28) | 00:00:01 | * 2 | HASH JOIN | | 5 | 235 | 10 (20) | 00:00:01 | 3 | MERGE JOIN | | 5 | 140 | 6 (17) | 00:00:01 | 4 | TABLE ACCESS BY INDEX ROWID | EMP | | | 238 | 2 (0) | 00:00:01 | | 5 | INDEX FULL SCAN | IDX_EMP_DEPT | 14 | 1 (0) | 00:00:01 | * 6 | SORT JOIN | | 1 | 11 | 4 (25) | 00:00:01 | * 7 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | | 8 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0) | 00:00:01 |-- -.

From the above execution plan, you can see that the order of table scans without ordered Hint is DEPT- > EMP- > JOBS, but after using ordered Hint, the order of table scans becomes EMP- > JOBS- > DEPT is the same as the order in the target SQL, and after the target SQL text is modified, the order of table scans becomes EMP- > DEPT- > JOBS accordingly.

2 、 LEADING

LEADING is a Hint for multiple target tables, which means that the optimizer takes the join result of the multiple tables we specify as the driving result set in the process of joining the target SQL table, and takes the first target table that appears from left to right in LEADING Hint as the first driver table in the whole table join process.

LEADING is milder than ORDERED because it only specifies the first driver table and driver result set, and does not fully specify the order of table joins as ORDERED does, which means LEADING gives the optimizer more room for adjustment.

When the table specified in the LEADING Hint cannot be used as a driver table or a result set during the join of the target SQL, the Oracle ignores the Hint.

The format is as follows:

/ * + LEADING (target table 1, target table 2...

Examples of use:

Select / * + leading (te) * / e.enamecamera j.jobree.salre d.deptno from emp eDie jobs jpeople dept d and e.ename=t.ename order by e.ename emptied temp t where e.empno=j.empno and e.deptno=d.deptno and d.lockedCHICAGO 'and e.ename=t.ename order by e.ename

Example:

-- do not use Hintscott@TEST > select e.enamerect j.jobree.salre d.deptno 2 from emp eRecience jobs jMagol dpgle empathtemp t 3 where e.empno=j.empno 4 and e.deptno=d.deptno 5 and d.lockedCHICAGO' 6 and e.ename=t.ename 7 order by e.ename 6 rows selected.Execution Plan---Plan hash value: 558051962 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 5 | 270 | 12 (17) | 00:00:01 | | 1 | SORT ORDER BY | | | 5 | 270 | 12 (17) | 00:00:01 | | * 2 | HASH JOIN | | 5 | 270 | 11 (10) | 00:00:01 | | * 3 | HASH JOIN | | 5 | 235 | 8 (13) | 00:00:01 | 4 | NESTED LOOPS | | 5 | NESTED LOOPS | | 5 | 140 | 4 (0) | 00:00:01 | | * 6 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | | * 7 | INDEX RANGE SCAN | IDX_EMP_DEPT | 5 | 0 | 00:00:01 | 8 | TABLE ACCESS BY INDEX ROWID | EMP | 5 | 85 | 1 (0) | 00:00:01 | 9 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0) | 00:00:01 | 10 | TABLE ACCESS FULL | EMP_TEMP | 14 | 98 | 3 (0) | 00:00:01 |- -- use LEADING Hintscott@TEST > select / * + leading (t e) * / e.ename J. Jobparentin. Salre d.deptno 2 from emp ePhillips jdept dtemp t 3 where e.empno=j.empno 4 and e.deptno=d.deptno 5 and d.localizationCHICAGO'6 and e.ename=t.ename 7 order by e.ename 6 rows selected.Execution Plan---Plan hash value: 937897748 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -- | 0 | SELECT STATEMENT | | 5 | 270 | 15 (20) | 00:00:01 | | 1 | SORT ORDER BY | 5 | 270 | 15 (20) | 00:00:01 | | * 2 | HASH JOIN | | 5 | 270 | 14 ( 15) | 00:00:01 | * 3 | HASH JOIN | | 5 | 175 | 10 (10) | 00:00:01 | * 4 | HASH JOIN | 14 | 336 | 7 (15) | 00:00:01 | | 5 | TABLE ACCESS FULL | EMP_TEMP | 14 | 98 | 3 (0) | 00:00:01 | 6 | | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0) | 00:00:01 | | * 7 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | 8 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0) | 00:00:01 |- -use Ordered Hintscott@TEST > select / * + ordered * / e.ename J. Jobparentin. Salre d.deptno 2 from emp ePhillips jdept dtemp t 3 where e.empno=j.empno 4 and e.deptno=d.deptno 5 and d.localizationCHICAGO'6 and e.ename=t.ename 7 order by e.ename 6 rows selected.Execution Plan---Plan hash value: 2459 794491 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 5 | 270 | 14 (22) | 00:00:01 | | 1 | SORT ORDER BY | | 5 | 270 | 14 (22) | 00:00:01 | | * 2 | HASH JOIN | | 5 | 270 | 13 (16) | 00:00:01 | | * 3 | HASH JOIN | | 5 | 235 | 10 (20) | 00:00:01 | | 4 | MERGE JOIN | | 14 | 504 | 6 (17) | 00:00:01 | 5 | TABLE ACCESS BY INDEX ROWID | EMP | 14 | 238 | 2 (0) | 00:00:01 | 6 | INDEX FULL SCAN | PK_EMP | 14 | 1 (0) | 00:00:01 | * 7 | SORT JOIN | 14 | 266 | 4 ( 25) | 00:00:01 | 8 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0) | 00:00:01 | * 9 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | 10 | TABLE ACCESS FULL | EMP_TEMP | 14 | 98 | 3 (0) | 00:00 : 01 | -.

As can be seen from the above execution plan, the order of table scanning without Hint is DEPT- > EMP- > JOBS- > EMP_TEMP; when using LEADING Hint, the order of table scanning is EMP_TEMP- > EMP- > DEPT- > JOBS,EMP_TEMP as the first driver table and the connection result of table EMP is used as the driver result set, which is consistent with the Hint requirements. When using Ordered Hint, the order of table scanning is EMP- > JOBS- > DEPT- > EMP_TEMP, which is the same as that in SQL.

Refer to "SQL Optimization based on Oracle"

Official document: http://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF005

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.