oracle 区间索引,oracle分区索引（一）

对索引的分区有两种方法：

局部分区索引：locally partitioned index 每个表分区都有一个索引分区，而且只索引该表分区。

局部分区索引分为两类：局部前缀索引，分区键在定义的前几列上。使用局部前缀索引的查询总允许索引分区消除，而使用局部非前缀索引的查询可能不允许。

局部非前缀索引

全局分区索引：globally  partitioned index 一个索引分区可能指向任何表分区。实际上索引分区数可能不等于表分区数。

由于全局索引只按区间或者散列分区，如果希望有一个组合或者列表分区索引，就必须使用局部索引。局部索引会使用与底层表相同的机制分区。

消除分区测试代码：

如果有多个列的查询，比如 (a，b)，a那么可以建一个(基于a分区)非前缀索引(a，b)

select *from all_objects where object_name like '%PARTITIONED_TABLE%'

/

CREATE TABLE partitioned_table

( a int,

b int,

data char(20)

)

PARTITION BY RANGE (a)

(

PARTITION part_1 VALUES LESS THAN(2) tablespace mytest1,

PARTITION part_2 VALUES LESS THAN(3) tablespace mytest2

)

/

create index local_prefixed on partitioned_table (a,b) local;--局部前缀索引

create index local_nonprefixed on partitioned_table (b) local;--局部非前缀索引

insert into partitioned_table

select mod(rownum-1,2)+1, rownum, 'x'

from all_objects;

begin

dbms_stats.gather_table_stats

( user,

'PARTITIONED_TABLE',

cascade=>TRUE );

end;

/

alter tablespace mytest2 offline;

select * from partitioned_table where a = 1 and b = 1;

delete from plan_table;

explain plan for

select * from partitioned_table where a = 1 and b = 1;

select * from table(dbms_xplan.display);

select * from partitioned_table where b = 1;

delete from plan_table;

explain plan for

select * from partitioned_table where b = 1;

select * from table(dbms_xplan.display);

drop index local_prefixed;

select * from partitioned_table where a = 1 and b = 1;

delete from plan_table;

explain plan for

select * from partitioned_table where a = 1 and b = 1;

select * from table(dbms_xplan.display);

为了保证唯一性(包括unique约束和primary key约束)，如果想使用一个局部索引来保证这个约束，那么分区键必须包括在约束本身中。