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

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

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.