oracle分区表的维护
建立实验表和索引
SQL> Create Table t(owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
2 last_ddl_time, timestamp, status, temporary, generated, secondary)
3 Partition By Range(object_id)
4 (Partition p_3000 Values Less Than(3000) Tablespace users,
5 Partition p_6000 Values Less than(6000) Tablespace users,
6 Partition p_max Values less than(maxvalue) tablespace users
7 )
8 As
9 Select owner, object_name, subobject_name, object_id, data_object_id, object_type, created,
10 last_ddl_time, timestamp, status, temporary, generated, secondary
11 From dba_objects
12 ;
SQL> create index idx_global_t_object_id on t(owner) global;
SQL> create index idx_local_t_object_id on t(object_id) local;
主要的分区维护操作
1. 新增分区
如果分区边界不是maxvalue,那么可以直接add一个新的分区,如果边界是maxvalue,则需要先drop掉原有分区,然后再add,或者采用分区的拆分split
SQL> alter table t drop partition p_max;
Table altered
SQL> alter table t add partition p_9000 values less than(9000) tablespace users;
对于局部索引,oracle会自动增加一个局部分区索引。
2. 移动分区
SQL> alter table t move partition p_6000 tablespace system;
Table altered
SQL> Select index_name,status From user_indexes Where table_name='T';
INDEX_NAME STATUS
------------------------------ --------
IDX_GLOBAL_T_OBJECT_ID UNUSABLE
IDX_LOCAL_T_OBJECT_ID N/A
分区移动会自动维护局部分区索引,oracle不会自动维护全局索引,所以需要我们重新rebuild 分区索引,具体需要rebuild哪些索引,可以通过dba_part_indexes,dba_ind_partitions去判断。
3. 截断分区
SQL> alter table t truncate partition p_3000 ;
Table truncated
SQL> Select index_name,status From user_indexes Where table_name='T';
INDEX_NAME STATUS
------------------------------ --------
IDX_GLOBAL_T_OBJECT_ID UNUSABLE
IDX_LOCAL_T_OBJECT_ID N/A
Truncate相对delete操作很快,数据仓库中的大量数据的批量数据加载可能会有用到;截断分区同样会自动维护局部分区索引,同时会使全局索引unusable,需要重建
4. Drop分区
SQL> alter table t drop partition p_6000;
Table altered
同样会自动维护局部分区索引,同时会使全局索引unusable,需要重建
5. 分区拆分split
通过user_tab_partitions 视图来看table有哪些分区
SQL> Select table_name,partition_name From user_tab_partitions Where table_name='T';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T P_3000
T P_9000
T P_MAX
现在想要把p_9000分区分成p_6000,用户存放object_id >=3000 and object_id<6000,p_9000 用户存放object_id>=6000 and object_id<9000的记录,利用split技术,就可以实现
SQL> alter table t split partition p_9000 at (6000) into (partition p_6000 tablespace users,partition p_9000 tablespace system);
SQL>
SQL> Select table_name,partition_name From user_tab_partitions Where table_name='T';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T P_3000
T P_6000
T P_9000
T P_MAX
SQL> Select Max(object_id) ,Min(object_id) From t Partition (p_6000);
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
5999 3000
SQL> Select Max(object_id) ,Min(object_id) From t Partition (p_9000);
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
8999 6000
对于剧本索引IDX_LOCAL_T_OBJECT_ID,通过查看user_ind_partitions ,可以看到split后会自动一个局部分区索引,索引名字等同于新增分区的名字,全局索引会失效,需要rebuild。
6. 分区合并merge
相邻的分区可以merge为一个分区,新分区的下边界为原来边界值较低的分区,上边界为原来边界值较高的分区,原先的局部索引相应也会合并,全局索引会失效,需要rebuild。
SQL> alter table t merge partition p_6000,p_9000 into partition p_9000;
alter table t merge partition p_6000,p_9000 into partition p_9000
ORA-00905: 缺少关键字
SQL> alter table t merge partitions p_6000,p_9000 into partition p_9000;
Table altered
SQL> Select table_name,partition_name From user_tab_partitions Where table_name='T';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T P_3000
T P_9000
T P_MAX
SQL> Select Max(object_id) ,Min(object_id) From t Partition (p_9000);
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
8999 3000
7. 分区交换 exchange
分区的交换可以把一个表和分区表中的一个分区中的数据进行对换,分区的交换只是一个数据字典的操作,因此操作速度很快,对于数据仓库中的load阶段,因为已经做了数据的清洗动作,还可以用without validation来避免对表中数据的验证(需要全表扫描)
SQL> select count(*) from t partition(p_6000);
COUNT(*)
----------
0
SQL> create table t_6000 as select * from dba_objects where object_id>=3000 and object_id<6000;
SQL> alter table t exchange partition p_6000 with table t_6000;
SQL> select count(*) from t partition(p_6000);
COUNT(*)
----------
2955
SQL> select count(*) from t_6000;
COUNT(*)
----------
0
如果交换的表中包含的记录不符合分区的规定,那么可以用without validation 子句跳过检查。
SQL> create table t_6000 as select * from dba_objects where object_id>=3000 and object_id<7000;
Table created
SQL> alter table t exchange partition p_6000 with table t_6000;
alter table t exchange partition p_6000 with table t_6000
ORA-14099: 未对指定分区限定表中的所有行
SQL> alter table t exchange partition p_6000 with table t_6000 without validation;
Table altered
Exchange 还有一个子句 including indexes ,指分区和表的索引相互交换,索引也可以交换,采用前面的例子,分区表有2个索引,一个在object_id列上的局部索引,一个是owner上的全局索引,实验在t_6000的object_id 上建立所以,exchange可以完成,但在owner上,还是报错奥…
alter table t exchange partition p_6000 with table t_6000 including indexes without validation
ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
SQL> create index t_idx_object_owner on t_6000(owner);
建立所以后,交换成功。
SQL> drop index t_idx_object_id;
SQL> create index t_idx_object_owner on t_6000(owner);
SQL> alter table t exchange partition p_6000 with table t_6000 including indexes without validation ;
ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10159839/viewspace-256883/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10159839/viewspace-256883/