Hint学习_01_Hint基本格式

1. Hint必须以如下格式出现在SQL文本中
/*+ <具体的Hint内容> */

2. Hint在SQL文本中必须紧跟关键字SELECT、INSERT、UPDATE、DELETE或MERGE之后
{SELECT | INSERT | UPDATE | DELETE | MERGE} /*+ <具体的Hint内容> */

3. Hint中第一个星号(*)和加号(+)之间不能有空格
4. Hint中加号(+)和具体的Hint内容之间可以有空格，也可以没有空格，但通常都有空格。
5. Hint中的具体内容可以是单个Hint，也可以是多个Hint的组合，如果是后者，则各个Hint间至少需要1个空格来彼此分隔。

6. Hint中指定具体对象时(比如指定表名或者索引名)，不能带上该对象所在SCHEMA的名称，即使该SQL文本中已经有对应的SCHEMA名称。

7. Hint中指定具体表名时，如果该表在对应SQL文本中有别名(alias)，则应该使用该表的别名。

1. Hint必须以如下格式出现在SQL文本中 /*+ <具体的Hint内容> */

全表扫描  /*+ full(目标表) */

set autotrace traceonly
select * from t where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1718802751

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_T_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


select /*+ full(t) */* from t where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |  6097 |  1232K|   190  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| T    |  6097 |  1232K|   190  (11)| 00:00:01 |
----------------------------------------------------------------------------------

2. Hint在SQL文本中必须紧跟关键字SELECT、INSERT、UPDATE、DELETE或MERGE之后

select */*+ full(t) */ from t where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1718802751

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_T_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

3. Hint中第一个星号(*)和加号(+)之间不能有空格

select /* + full(t) */* from t where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1718802751

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_T_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

4. Hint中加号(+)和具体的Hint内容之间可以有空格，也可以没有空格，但通常都有空格。

select /*+full(t)*/* from t where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |  6097 |  1232K|   190  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| T    |  6097 |  1232K|   190  (11)| 00:00:01 |
----------------------------------------------------------------------------------

6. Hint中指定具体对象时(比如指定表名或者索引名)，不能带上该对象所在SCHEMA的名称，即使该SQL文本中已经有对应的SCHEMA名称。

select /*+ full(t) */* from testzyl.t where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |  6097 |  1232K|   190  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| T    |  6097 |  1232K|   190  (11)| 00:00:01 |
----------------------------------------------------------------------------------

select /*+ full(testzyl.t) */* from testzyl.t where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1718802751

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_T_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

7. Hint中指定具体表名时，如果该表在对应SQL文本中有别名(alias)，则应该使用该表的别名。

select /*+ full(t) */* from t t1 where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1718802751

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_T_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

select /*+ full(t1) */* from t t1 where object_id=100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |  6097 |  1232K|   190  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| T    |  6097 |  1232K|   190  (11)| 00:00:01 |
----------------------------------------------------------------------------------