关于局部前缀索引和非前缀索引

郑全

局部分区索引又分为两类：

• Local prefixed index（局部前缀索引）
• Local nonprefixed index（局部非前缀索引）
  

Ⅰ：局部前缀索引：以分区键作为索引定义的第一列

Ⅱ：局部非前缀索引：分区键没有作为索引定义的第一列

示例语句：

create table local_index_example(

   id number(2),

   name varchar2 (50),

   sex varchar2(10)

   )

  partition by range (id)

   (  partition part_1 values less than (5),  

      partition part_2 values less than (10)

   )

--

创建局部前缀索引；分区键（id）作为索引定义的第一列

create index local_prefixed_index

on local_index_example (id, name) local;

--

创建局部非前缀索引；分区键未作为索引定义的第一列

create index local_nonprefixed_index

on local_index_example (name, id) local;

注意：判断局部索引是前缀还是非前缀的只需要看分区键是否作为索引定义的第一列

3. 什么时候该使用前缀索引？什么时候该使用非前缀索引？

对于该使用前缀还是非前缀索引，这完全取决于你的实际需求，你应该尽量从实际角度出发选择合适的索引方式以充分利用到其分区消除的特性。

如果查询首先访问索引的话，它能否实现分区消除完全取决于查询中使用的谓词（即Where筛选条件）；

比如用上面的 local_index_example 表举例，现有两个查询：

①： select … from local_index_example where id = :id and name = :name;

②： select … from local_index_example where name = :name;

对于以上两个查询来说，如果查询第一步是走索引的话，则：

局部前缀索引 local_prefixed_index 只对 ① 有用；

局部非前缀索引 local_nonprefixed_index 则对 ① 和 ② 均有用；

如果你有多个类似 ① 和 ② 的查询的话，则可以考虑建立局部非前缀索引；如果平常多使用查询 ① 的话，则可以考虑建立局部前缀索引；

总之，重点是你要尽可能保证查询包含的谓词允许索引分区消除