oracle分区索引

Oracle分区索引
1.local index


分区机制和表分区机制一样
如果本地索引的索引列以分区键开头，则称为前缀(prefix)局部索引
如果本地索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀(nonprefix)索引
create table test (id number,data varchar2(100),hash_data varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (maxvalue)
);
create index i_id on test(id) local;----prefix index
select s.index_name,s.partitioned,s.global_stats from dba_indexes s where index_name in='I_ID';
INDEX_NAME  PARTITIONED GLOBAL_STATS
----------- ----------- ------------
I_ID       YES         NO
select s.index_name,s.partition_name from dba_ind_partitions s where index_name='I_ID';
INDEX_NAME PARTITION_NAME
---------- --------------
I_ID       P1
I_ID       P2
I_ID       P3
select s.index_name,s.alignment from dba_part_indexes s where index_name='I_ID';
INDEX_NAME  ALIGNMENT
----------- ------------
I_ID        PREFIXED
create index i_nid on test(data,id) local;----prefix index
select s.index_name,s.partitioned,s.global_stats from dba_indexes s where index_name in='I_NID';
INDEX_NAME  PARTITIONED GLOBAL_STATS
----------- ----------- ------------
I_NID       YES         NO
select s.index_name,s.alignment from dba_part_indexes s where index_name='I_NID';
INDEX_NAME  ALIGNMENT
----------- ------------
I_NID       NON_PREFIXED


bitmap索引必须是local的
create bitmap index i_id_global on test(id)
ORA-25122: 在分区表上仅允许 LOCAL 位图索引
create bitmap index i_id_global on test(id) global
ORA-25113: GLOBAL 可能无法与位图索引一起使用


2.全局索引


全局分区索引的分区建/分区数和表的可能不同。分区机制也不一样。
可以分区也可以不分区。但是全局分区索引必须是前缀索引，即索引的列和索引分区列必须一样(前缀索引)。
全局索引索引条目可能指向很多个表分区，所以在截断表分区的时候，全局索引会失效。
多用于oltp
truncate表的时候使用update global indexes来更新索引。


create index i_id_global on test(id);---global非分区索引
select s.index_name,s.partitioned,s.global_stats from dba_indexes s where index_name='I_ID_GLOBAL';
INDEX_NAME   PARTITIONED GLOBAL_STATS
------------ ----------- ------------
I_ID_GLOBAL  NO          YES
select s.index_name,s.partition_name from dba_ind_partitions s where index_name='I_ID_GLOBAL';--no row


create  index i_id_global on test(id) global;---global非分区索引
select s.index_name,s.partitioned,s.global_stats from dba_indexes s where index_name='I_ID_GLOBAL';
INDEX_NAME   PARTITIONED GLOBAL_STATS
------------ ----------- ------------
I_ID_GLOBAL  NO          YES
select s.index_name,s.partition_name from dba_ind_partitions s where index_name='I_ID_GLOBAL';--no row




create index i_id_global on test(id) global
partition by range(data)
( partition p1 values less than ('vvv'),
 partition p2 values less than ('zzzz')
 )
ORA-14038: GLOBAL 分区索引必须加上前缀


create index i_id_global_d on test(data) global
partition by range(data)
( partition p1 values less than ('aa') ,
 partition p2 values less than (maxvalue)
 );------global分区索引
 
select s.index_name,s.partitioned,s.global_stats from dba_indexes s where index_name='I_ID_GLOBAL_D';
INDEX_NAME    PARTITIONED GLOBAL_STATS
------------- ----------- ------------
I_ID_GLOBAL_D  YES         NO


select s.index_name,s.partition_name from dba_ind_partitions s where index_name='I_ID_GLOBAL';-----分区数和分区键和表不一样
INDEX_NAME    PARTITION_NAME
------------- ------------------------------
I_ID_GLOBAL_D   P1
I_ID_GLOBAL_D   P2