Local prefixed index和Local nonprefixed index对select语句的性能影响分析

1、搞清楚两种索引的概念

在比较两种索引对select产生的影响之前，先要搞清楚，什么是Local prefixed index，什么叫Local nonprefixed index。其实，这两种索引，都是属于分区local索引，所以，这两种类型的索引，只有可能在分区表上才会出现。

1.1 什么是Local prefixed index

是指索引中的列，就是分区表的分区键列，或者是索引中的列，包含表的分区键值列，并且为前置位置在索引最前部位置的本地分区索引。例如，emp表是按时间范围分区的表，分区键列是create_time，如果分区索引中的列为create_time，或是以（create_time,emp_no）列的本地复合索引

1.2 什么是Local nonprefixed index

在理解了什么是Local prefixedindex后，再来理解什么是Local nonprefixed index就容易了。是指索引中的列，未包含分区表的分区键列，或者是分区键值列不在前置位置的本地分区索引例如，emp表是按时间范围分区的表，分区键列是create_time，如果分区索引中的列为不包含create_time列，或者是象（emp_no ,create_time）这种create_time列不在索引前置位置的本地分区索引

2、如何查询索引的类型

视图：DBA_PART_INDEXES LOCALITY字段:记录是否为LOCAL索引 ALIGNMENT字段：记录是PREFIXED索引还是NON_PREFIXED索引

3、准备与验证测试环境

3.1 创建分区表

create table tivoli.li_db_session_t（dbname varchar2（10）,allsess number（10）,activess number（10）,timstap date）partition by range（timstap）（PARTITION ONEIDX_MINVALUESLESSTHAN（to_date（"2010-08-01","yyyy-mm-dd"））, PARTITION ONEIDX_2010_08_1 VALUES LESS THAN（to_date（"2010-09-01","yyyy-mm-dd"））, PARTITION ONEIDX_2010_08_2 VALUES LESS THAN（to_date（"2010-09-15","yyyy-mm-dd"））, PARTITION ONEIDX_2010_09_1 VALUES LESS THAN（to_date（"2010-10-01","yyyy-mm-dd"））, PARTITION ONEIDX_2010_09_2 VALUES LESS THAN（to_date（"2010-10-15","yyyy-mm-dd"））, PARTITION ONEIDX_2010_10_1 VALUES LESS THAN（to_date（"2010-11-01","yyyy-mm-dd"））, PARTITION ONEIDX_2010_10_2 VALUES LESS THAN（to_date（"2010-11-15","yyyy-mm-dd"））, PARTITION ONEIDX_2010_11_1 VALUES LESS THAN（to_date（"2010-12-01","yyyy-mm-dd"））, PARTITION ONEIDX_2010_11_2 VALUES LESS THAN（to_date（"2010-12-15","yyyy-mm-dd"））, PARTITION ONEIDX_2010_12_1 VALUES LESS THAN（to_date（"2011-01-01","yyyy-mm-dd"））, PARTITION ONEIDX_2010_12_2 VALUES LESS THAN（to_date（"2011-01-15","yyyy-mm-dd"））, PARTITION ONEIDX_2011_01_1 VALUES LESS THAN（to_date（"2011-02-01","yyyy-mm-dd"））, PARTITION ONEIDX_2011_01_2 VALUES LESS THAN（to_date（"2011-02-15","yyyy-mm-dd"））, PARTITION ONEIDX_2011_02_1 VALUES LESS THAN（to_date（"2011-03-01","yyyy-mm-dd"））, PARTITION ONEIDX_2011_02_2 VALUES LESS THAN（to_date（"2011-03-15","yyyy-mm-dd"））, PARTITION ONEIDX_2011_03_1 VALUES LESS THAN（to_date（"2011-04-01","yyyy-mm-dd"））, PARTITION ONEIDX_2011_03_2 VALUES LESS THAN（to_date（"2011-04-15","yyyy-mm-dd"））, PARTITION ONEIDX_2011_04_1 VALUES LESS THAN（to_date（"2011-05-01","yyyy-mm-dd"））, PARTITION ONEIDX_2011_04_2 VALUES LESS THAN（to_date（"2011-05-15","yyyy-mm-dd"））, PARTITION ONEIDX_MAX VALUES LESS THAN（MAXVALUE））;

插入4406727行数据，整个表大小为312MB。

3.2 创建五种场景的索引

--Local prefixed index类型一：createindex Tivoli.li_idx_01ontivoli.li_db_session_t（timstap）localnologging;--Local prefixed index类型二：createindex Tivoli.li_idx_02on tivoli.li_db_session_t（timstap,dbname,allsess,activess）localnologging;--Local nonprefixed index类型一：create index Tivoli.li_idx_03on tivoli.li_db_session_t（dbname,allsess,activess）localnologging;--Local nonprefixed index类型二：create index Tivoli.li_idx_04on tivoli.li_db_session_t（dbname,allsess,timstap,activess）localnologging;--全局索引：（该索引，由于字段与Tivoli.li_idx_04安全一致，所以，无法两个索引并存，需要先删除Tivoli.li_idx_04后，才能创建Tivoli.li_idx_05索引）create index Tivoli.li_idx_05on tivoli.li_db_session_t（dbname,allsess,timstap,activess）;

3.3 对表与索引进行统计分析

begin dbms_stats.gather_table_stats（ownname=>"TIVOLI",tabname=>"LI_DB_SESSION_T",estimate_percent=>50,cascade=>TRUE）;end;

3.4 验证所创建索引的类型

select index_name,partitioning_type,subpartitioning_type,locality,alignment from DBA_PART_INDEXES where index_namein（"LI_IDX_01","LI_IDX_02","LI_IDX_03","LI_IDX_04","LI_IDX_05"）

输出结果如下：

INDEX_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE	LOCALITY	ALIGNMENT
LI_IDX_01	RANGE	NONE	LOCAL	PREFIXED
LI_IDX_02	RANGE	NONE	LOCAL	PREFIXED
LI_IDX_03	RANGE	NONE	LOCAL	NON_PREFIXED
LI_IDX_04	RANGE	NONE	LOCAL	NON_PREFIXED

LI_IDX_05因为还没有创建所以查询没有结果，实际上，如果LI_IDX_05不是分区索引，所以，即便该索引建立起来了，在DBA_PART_INDEXES视图中也不会出现。

4、五种索引类型下的性能对比

以一条select语句为测试语句。

4.1 场景一：local prefixed类型，索引列为表分区键列

SQL> set autotrace traceonlySQL> set linesize 999SQL> select /*+ index（t li_idx_01）*/ * from tivoli.li_db_session_t t where t.allsess=28 and t.dbname="COSTDB" and t.timstap >to_date（"2011-01-01","yyyy-mm-dd"） and t.timstap < to_date（"2011-01-20","yyyy-mm-dd"） and t.activess=4; 498 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3409921846----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 200 | 208K （1）| 00:41:38 | | || 1 | PARTITION RANGE ITERATOR | | 10 | 200 | 208K （1）| 00:41:38 | 11 | 12 ||* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| LI_DB_SESSION_T | 10 | 200 | 208K （1）| 00:41:38 | 11 | 12 ||* 3 | INDEX RANGE SCAN | LI_IDX_01 | 630K| | 1681 （1）| 00:00:21 | 11 | 12 |----------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 2 - filter（"T"."ALLSESS"=28 AND "T"."DBNAME"="COSTDB" AND "T"."ACTIVESS"=4） 3 - access（"T"."TIMSTAP">TO_DATE（" 2011-01-01 00:00:00", "syyyy-mm-dd hh24:mi:ss"） AND "T"."TIMSTAP"<TO_DATE（" 2011-01-20 00:00:00", "syyyy-mm-dd hh24:mi:ss"））Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 262334 consistent gets 0 physical reads 0 redo size 9997 bytes sent via SQL*Net to client 886 bytes received via SQL*Net from client 35 SQL*Net roundtrips to/from client 0 sorts （memory） 0 sorts （disk） 498 rows processed

点评：先是对LI_IDX_01索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，然后通过索引中的ROWID回表（TABLE ACCESS BY LOCAL INDEX ROWID），consistent gets达到262334，代价很高。

4.2 场景二：local prefixed类型，多列索引，表分区键列为前置位置

SQL> select /*+ index（t li_idx_02）*/ * from tivoli.li_db_session_t t where t.allsess=28 and t.dbname="COSTDB" and t.timstap >to_date（"2011-01-01","yyyy-mm-dd"） and t.timstap < to_date（"2011-01-20","yyyy-mm-dd"） and t.activess=4; 498 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 3413193479------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 200 | 2783 （1）| 00:00:34 | | || 1 | PARTITION RANGE ITERATOR| | 10 | 200 | 2783 （1）| 00:00:34 | 11 | 12 ||* 2 | INDEX RANGE SCAN | LI_IDX_02 | 10 | 200 | 2783 （1）| 00:00:34 | 11 | 12 |------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 2 - access（"T"."TIMSTAP">TO_DATE（" 2011-01-01 00:00:00", "syyyy-mm-dd hh24:mi:ss"） AND "T"."DBNAME"="COSTDB" AND "T"."ALLSESS"=28 AND "T"."ACTIVESS"=4 AND "T"."TIMSTAP"<TO_DATE（" 2011-01-20 00:00:00", "syyyy-mm-dd hh24:mi:ss"）） filter（"T"."ALLSESS"=28 AND "T"."DBNAME"="COSTDB" AND "T"."ACTIVESS"=4） Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 3141 consistent gets 3099 physical reads 0 redo size 9997 bytes sent via SQL*Net to client 886 bytes received via SQL*Net from client 35 SQL*Net roundtrips to/from client 0 sorts （memory） 0 sorts （disk） 498 rows processed

点评：先是对LI_IDX_02索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，需要返回的数据，access（访问）和filter（过滤）全部在索引中完成，不需要回表，consistent gets为3141，比使用LI_IDX_01索引的consistent gets小83倍。

4.3 场景三：localnonprefixed类型，索引列中不包含表分区键列

SQL> select /*+ index（t li_idx_03）*/ * from tivoli.li_db_session_t t where t.allsess=28 and t.dbname="COSTDB" and t.timstap >to_date（"2011-01-01","yyyy-mm-dd"） and t.timstap < to_date（"2011-01-20","yyyy-mm-dd"） and t.activess=4; 498 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 3955115924----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 200 | 98 （0）| 00:00:02 | | || 1 | PARTITION RANGE ITERATOR | | 10 | 200 | 98 （0）| 00:00:02 | 11 | 12 ||* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| LI_DB_SESSION_T | 10 | 200 | 98 （0）| 00:00:02 | 11 | 12 ||* 3 | INDEX RANGE SCAN | LI_IDX_03 | 136 | | 5 （0）| 00:00:01 | 11 | 12 |---------------------------------------------------------------------------------------------------------------------- Predicate Information （identified by operation id）:--------------------------------------------------- 2 - filter（"T"."TIMSTAP">TO_DATE（" 2011-01-01 00:00:00", "syyyy-mm-dd hh24:mi:ss"） AND "T"."TIMSTAP"<TO_DATE（" 2011-01-20 00:00:00", "syyyy-mm-dd hh24:mi:ss"）） 3 - access（"T"."DBNAME"="COSTDB" AND "T"."ALLSESS"=28 AND "T"."ACTIVESS"=4） Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 658 consistent gets 174 physical reads 0 redo size 13309 bytes sent via SQL*Net to client 886 bytes received via SQL*Net from client 35 SQL*Net roundtrips to/from client 0 sorts （memory） 0 sorts （disk） 498 rows processed

点评：先是对LI_IDX_03索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，然后通过索引中的ROWID回表（TABLE ACCESS BY LOCAL INDEX ROWID），consistent gets为658。

4.4 场景四：localnonprefixed类型，多列索引，表分区键列不为前置位置

SQL> select /*+ index（t li_idx_04）*/ * from tivoli.li_db_session_t t where t.allsess=28 and t.dbname="COSTDB" and t.timstap >to_date（"2011-01-01","yyyy-mm-dd"） and t.timstap < to_date（"2011-01-20","yyyy-mm-dd"） and t.activess=4; 498 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 3237585467------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 200 | 5 （0）| 00:00:01 | | || 1 | PARTITION RANGE ITERATOR| | 10 | 200 | 5 （0）| 00:00:01 | 11 | 12 ||* 2 | INDEX RANGE SCAN | LI_IDX_04 | 10 | 200 | 5 （0）| 00:00:01 | 11 | 12 |------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 2 - access（"T"."DBNAME"="COSTDB" AND "T"."ALLSESS"=28 AND "T"."TIMSTAP">TO_DATE（" 2011-01-01 00:00:00", "syyyy-mm-dd hh24:mi:ss"） AND "T"."ACTIVESS"=4 AND "T"."TIMSTAP"<TO_DATE（" 2011-01-20 00:00:00", "syyyy-mm-dd hh24:mi:ss"）） filter（"T"."ACTIVESS"=4）Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 43 consistent gets 9 physical reads 0 redo size 9997 bytes sent via SQL*Net to client 886 bytes received via SQL*Net from client 35 SQL*Net roundtrips to/from client 0 sorts （memory） 0 sorts （disk） 498 rows processed

点评：先是对LI_IDX_04索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，access（访问）和filter（过滤）全部在索引中完成，不需要回表，consistent gets只有43，代价极小。

4.5 场景五：global index（全局索引）

SQL> select /*+ index（t li_idx_05）*/ * from tivoli.li_db_session_t t where t.allsess=28 and t.dbname="COSTDB" and t.timstap >to_date（"2011-01-01","yyyy-mm-dd"） and t.timstap < to_date（"2011-01-20","yyyy-mm-dd"） and t.activess=4; 498 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 1711410678------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 200 | 3 （0）| 00:00:01 ||* 1 | INDEX RANGE SCAN| LI_IDX_05 | 10 | 200 | 3 （0）| 00:00:01 |------------------------------------------------------------------------------ Predicate Information （identified by operation id）:--------------------------------------------------- 1 - access（"T"."DBNAME"="COSTDB" AND "T"."ALLSESS"=28 AND "T"."TIMSTAP">TO_DATE（" 2011-01-01 00:00:00", "syyyy-mm-dd hh24:mi:ss"） AND "T"."ACTIVESS"=4 AND "T"."TIMSTAP"<TO_DATE（" 2011-01-20 00:00:00", "syyyy-mm-dd hh24:mi:ss"）） filter（"T"."ACTIVESS"=4）Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 41 consistent gets 6 physical reads 0 redo size 9997 bytes sent via SQL*Net to client 886 bytes received via SQL*Net from client 35 SQL*Net roundtrips to/from client 0 sorts （memory） 0 sorts （disk） 498 rows processed

点评：先是对LI_IDX_05索引做PARTITIONRANGE ITERATOR 的INDEX RANGE SCAN，access（访问）和filter（过滤）全部在索引中完成，不需要回表，consistent gets只有41，五种不同类型应用中，代价最小。

4.6 场景六：where条件中不带分区列，但是使用不含分区列的LOCAL-NON_PREFIXED索引

SQL> select /*+ index（t li_idx_03）*/ * from tivoli.li_db_session_t t where t.allsess=28 and t.dbname="COSTDB" and t.activess=4; --上面语句，where条件中不含表的分区列2346 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 1367932018----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 136 | 2720 | 134 （0）| 00:00:02 | | || 1 | PARTITION RANGE ALL | | 136 | 2720 | 134 （0）| 00:00:02 | 1 | 20 || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| LI_DB_SESSION_T | 136 | 2720 | 134 （0）| 00:00:02 | 1 | 20 ||* 3 | INDEX RANGE SCAN | LI_IDX_03 | 136 | | 41 （0）| 00:00:01 | 1 | 20 |----------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 3 - access（"T"."DBNAME"="COSTDB" AND "T"."ALLSESS"=28 AND "T"."ACTIVESS"=4）Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 1869 consistent gets 1046 physical reads 0 redo size 59842 bytes sent via SQL*Net to client 2239 bytes received via SQL*Net from client 158 SQL*Net roundtrips to/from client 0 sorts （memory） 0 sorts （disk） 2346 rows processed

5、性能对比小结

5.1 五种索引性能对比

（1）SQL语句：select * from tivoli.li_db_session_t t where t.allsess=28 andt.dbname="COSTDB" and t.timstap >to_date（"2011-01-01","yyyy-mm-dd"） andt.timstap < to_date（"2011-01-20","yyyy-mm-dd"） and t.activess=4;

序号	使用索引名称	索引特点	索引类型	是否有回表	time	consistent gets
1	LI_IDX_01	只有分区字段列的local索引	LOCAL-PREFIXED	是	0:41:38	262334
2	LI_IDX_02	分区字段列为索引首位，索引中的列包含where条件中的所有列的local索引	LOCAL-PREFIXED	否	0:00:34	3141
3	LI_IDX_03	不包含分区字段列的local索引，索引字段包含where条件中除分区列以外的所有列	LOCAL-NON_PREFIXED	是	0:00:02	658
4	LI_IDX_04	包含where条件中的所有列，包括分区字段列，但是分区字段列非首位的local索引	LOCAL-NON_PREFIXED	否	0:00:01	43
5	LI_IDX_05	包含where条件中的所有列，包括分区字段列，但是分区字段列非首位的全局索引	GLOBAL	否	0:00:01	41

（2）SQL语句二：select /*+ index（t li_idx_03）*/ * fromtivoli.li_db_session_t t where t.allsess=28 and t.dbname="COSTDB" andt.activess=4;

序号	使用索引名称	索引特点	索引类型	是否有回表	time	consistent gets
1	LI_IDX_03	不包含分区字段列的local索引，??引字段包含where条件中除分区列以外的所有列	LOCAL-NON_PREFIXED	是	0:00:02	1869

5.2 小结 （1）从上面性能对比数据来看，GLOBAL索引的性能最好，但是由于GLOBAL索引在删除分区后索引会失效，所以分区表上不建议使用GLOBAL索引。（2）关于LOCAL-PREFIXED与LOCAL-NON_PREFIXED索引：如果查询条件包含索引的所有列，LOCAL-NON_PREFIXED索引索引性能优化于LOCAL-PREFIXED索引，只包含分区字段列的LOCAL-PREFIXED索引性能最差（3）不包含分区字段列的LOCAL-NON_PREFIXED索引（如LI_IDX_03），一定是要在where条件中带有分区字段列做为条件的情况下，效果才会理想，如果where条件中不包含分区字段列，就不应该使用LOCAL索引，全局索引的效果会远远优于不包含分区字段列的LOCAL-NON_PREFIXED索引更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址