在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', 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，需要先测试，有可能不能达到预期效果