爱华网一般来说强制索引都是采用/*+INDEX(表名,索引名字)*/ 的方法,但是这种方法有个缺点，如果索引的名字改变了，就会导致程序重新改动，大大增加维护成本。
其实索引提示还可以使用列的方法进行,语法是/*+INDEX(表名,(索引列的列表))*/,这种方法即使索引的名字变了，也不会导致程序重新改动，除非索引列的顺序变化了。
看如下一个简单例子：
SQL> create table test as select * from all_objects;
Table created.
SQL> create index idx_test_01 on test(object_name);
Index created.
SQL> create index idx_test_02 on test(owner,object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade=>true);
PL/SQL procedure successfully completed.
首先创建了一个表TEST，并且建立了2个索引，然后收集了统计信息。
SQL> select * from test where owner='SYS' and object_name='DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 3384190782
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 238 | 26656 | 35 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 238 | 26656 | 35 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 952
| | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
2 - access("OBJECT_NAME"='DBA_OBJECTS')
由于OBJECT_NAME的可选择性比OWNER好很多，因此执行计划选择了走索引IDX_TEST_01 。 下面我们采用强制索引提示让其走索引IDX_TEST_02，
SQL> select /*+index(test,idx_test_02)*/ * from test where owner='SYS' and object_name='DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1715650972
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 238 | 26656 | 3284 (1)| 00:00:40 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 238 | 26656 | 3284 (1)| 00:00:40 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_02 | 97844
| | 326 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBA_OBJECTS')
2 - access("OWNER"='SYS')
这种强制索引提示的方法是普遍采用的方法，但是这种方法在索引名字改变后，就会导致提示的失效。
SQL> alter index IDX_TEST_02 rename to IDX_TEST_03;
Index altered.
SQL> select /*+index(test,idx_test_02)*/ * from test where owner='SYS' and object_name='DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 3384190782
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 238 | 26656 | 35 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 238 | 26656 | 35 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 952
| | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
2 - access("OBJECT_NAME"='DBA_OBJECTS')
在把索引的名字idx_test_02重新命名为idx_test_03后，索引提示
/*+index(test,idx_test_02)*/ 已经失效了。
如果采用列的方法，索引名字的更改不会导致提示的失效。
SQL> select /*+index(test,(owner,object_id))*/ * from test where owner='SYS' and object_name='DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 883341653
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 238 | 26656 | 3284 (1)| 00:00:40 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 238 | 26656 | 3284 (1)| 00:00:40 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_03 | 97844
| | 326 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBA_OBJECTS')
2 - access("OWNER"='SYS')
SQL> alter index idx_test_03 rename to idx_test_02;
Index altered.
SQL> select /*+index(test,(owner,object_id))*/ * from test where owner='SYS' and object_name='DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1715650972
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 238 | 26656 | 3284 (1)| 00:00:40 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 238 | 26656 | 3284 (1)| 00:00:40 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_02 | 97844
| | 326 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBA_OBJECTS')
2 - access("OWNER"='SYS')
可以看到即使索引的名字改变了，我们还是能用到我们想要的索引。
使用索引列提示的时候要注意：必须把索引的前导列放在前面，在本例中采用前导列即使OWNER，如果采
用/*+index(test,(object_id,owner))*/ 提示就不起作用。
SQL> select /*+index(test,(object_id,owner))*/ * from test where owner='SYS' and object_name='DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 3384190782
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 238 | 26656 | 35 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 238 | 26656 | 35 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 952
| | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
2 - access("OBJECT_NAME"='DBA_OBJECTS')
在某些情况下，只带前导列即可。
SQL> select /*+index(test,(owner))*/ * from test where owner='SYS' and
object_name='DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1715650972
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 238 | 26656 | 3284 (1)| 00:00:40 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 238 | 26656 | 3284 (1)| 00:00:40 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_02 | 97844
| | 326 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBA_OBJECTS')
2 - access("OWNER"='SYS')
如果创建索引列的顺序变了，就将会导致提示的失效(如果采用索引名提示可能会失效也可能不会失效)：
SQL> drop index idx_test_02;
Index dropped.
SQL> create index idx_test_02 on test(object_id,owner);
Index created.
SQL> select /*+index(test,(owner,object_id))*/ * from test where owner='SYS' and object_name='DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 3384190782
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 238 | 26656 | 35 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 238 | 26656 | 35 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 952
| | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
2 - access("OBJECT_NAME"='DBA_OBJECTS')
SQL>
因此，如果索引的名字经常改变，在编码的时候尽量采用索引列提示的方法。如果索引的列顺序经常改变，在编码的时候尽量采用索引名提示的方法。
百度搜索“爱华网”,专业资料,生活学习,尽在爱华网
