oracle分割槽表的維護

oracle_kai發表於2008-04-29
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章