看优化文档的时候发现，指定索引的HINT还可以通过列方式。
在9i和以前的版本，索引提示的格式为/*+ index(table_alias) */或/*+ index(table_alias index_name) */，但是在10g中不仅可以通过索引名称来确定HINT的索引，还可以通过指定列名的方式。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database10gEnterpriseEdition Release10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> create table t
2 as select *
3 from dba_objects;
Table created.
SQL> create index ind_t_owner_type
2 on t (owner, object_type);
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all indexed columns size 100')
PL/SQL procedure successfully completed.
SQL> set autot trace exp
SQL> select *
2 from t
3 where wner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 | 2138K| 180 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 23299 | 2138K| 180 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
SQL> select /*+ index(t ind_t_owner_type) */ *
2 from t
3 where wner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 393469706
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 |2138K| 721 (1)| 00:00:11 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 23299 |2138K| 721 (1)| 00:00:11 |
|* 2 | INDEX RANGE SCAN | IND_T_OWNER_TYPE | 23299 | | 44 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
除了常规的索引名称方式，还可以通过owner或owner、object_type列的方式指定：
SQL> select /*+ index(t (t.owner)) */ *
2 from t
3 where wner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 393469706
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 |2138K| 721 (1)| 00:00:11 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 23299 |2138K| 721 (1)| 00:00:11 |
|* 2 | INDEX RANGE SCAN | IND_T_OWNER_TYPE | 23299 | | 44 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
SQL> select /*+ index(t (owner, object_type)) */ *
2 from t
3 where wner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 393469706
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 |2138K| 721 (1)| 00:00:11 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 23299 |2138K| 721 (1)| 00:00:11 |
|* 2 | INDEX RANGE SCAN | IND_T_OWNER_TYPE | 23299 | | 44 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
可以通过索引的所有列来指定，也可以通过前缀列的方式，索引列前面可以添加表的别名，如果省略则默认是前面指定别名的表。
但是，不能通过非前缀列指定索引，甚至指定列的次序与索引列顺序不一致都会导致HINT失效：
SQL> select /*+ index(t (object_type)) */ *
2 from t
3 where wner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 | 2138K| 180 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 23299 | 2138K| 180 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
SQL> select /*+ index(t (object_type, owner)) */ *
2 from t
3 where wner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 | 2138K| 180 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 23299 | 2138K| 180 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
