最近公司做一些分析报表,某两个表一个月的记录量去到千万级,因是同事负责,自己没能接触,但同事问起,一时想到使用分区表一技术,故再仔细的研究了一下,以下为备忘:
全局索引(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.位图索引只能使用本地分区索引