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 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