在比较两种索引对select产生的影响之前，先要搞清楚，什么是Local prefixed index，什么叫Local nonprefixed index。其实，这两种索引，都是属于分区local索引，所以，这两种类型的索引，只有可能在分区表上才会出现。
是指索引中的列，就是分区表的分区键列，或者是索引中的列，包含表的分区键值列，并且为前置位
置在索引最前部位置的本地分区索引。
例如，emp表是按时间范围分区的表，分区键列是create_time，如果分区索引中的列为create_time，
或是以（create_time,emp_no）列的本地复合索引
在理解了什么是Local prefixedindex后，再来理解什么是Local nonprefixed index就容易了。
是指索引中的列，未包含分区表的分区键列，或者是分区键值列不在前置位置的本地分区索引
例如，emp表是按时间范围分区的表，分区键列是create_time，如果分区索引中的列为不包含create_time列，或者是象（emp_no ,create_time）这种create_time列不在索引前置位置的本地分区索引
视图：DBA_PART_INDEXES
LOCALITY字段:记录是否为LOCAL索引
ALIGNMENT字段：记录是PREFIXED索引还是NON_PREFIXED索引
|
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。
|
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; 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); |
|
begin dbms_stats.gather_table_stats(ownname=>‘TIVOLI‘,tabname=>‘LI_DB_SESSION_T‘,estimate_percent=>50,cascade=>TRUE); end; |
|
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视图中也不会出现。
以一条select语句为测试语句。
|
SQL> set linesize 999 SQL> 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，代价很高。
|
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倍。
|
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。
|
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，代价极小。
|
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，五种不同类型应用中，代价最小。
|
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 |
（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索引
本文作者：黎俊杰（网名：踩点），从事”系统架构、操作系统、存储设备、数据库、中间件、应用程序“六个层面系统性的性能优化工作
欢迎加入 系统性能优化专业群，共同探讨性能优化技术。群号：258187244
Local prefixed index和Local nonprefixed index对select语句的性能影响分析
原文：http://blog.csdn.net/ljunjie82/article/details/43574721