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