强制索引的方法

一般来说强制索引都是采用/*+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>

因此，如果索引的名字经常改变，在编码的时候尽量采用索引列提示的方法。如果索引的列顺序经常改变，在编码的时候尽量采用索引名提示的方法。