在比较两种索引对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 traceonly
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" 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" 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" 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" 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" 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索引
：