【轉】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 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中的表索引不匹配

 

類別：Oracle 檢視評論

Link URL: http://hi.baidu.com/ljm0211/blog/item/da87242d470d2615359bf743.html