联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在9i和以前的版本，索引提示的格式为/*+ index(table_alias) */或/*+ index(table_alias index_name) */，但是在10g中不仅可以通过索引名称来确定HINT的索引，还可以通过指定列名的方式，格式为/*+ index(table_alias (column_names)) */
SQL> create table test_hint
2 as
3 select * from dba_objects;
Table created.
SQL> create index ind_hint on test_hint(owner,object_type);
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'TEST_HINT',
2 method_opt => 'for all indexed columns size 100',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autot trace exp
SQL> select * from test_hint where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 11101196
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23272 | 2113K| 161 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST_HINT | 23272 | 2113K| 161 (1)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
SQL> select /*+index(a)*/ * from test_hint a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 890897193
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23272 | 2113K| 1122 (1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 | 2113K| 1122 (1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | IND_HINT | 23272 | | 84 (0)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
SQL> select /*+index(a ind_hint)*/ * from test_hint a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 890897193
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23272 | 2113K| 1122 (1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 | 2113K| 1122 (1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | IND_HINT | 23272 | | 84 (0)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
SQL> select /*+index(a (owner,object_type))*/ * from test_hint a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 890897193
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23272 | 2113K| 1122 (1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 | 2113K| 1122 (1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | IND_HINT | 23272 | | 84 (0)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
--指定和index完全一致的列，走index
SQL> select /*+index(a (owner))*/ * from test_hint a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 890897193
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23272 | 2113K| 1122 (1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 | 2113K| 1122 (1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | IND_HINT | 23272 | | 84 (0)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
--指定列和where条件一致，也可以使用该index
SQL> select /*+index(a (object_id))*/ * from test_hint a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 11101196
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23272 | 2113K| 161 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST_HINT | 23272 | 2113K| 161 (1)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
--虽然是index中的一个列，但是由于不是where条件中，所以不能被使用
SQL> select /*+index(a (owner))*/ * from test_hint a where object_type = 'TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 1755360976
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1752 | 159K| 104 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HINT | 1752 | 159K| 104 (0)| 00:00:02 |
|* 2 | INDEX SKIP SCAN | IND_HINT | 1752 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
filter("OBJECT_TYPE"='TABLE')
--指定index的第一列，虽然不在where中，但是还是会使用index
--说明：使用/*+ index(table_alias (column_names)) */方式的hint，需要先测试，有可能不能达到预期效果