Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2885636
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2012-03-09 10:54:39


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

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

 
阅读(15300) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~