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

在比较两种索引对select产生的影响之前，先要搞清楚，什么是Local prefixed index，什么叫Local nonprefixed index。其实，这两种

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

allsess

activess

timstap  date)

timstap)

to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 to_date

 

);

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

3.2 创建五种场景的索引

--Local prefixed index类型一：

--Local prefixed index类型二：

--Local nonprefixed index类型一：

--Local nonprefixed index类型二：

--全局索引：

 （该索引，由于字段与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_statstabnameestimate_percent

end;

3.4 验证所创建索引的类型

输出结果如下：

 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

 

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 |