Hint作为一个指示，相当一个注释，来辅助优化，指示优化器，使用方法就是放在SQL语句第一个关键字的后面。

8i之后，oracle优化是依据CBO模式。其中简单地说，访问表的方法有两种：全表扫描与索引扫面，表的连接方式有3种：
排序合并连接（Sort Merge Join），嵌套循环连接（Nested Loops Join），哈希连接（Hash Join）。通过hint
指示优化器能修改执行计划，以下是简单的测试过程。

----hint的使用：

---索引范围扫描的查询：

hr@PROD>select * from employees where job_id='IT_PROG';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1019430118

 

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |     5 |   345 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |     5 |   345 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_JOB_IX |     5 |       |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("JOB_ID"='IT_PROG')

 

---修改走全表扫描：
hr@PROD>select /*+ full(t) */ * from employees t

  2  where job_id='IT_PROG';

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |     5 |   345 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     5 |   345 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("JOB_ID"='IT_PROG')

 

---全表扫描查询：

hr@PROD>select * from employees t where department_id<=110;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |   106 |  7314 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   106 |  7314 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("DEPARTMENT_ID"<=110)

 

---修改为索引范围扫描：

hr@PROD>select /*+INDEX(t EMP_DEPARTMENT_IX)*/ * from employees t

  2  where department_id<=110;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2056577954

-------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |   106 |  7314 |     8   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |   106 |  7314 |     8   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |   106 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("DEPARTMENT_ID"<=110)

#全表扫描比索引扫描开销更大。

 

 

---测试该查询什么时候会走索引范围扫描：

hr@PROD>select * from employees t where department_id<=70;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |    61 |  4209 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |    61 |  4209 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("DEPARTMENT_ID"<=70)

 

hr@PROD>select * from employees t where department_id<=40;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2056577954

 

-------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |    10 |   690 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   690 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DEPARTMENT_ID"<=40)

 

hr@PROD>select * from employees t where department_id<=50;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |    56 |  3864 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |    56 |  3864 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("DEPARTMENT_ID"<=50)

#可以看到，该查询方案中，当查询记录超过总记录的一般的时候，就会走全表扫描。 

---不使用索引扫描：

hr@PROD>select * from employees t where department_id=100;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2056577954

-------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |     6 |   414 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     6 |   414 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     6 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DEPARTMENT_ID"=100)

 

---指定不走索引扫描：

hr@PROD>select /*+NO_INDEX(T EMP_DEPARTMENT_IX)*/ * from employees t

  2  where department_id=100;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |     6 |   414 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("DEPARTMENT_ID"=100)

 

 

---merge jion连接的查询：

hr@PROD>select * from employees d,departments t

  2  where d.department_id=t.department_id;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1343509718

 

--------------------------------------------------------------------------------------------

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |             |   106 |  9540 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN                  |             |   106 |  9540 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   567 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |             |   107 |  7383 |     4  (25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  7383 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPARTMENT_ID"="T"."DEPARTMENT_ID")

       filter("D"."DEPARTMENT_ID"="T"."DEPARTMENT_ID")

 

---修改使用nested loops 连接：

hr@PROD>select /*+ USE_NL(d,t)*/ * from employees d,departments t

  2  where d.department_id=t.department_id;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1021246405

--------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                   |   106 |  9540 |    14   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |                   |   106 |  9540 |    14   (0)| 00:00:01 |

|   2 |   NESTED LOOPS               |                   |   270 |  9540 |    14   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL         | DEPARTMENTS       |    27 |   567 |     3   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     4 |   276 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPARTMENT_ID"="T"."DEPARTMENT_ID")

 

---修改使用hash jion连接方式：

hr@PROD>select /*+ USE_HASH(d,t)*/ * from employees d,departments t

  2   where d.department_id=t.department_id;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2052257371

----------------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |             |   106 |  9540 |     6   (0)| 00:00:01 |

|*  1 |  HASH JOIN         |             |   106 |  9540 |     6   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   567 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  7383 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("D"."DEPARTMENT_ID"="T"."DEPARTMENT_ID")

 

#同样可以将hash jion改为merge jion。