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

Hint can influence the optimizer's choice of execution plan, but this effect is not mandatory, and the optimizer may ignore the Hint in the target SQL in some cases. After Hint is ignored by Oracle due to various reasons, Oracle will not give any hint or warning, let alone report an error, and the target SQL can still run normally, which is in line with the identity that Hint is actually a special comment. Comments are originally optional, and the existence of SQL, which can be executed normally without Hint, should not be caused by the addition of Hint.

Let's take a look at several common situations where Hint is ignored by Oracle.

1 the Hint used has grammatical or spelling errors

Once there is a grammar or spelling error in the Hint used, Oracle ignores the Hint. Take a look at some sample SQL:

Select / * + ind (emp pk_emp) * / * from emp

Select / * + index (emp pk_emp * / * from emp

Select / * + index (emp pk_emp) * / * from emp

Select * / * + index (emp pk_emp) * / from emp

Select / * + index (scott.emp pk_emp) * / * from emp

Select / * + index (emp pk_emp) * / * from emp e

Select / * + index (emp emp_pk) * / * from emp

Select / * + full (T2) * / t1.enameredt1.deptno from emp T1 where t1.deptno in (select t2.deptno from detp t where t2.localizedCHICAGO')

In fact, the Hint in the above 8 SQL is invalid and will be ignored by Oracle.

1 because the keyword should be "index" instead of "ind"

2 is because a closing parenthesis is missing

3 because there is a space between the first * and + in Hint

4 because Hint appears in the wrong place, it should appear in front of *

5 because the emp table is preceded by the SCHEME name

6 because there is no alias for the emp table

7 because the index name is misspelled

8 is because Hint spans Query Block. The scope of Hint is limited to its own Query Block, and if a Hint student extends the scope beyond its Query Block without specifying the name of the Query Block in which it is valid, Oracle will ignore the Hint.

2 invalid Hint used

Even if the syntax is correct, if for some reason Oracle considers the Hint invalid, Oracle still ignores the Hint.

Take a look at some examples

Scott@TEST > set autotrace traceonly scott@TEST > select / * + index (dept idx_dept_loc) * / deptno,dname from dept where loc='CHICAGO' Execution Plan---Plan hash value: 492093765 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 10 | 300 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 10 | 300 | 2 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_DEPT_LOC | 4 | 1 (0) | 00:00:01 | -.

As can be seen from the above output, the execution plan of the above SQL is to scan the index range of the index IDX_DEPT_LOC, indicating that the Hint is in effect, but if the where condition is replaced with a deptno=30 that has nothing to do with the index IDX_DEPT_LOC, let's look at the implementation.

Scott@TEST > select / * + index (dept idx_dept_loc) * / deptno,dname from dept where deptno=30 Execution Plan---Plan hash value: 2852011669 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 22 | 2 (0) | 00:00:01 | | * 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 1 (0) | 00:00:01 |

As you can see from the output above, the execution plan is the INDEX UNIQUE SCAN for the primary key PK_DEPT, not the IDX_DEPT_LOC in Hint. This means that Hint is invalid in this SQL.

Even if the where condition is not changed, if the index IDX_DEPT_LOC is deleted, the Hint will fail:

Scott@TEST > drop index idx_dept_loc;Index dropped.scott@TEST > select / * + index (dept idx_dept_loc) * / deptno,dname from dept where loc='CHICAGO' Execution Plan---Plan hash value: 3383998547 -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | | 10 | 300 | 29 (0) | 00:00:01 | | * 1 | TABLE ACCESS FULL | DEPT | 10 | 300 | 29 (0) | 00:00:01 |-

From the above implementation plan, we can see that the TABLE ACCESS FULL,Hint of the table DEPT is also invalid.

To take a look at an example of using a composite Hint, let's first look at the following SQL execution plan

Scott@TEST > select / * + full (dept) parallel (dept 2) * / deptno from dept Execution Plan---Plan hash value: 587379989 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |- -| 0 | SELECT STATEMENT | | 1000 | 13000 | 16 (0) | 00:00:01 | | | 1 | PX COORDINATOR | | 2 | PX SEND QC (RANDOM) |: TQ10000 | 1000 | 13000 | 16 (0) | 00:00:01 | Q1Ligue 00 | P-> S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 1000 | 13000 | 16 (0) | 00:00:01 | Q1LING00 | | PCWC | 4 | TABLE ACCESS FULL | DEPT | 1000 | 13000 | 16 (0) | 00:00:01 | Q1Power00 | PCWP | |- -.

As can be seen from the above output, it is now a parallel full table scan of the table DEPT, indicating that two of the combined Hint are in effect. This Hint means that both full table scans and parallel access table DEPT are required. There is no contradiction between the two, because full table scans can be performed in parallel. Take a look at the following SQL:

Scott@TEST > select / * + index (dept pk_dept) parallel (dept 2) * / deptno from dept 4 rows selected.Execution Plan---Plan hash value: 2913917002 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1000 | 13000 | 26 (0) | 00:00:01 | | 1 | INDEX FULL SCAN | PK_DEPT | 1000 | 13000 | 26 (0) | 00:00:01 |- -.

Now SQL takes the full index scan of the index PK_DEPT, but serial, which means that parallel (dept 2) in the Hint is invalid, because the primary key index PK_DEPT on the table DEPT is not a partitioned index, and for non-partitioned indexes, index range scan or index full scan cannot be performed in parallel, so parallel (dept 2) is ignored in the above combination Hint.

Look at another example of HASH JOIN:

The Hint of use_hash in the following SQL is valid:

Scott@TEST > select / * + use_hash (T1) * / t1.empnogramme t1.empnoredingt2.loc from emp T1 dept t2 where t1.deptno=t2.deptno and t2.localizedCHICAGO' 6 rows selected.Execution Plan---Plan hash value: 615168685 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- | 0 | SELECT STATEMENT | | 5 | 185 | 7 (15) | 00:00:01 | | * 1 | HASH JOIN | 5 | 185 | 7 (15) | 00:00:01 | | * 2 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 | 3 | (0) | 00:00:01 |

But if you change SQL to the following, the Hint of use_hash will be ignored

Scott@TEST > select / * + use_hash (T1) * / t1.empnogramme t1.empnogramme t2.loc from emp T1 where t1.deptno dept t2 where t1.deptno > t2.deptno and t2.localizationCHICAGO' No rows selectedExecution Plan---Plan hash value: 4192419542Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 37 | 6 (0) | 00:00:01 | | 1 | NESTED LOOPS | 1 | 37 | 6 (0) | 00:00:01 | | * 2 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | * 3 | TABLE ACCESS FULL | EMP | 1 | 26 | 3 (0) | ) | 00:00:01 |-

From the above execution plan, we can see that use_hash is indeed ignored by Oracle, because hash connections are only applicable to equivalent join conditions, and non-equivalent join conditions are meaningless to hash connections, so the above Hint is ignored by Oracle.

3 the Hint used is self-contradictory

If the combination Hint used is self-contradictory, then these contradictory Hint will be ignored by Oracle. However, Oracle will only ignore all self-contradictory Hint, but if there are other valid Hint in the combination Hint used, these valid Hint will not be affected.

Looking at an example that uses self-contradictory Hint, first execute the SQL of a single Hint

Scott@TEST > select / * + index_ffs (dept pk_dept) * / deptno from dept 4 rows selected.Execution Plan---Plan hash value: 2578 398298 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 4 | 12 | 2 (0) | 00:00:01 | | 1 | INDEX FAST FULL SCAN | PK_DEPT | 4 | 12 | 2 (0) | 00:00:01 |- -.Scott @ TEST > select / * + full (dept) * / deptno from dept 4 rows selected.Execution Plan---Plan hash value: 3383998547 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 4 | 12 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL | DEPT | 4 | 12 | 3 (0) | 00:00:01 |- -

From the output above, you can see that using the above two Hint alone can be effective by Oracle, but this is not the case if the two Hint are used together:

Scott@TEST > select / * + index_ffs (dept pk_dept) full (dept) * / deptno from dept 4 rows selected.Execution Plan---Plan hash value: 2913917002 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 4 | 12 | 1 (0) | 00:00:01 | | 1 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0) | 00:00:01 |- -

From the output above, you can see that the execution plan does not follow the execution plan specified in Hint, but does INDEX FULL SCAN to the primary key index PK_DEPT, indicating that both of the Hint are invalid.

Let's look at the following example:

Scott@TEST > select / * + index_ffs (dept pk_dept) full (dept) cardinality (dept 1000) * / deptno from dept 4 rows selected.Execution Plan---Plan hash value: 2913917002 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1000 | 3000 | 1 (0) | 00:00:01 | | 1 | INDEX FULL SCAN | PK_DEPT | 1000 | 3000 | 1 (0) | 00:00:01 |- -

From the above output, we can see that the execution plan is still INDEX FULL SCAN for the primary key index PK_DEPT, but the cardinality that does INDEX FULL SCAN back to the result set has changed from 4 to 1000, indicating that cardinality (dept 1000) is in effect. It also verifies that if there are other valid Hint in the combined Hint, then these effective Hint will not be affected.

4 the Hint used is interfered by query conversion

Sometimes, query transformation can also cause the relevant Hint to fail, that is, Hint may be ignored by Oracle because it is interfered by query transformation.

Let's take a look at an example where the relevant Hint is ignored by Oracle because of the use of query transformation.

Create a test table jobs

Scott@TEST > create table jobs as select empno,job from emp;Table created.

Construct a SQL

Select / * + ordered cardinality (E100) * / e.ename, j.job, e.sal, v.avg_sal from emp e, jobs j, (select / * + merge * / e.deptno, avg (e.sal) avg_sal from emp e Dept d where d.loc = 'chicago' and d.deptno = e.deptno group by e.deptno) v where e.empno = j.empno and e.deptno = v.deptno and e.sal > v.avg_sal order by e.ename

The SQL above is the SQL associated with two tables (EMP and JOBS) and the embedded view V, which in turn is associated with the tables EMP and DEPT. Three Hint are used in this SQL, of which merge is used to make the embedded view V do view merging. Ordered indicates that the join order of table EMP, JOBS and embedded view V when the above SQL is executed should be the same as the order in which they appear in the SQL text of the SQL, that is, they should do table join in order from left to right.

If the above three Hint are effective, the keyword "VIEW" should not appear in the execution plan of the target SQL (indicating that the view merge is done, reflecting the function of Merge Hint). The join of table EMP, JOBS and embedded view V should become the join of base table EMP and DEPT corresponding to table EMP, JOBS and embedded view V. And the order of join should be EMP- > JOBS- > base table EMP and DEPT corresponding to embedded view V (reflecting the function of Ordered Hint), and the value of Cardinality corresponding to the scan result of table EMP in peripheral query should be 100 (reflecting the function of Cardinality Hint).

Now take a look at the actual situation and execute the SQL above:

Scott@TEST > select / * + ordered cardinality (E100) * / 2 e.ename, j.job, e.sal, v.avg_sal 3 from emp e, 4 jobs j, 5 (select / * + merge * / 6 e.deptno, avg (e.sal) avg_sal 7 from emp e Dept d 8 where d.loc = 'chicago' 9 and d.deptno = e.deptno 10 group by e.deptno) v 11 where e.empno = j.empno 12 and e.deptno = v.deptno 13 and e.sal > v.avg_sal 14 order by e.ename No rows selectedExecution Plan---Plan hash value: 930847561 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | 19656 | 15 (20) | 00:00:01 | | * 1 | FILTER | | 2 | SORT GROUP BY | 19656 | 15 (20) | 00:00:01 | | * 3 | HASH JOIN | 19656 | 14 (15) | 00:00:01 | | * 4 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | * 5 | HASH JOIN | | 53705 | 10 (10) | 00:00:01 | 6 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0) | 00:00: | | * 7 | HASH JOIN | | 8900 | 7 (15) | 00:00:01 | | 8 | TABLE ACCESS FULL | EMP | 5800 | 3 (0) | 00:00:01 | | 9 | TABLE ACCESS FULL | JOBS | 14 | 434 | 3 (0) | 00:00:01 |-|

As you can see from the above execution plan, the keyword "VIEW" does not appear, and the value of the Cardinality corresponding to the scan result of the table EMP is indeed 100, but the join order is not the order mentioned above, but the table DEPT selected first. This means that Merge Hint and Cardinality Hint in the above three Hint are in effect, but Ordered Hint is ignored by Oracle. This is due to the interference of query transformation (view merging of embedded view V is a query transformation).

To prove that the Ordered Hint of the above SQL was ignored by Oracle because of the interference of query transformation, now replace the merge in the embedded view V with no_merge (do not allow the embedded view to do view merge), and execute the SQL again:

Scott@TEST > select / * + ordered cardinality (E100) * / 2 e.ename, j.job, e.sal, v.avg_sal 3 from emp e, 4 jobs j, 5 (select / * + no_merge * / 6 e.deptno, avg (e.sal) avg_sal 7 from emp e Dept d 8 where d.loc = 'chicago' 9 and d.deptno = e.deptno 10 group by e.deptno) v 11 where e.empno = j.empno 12 and e.deptno = v.deptno 13 and e.sal > v.avg_sal 14 order by e.ename No rows selectedExecution Plan---Plan hash value: 2898000699 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 8 | 728 | 14 (22) | 00:00:01 | | 1 | SORT ORDER BY | | 8 | 728 | 14 (22) | 00 | : 00:01 | * 2 | HASH JOIN | 8 | 728 | 13 (16) | 00:00:01 | | * 3 | HASH JOIN | 6500 | 7 (15) | 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 4600 | 3 (0) | | 00:00:01 | | 5 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0) | 00:00:01 | 6 | VIEW | | 5 | 130 | 6 (17) | 00:00:01 | | 7 | HASH GROUP BY | | 5 | 185 | 6 | (17) | 00:00:01 | 8 | MERGE JOIN | | 5 | 185 | 6 (17) | 00:00:01 | * 9 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 2 (0) | 00:00:01 | | 10 | INDEX FULL SCAN | PK_DEPT | 4 | 1 (0) | | 00:00:01 | * 11 | SORT JOIN | | 14 | 364 | 4 (25) | 00:00:01 | | 12 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0) | 00:00:01 |--

As you can see from the above execution plan, the keyword "VIEW" appears, indicating that there is no view merge, and the Cardinality of the table EMP is 100. the join order is the same as previously expected, indicating that the Ordered Hint ignored before the query transformation is disabled is in effect again.

5 the Hint used is interfered by the reserved keyword

Oracle parses Hint in left-to-right order, and if the word encountered is a reserved keyword of Oracle, Oracle ignores the word and all subsequent words; if the encountered word is neither a keyword nor Hint, it ignores the word; if the encountered word is a valid Hint, Oracle retains the Hing.

Because of the above Oracle's principle of parsing Hint, retaining keywords may also invalidate the relevant Hint.

The reserved keyword of Oracle can be found in the view V$RESERVED_WORDS. From the query results below, you can see that',', 'COMMENT',' IS' are all reserved keywords, but "THIS" is not.

Scott@TEST > select keyword,length from v$reserved_words where keyword in (',', 'THIS','IS','COMMENT'); KEYWORD LENGTH- -, 1COMMENT 7IS 2

Let's look at an example of a Hint invalidation caused by a reserved keyword, and execute the following SQL

Scott@TEST > select t1.empnorecy t1.empnorection t2.loc from emp T1 dept t2 where t1.deptno=t2.deptno 14 rows selected.Execution Plan---Plan hash value: 844388907 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 14 | 518 | 6 (17) | 00:00:01 | | 1 | MERGE JOIN | | 14 | 518 | 6 (17) | 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 44 | 2 (0) | 00:00:01 | 3 | INDEX FULL SCAN | PK_DEPT | 4 | 1 (0) | 00:00:01 | * 4 | SORT JOIN | 14 | 364 | 4 (25) | 00:00:01 | 5 | TABLE ACCESS FULL | EMP | | 14 | 364 | 3 (0) | 00:00:01 |-|

From the point of view of the execution plan, it is MERGE SORT JOIN. Add the following Hint to SQL and execute:

Scott@TEST > select / * + use_hash (T1) index (T2 pk_dept) * / t1.empno t1.empno t2.loc from emp T1 where t1.deptno=t2.deptno 14 rows selected.Execution Plan---Plan hash value: 2622 742753 Muhami- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 14 | 518 | 6 (17) | 00:00:01 | | * 1 | HASH JOIN | | 14 | 518 | 6 (17) | 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 44 | 2 (0) | 00:00:01 | 3 | INDEX FULL SCAN | PK_DEPT | 4 | 1 (0) | 00:00:01 | 4 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0) | 00:00:01 |-

From the above execution plan, you can see that both of the Hint are in effect. Emp is the driven table for HASH JOIN and the index PK_DEPT for DEPT tables. Now add','to Hint to view the execution:

Scott@TEST > select / * + use_hash (T1), index (T2 pk_dept) * / t1.empno T1 2.loc from emp T1 dept 2 where t1.deptno=t2.deptno 14 rows selected.Execution Plan---Plan hash value: 615168685 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -- | 0 | SELECT STATEMENT | | 14 | 518 | 7 (15) | 00:00:01 | | * 1 | HASH JOIN | 14 | 518 | 7 (15) | 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 44 | 3 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0) | ) | 00:00:01 |-

As can be seen from the execution plan, it is still HASH JOIN but index (T2 pk_dept) is invalid. Because','is the reserved keyword of Oracle, the index (T2 pk_dept) after','is invalid, then modify the Hint as follows and execute SQL:

Scott@TEST > select / * + comment use_hash (T1) index (T2 pk_dept) * / t1.empno t1.empno t2.loc from emp T1 where t1.deptno=t2.deptno 14 rows selected.Execution Plan---Plan hash value: 844388907 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 14 | 518 | 6 (17) | 00:00:01 | | 1 | MERGE JOIN | | 14 | 518 | 6 (17) | 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 44 | 2 (0) | 00:00:01 | 3 | INDEX FULL SCAN | PK_DEPT | 4 | 1 (0) | 00:00:01 | * 4 | SORT JOIN | 14 | 364 | 4 (25) | 00:00:01 | 5 | TABLE ACCESS FULL | EMP | | 14 | 364 | 3 (0) | 00:00:01 |-|

As you can see from the execution plan, what is going on now is the same as the original execution plan, which means that both of Hint are invalid because they both follow the Oracle retention keyword comment. Modify the Hint and execute the SQL again as follows:

Scott@TEST > select / * + this use_hash (T1) index (T2 pk_dept) * / t1.empno t1.empno t2.loc from emp T1 where t1.deptno=t2.deptno 14 rows selected.Execution Plan---Plan hash value: 2622 742753 Muhami- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 14 | 518 | 6 (17) | 00:00:01 | | * 1 | HASH JOIN | | 14 | 518 | 6 (17) | 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 44 | 2 (0) | 00:00:01 | 3 | INDEX FULL SCAN | PK_DEPT | 4 | 1 (0) | 00:00:01 | 4 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0) | 00:00:01 |-

Now the execution plan is out of the way specified by Hint, indicating that both are in effect, because this is not the reserved keyword of Oracle.

Above, there are five cases in which Hint is ignored by Oracle. You must pay attention to the usage during the use of examples, and use correct and effective Hint to improve the efficiency of SQL execution to prevent Hint from being ignored by Oracle.

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.