The second part of Oracle Hint learning (ignoring hint)
Use parallel hint on non-partitioned indexes:
The following full table scan parallel hint can take effect:
SQL > 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 | | 4 | 12 | 2 (0) | 00:00:01 | 1 | PX COORDINATOR | | 2 | PX SEND QC (RANDOM) |: TQ10000 | 4 | 12 | 2 (0) | 00:00:01 | Q1Mague 00 | P-> S | QC (RAND) | | 3 | PX BLOCK ITERATOR | 4 | 12 | 2 (0) | 00:00:01 | Q1Mague 00 | PCWC | | 4 | TABLE ACCESS FULL | DEPT | 4 | 12 | 2 (0) | 00:00:01 | Q1Mague 00 | PCWP |-| -Statistics- -11 recursive calls 0 db block gets 5 consistent gets 6 physical reads 0 redo size594 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed
It is not valid for non-partitioned indexes to use parallel hint:
SQL > select / * + index (dept pk_dept) parallel (dept 2) * / deptno from dept 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 |- -Statistics--- 62 recursive calls 0 db block gets 59 consistent gets 0 physical reads 0 redo size594 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 4 rows processed
Error specifying driven table in use_hash, invalid hint:
SQL > select / * + use_hash (T2) * / t1.empno t1.enamelanguage t2.loc from emp T1 dept t2 where t1.deptno=t2.deptno and t2.localizedCHICAGO' 6 rows selected.Execution Plan---Plan hash value: 844388907 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 5 | 120 | 6 (17) | 00:00:01 | | 1 | MERGE JOIN | | 5 | 120 | 6 (17) | 00:00:01 | | * 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 2 (0) | | 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 | * 4 | SORT JOIN | | 14 | 182 | 4 (25) | 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0) | 00:00:01 |-| -because hash connections only apply to equivalent associations All hint are invalid: instead, go NLSQL > select / * + use_hash (T1) * / t1.empnoreaget1.ename from emp t2.loc from emp T1 where t1.deptno > = t2.deptno and t2.localizedCHICAGO' 6 rows selected.Execution Plan---Plan hash value: 4192419542Murray- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | 7 | 168 | 6 (0) | 00:00:01 | 1 | NESTED LOOPS | 7 | NESTED LOOPS | 7 | 6 (0) | 00:00:01 | | * 2 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | * 3 | TABLE ACCESS FULL | EMP | 7 | 91 | 3 (0) | 00:00:01 | |
The correct information is as follows:
SQL > select / * + use_hash (T1) * / t1.empno t1.enamelanguage t2.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 | 120 | 6 (0) | 00:00:01 | | * 1 | HASH JOIN | | 5 | 120 | 6 (0) | 00:00:01 | | * 2 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0) | 00:00:01 | |
Self-contradictory hint ignores:
SQL > select / * + full (dept) index_ffs (dept pk_dept) * / deptno from dept
Execution Plan
Plan hash value: 2913917002
| | 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 |
Statistics
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
