Oracle Hint学习之二（忽略hint的情形）

1. 在非分区索引上使用并行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 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

非分区索引使用并行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 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

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无效：而走NLSQL> 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