oracle分区表的维护

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/