在非分区索引上使用并行hint：
如下全表扫描并行hint可以生效：
SQL> select 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 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 processeduse_hash中指定被驱动表错误，hint无效：
SQL> select 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 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 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 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
