索引分区：全局索引和本地索引一些备忘

最近公司做一些分析报表，某两个表一个月的记录量去到千万级，因是同事负责，自己没能接触，但同事问起，一时想到使用分区表一技术，故再仔细的研究了一下，以下为备忘：

 

全局索引(global index)

1.必须是前缀索引，即索引列必须是以索引分区键相同,测试如下：

SQL> create table t4

  2  (bp varchar2(10),

  3   tdate date)

  4   partition by range(tdate)

  5  (partition p1 values less than (to_date('20120101','yyyymmdd')),

  6   partition p2 values less than (to_date('20130101','yyyymmdd')),

  7   partition pmax values less than (maxvalue));

 

SQL> create index t4_idx1 on t4(bp)

  2  global partition by range(bp)

  3  (partition p1 values less than ('999999999'),

  4   partition p2 values less than (maxvalue));

 

Index created.

 

SQL> create index t4_idx2 on t4(bp)

  2  global partition by range(tdate)  

  3  (partition p1 values less than (to_date('20120101','yyyymmdd')),

  4   partition p2 values less than (maxvalue));

global partition by range(tdate)

                              *

ERROR at line 2:

ORA-14038: GLOBAL partitioned index must be prefixed

 

2.若要分区的话只能按range或hash分区(hash分区是10g后才支持)

3.因全局索引的分区不依赖分区表，故表的分区更改后，须手动rebuild索引分区以作同步，该步操作消耗资源较大.

         1)查找失效的partition

          select index_name,partition_name,status from dba_ind_partitions where status='UNUSABLE';

         2)只能单个分区rebuild.

         alter table <table> rebuild parition <partition name>

本地索引(local index),本地索引分区为一个索引分区对一个表分区，索引分区由oracle自动维护

1.唯一性的本地分区索引须含表分区键列,测试如下：

SQL> create table t4

  2  (area char(4),

  3  bp varchar2(20))

  4  partition by list(area)

  5  (partition p1 values('CN'),

  6   partition p2 values('TW'),

  7   partition p3 values('HK'));

 

Table created.

 

--create unique index报错

SQL> create unique index t4_idx1 on t4(bp) local;

create unique index t4_idx1 on t4(bp) local

                               *

ERROR at line 1:

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

 

--但create non -unique index则没问题

SQL> create index t4_idx1 on t4(bp) local;

Index created.

 

--用分区键create unique index是OK的.

SQL> create unique index t4_idx2 on t4(area) local;

Index created.

 

SQL> create unique index t4_idx3 on t4(bp,area) local;

Index created.

本地索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用本地索引去给表做唯一性约束，则约束中必须要包括分区键列。

 

2.前缀和非前缀本地分区索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键,

   何为分区消除：oracle执行计划能按索引键值只到相应分区寻值，从而有效减少扫描，此为之分区消除.

3.位图索引只能使用本地分区索引