在非分区索引上使用并行hint：
如下全表扫描并行hint可以生效：
SQL> select /*+ full(dept) parallel(dept 2) */deptno from dept; Execution Plan ---------------------------------------------------------- Plan hash value: 587379989 ------------------------------------------------------------------------------------------------------------- | 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 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR || 4 | 12 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| DEPT| 4 | 12 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 11 recursive calls 0 db block gets 5 consistent gets 6 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
非分区索引使用并行hint无效：
SQL> select /*+ index(dept pk_dept) parallel(dept 2) */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 ---------------------------------------------------------- 62 recursive calls 0 db block gets 59 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 7 sorts (memory) 0 sorts (disk) 4 rows processed
use_hash中指定被驱动表错误，hint无效：
SQL> select /*+ use_hash(t2) */ t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO'; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | 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 | ----------------------------------------------------------------------------------------
因hash连接只适用等值关联，所有hint无效：而走NL SQL> select /*+ use_hash(t1) */ t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno>=t2.deptno and t2.loc='CHICAGO'; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4192419542 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | |7 | 168 |6 (0)| 00:00:01 | | 1 | NESTED LOOPS | |7 | 168 |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 |
---------------------------------------------------------------------------
正确如下：
SQL> select /*+ use_hash(t1) */ t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO'; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 615168685 --------------------------------------------------------------------------- | 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 |
---------------------------------------------------------------------------
自相矛盾的hint会忽略掉：
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
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
