对索引的分区有两种方法:
局部分区索引: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约束),如果想使用一个局部索引来保证这个约束,那么分区键必须包括在约束本身中。