程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> ORACLE分區表梳理系列(二)- 分區表日常維護及注意事項(紅字需要留意),oracle日常維護

ORACLE分區表梳理系列(二)- 分區表日常維護及注意事項(紅字需要留意),oracle日常維護

編輯:Oracle教程

ORACLE分區表梳理系列(二)- 分區表日常維護及注意事項(紅字需要留意),oracle日常維護


版權聲明:本文發布於http://www.cnblogs.com/yumiko/,版權由Yumiko_sunny所有,歡迎轉載。轉載時,請在文章明顯位置注明原文鏈接。若在未經作者同意的情況下,將本文內容用於商業用途,將保留追究其法律責任的權利。如果有問題,請以郵箱方式聯系作者([email protected])。


 前言

  • 本文著重總結分區表的日常維護操作以及相應的注意事項
  • 本文涉及的日常維護內容包括:
    • 增加分區(add)
    • 移動分區(move)
    • 截斷分區(truncate)
    • 刪除分區(drop)
    • 拆分分區(split)
    • 合並分區(merge)      --hash分區不適用
    • 交換分區(exchange)
    • 收縮分區(coalesce)   --僅適用於hash分區
  • 本文涉及一些非分區表至分區表的遷移方法的思路,以及一些日常維護操作在特殊情況下的處理方法。
  • 本文演示涉及的測試分區表,若無特殊創建或者說明,默認使用“測試表准備”部分提及的測試表。
  • 本文演示使用的數據庫版本為oracle 11.2.0.4。

 

需要注意:關於分區表日常維護操作,對於分區表索引的影響未提及,會在後面總結分區表索引時進行闡述說明。

 

 

1、測試表准備

為了便於具體的操作演示,首先准備一張RANGE型的測試分區表TEST_RANGE_PARTITION。

這裡的測試數據來源於oracle測試用戶scott下的emp表。

--創建分區表TEST_RANGE_PARTITION
--這裡通過dbms_metadata.get_ddl獲得emp表的建表結構進而修改
Yumiko_sunny@OA01> CREATE TABLE "SCOTT"."TEST_RANGE_PARTITION" 2 ( "EMPNO" NUMBER(4,0), 3 "ENAME" VARCHAR2(10), 4 "JOB" VARCHAR2(9), 5 "MGR" NUMBER(4,0), 6 "HIREDATE" DATE, 7 "SAL" NUMBER(7,2), 8 "COMM" NUMBER(7,2), 9 "DEPTNO" NUMBER(2,0) 10 ) 11 PARTITION BY RANGE ("SAL") 12 (PARTITION "TEST_RANGE_SAL_01" VALUES LESS THAN (1000) 13 PARTITION "TEST_RANGE_SAL_02" VALUES LESS THAN (2000) 14 PARTITION "TEST_RANGE_SAL_03" VALUES LESS THAN (3000) 15 PARTITION "TEST_RANGE_SAL_MAX" VALUES LESS THAN (MAXVALUE) 16 ); Table created. Yumiko_sunny@OA01> insert into TEST_RANGE_PARTITION select * from emp; 14 rows created. Yumiko_sunny@OA01> commit; Commit complete.

 

通過下面的方法,了解關於上面創建分區表的數據分布基本情況。

--查詢分表各分區的條件以及數據庫分布情況
--可以看到此時NUM_ROWS列為空,主要是因為表的的統計信息未收集導致。
Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 2 from user_part_tables a,user_tab_partitions b 3 where a.TABLE_NAME=b.TABLE_NAME; TABLE_NAME PARTITION PARTITION_NAME HIGH_VALUE NUM_ROWS ------------------------------ --------- -------------------- ----------- ---------- TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_01 1000 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_02 2000 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_03 3000 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_MAX MAXVALUE --收集分區表TEST_RANGE_PARTITION的統計信息
Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics; Table analyzed.
--可以看到,此時各分區的數據情況已經顯示出來 Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 2 from user_part_tables a,user_tab_partitions b 3 where a.TABLE_NAME=b.TABLE_NAME; TABLE_NAME PARTITION PARTITION_NAME HIGH_VALUE NUM_ROWS ------------------------------ --------- -------------------- ----------- ---------- TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_01 1000 2 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_02 2000 6 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_03 3000 3 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_MAX MAXVALUE 3

通過上面的操作,已經成功創建了一張RANGE型的分區表。

下面將依托這張表,介紹分區表的日常維護操作。

 

 

2、增加分區維護操作(add)

增加分區維護操作,顧名思義,主要針對當前分區表進行添加新分區的操作。

當分區表存在默認條件分區,如:RANGE分區表的MAXVALUE分區、LIST分區表的DEFAULT分區,此時增加分區操作會報錯。

下面嘗試通過增加分區操作,直接為測試表增加分區TEST_RANGE_SAL_04

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000);
alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000)
                                               *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

可以看到,針對存在默認條件的分區表,無法執行增加分區操作。

 

解決辦法:

1、刪除原默認條件分區,待增加分區後,再重新添加默認條件分區。
2、使用拆分分區(split)的方式,後面介紹

這裡,我們嘗試下解決辦法1的方法進行操作。

--刪除存在默認條件MAXVALUE的分區
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_MAX; Table altered.
--重新收集分區表的統計信息 Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics; Table analyzed.
--觀察分區表的信息,可以看到此時默認條件MAXVALUE的分區已經不存在 Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 2 from user_part_tables a,user_tab_partitions b 3 where a.TABLE_NAME=b.TABLE_NAME; TABLE_NAME PARTITION PARTITION_NAME HIGH_VALUE NUM_ROWS ------------------------------ --------- -------------------- ----------- ---------- TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_01 1000 2 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_02 2000 6 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_03 3000 3
--增加新分區TEST_RANGE_SAL_04 Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000); Table altered.
--重新增加默認條件MAXVALUE分區 Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_MAX values less than(maxvalue); Table altered.

 

通過上面的方法,已經完成了增加分區的操作。下面進一步驗證增加分區的操作。

--重新收集測試分區表的統計信息
Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics; Table analyzed.
--查看分區表信息,可以看到上面增加的新分區 Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 2 from user_part_tables a,user_tab_partitions b 3 where a.TABLE_NAME=b.TABLE_NAME; TABLE_NAME PARTITION PARTITION_NAME HIGH_VALUE NUM_ROWS --------------------- --------- ------------------ ----------- --------- TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_01 1000 2 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_02 2000 6 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_03 3000 3 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_MAX MAXVALUE 0 TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_04 4000 0

需要注意的是:對於默認條件的分區進行刪除,其數據不會重分布到其他分區,而是刪除數據。因此在生產環境使用需慎重

至此,增加分區維護操作的介紹結束。

 

 

3、移動分區維護操作(move)

移動分區維護操作,主要是將分區從一個表空間遷移至另一個表空間中。

--查看當前分區對應的表空間情況
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions; TABLE_NAME PARTITION_NAME TABLESPACE_NAME ------------------------------ -------------------- ------------------------------ TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS TEST_RANGE_PARTITION TEST_RANGE_SAL_01 USERS TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS --執行移動分區操作 Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION move partition TEST_RANGE_SAL_01 tablespace PARTITION_TS; Table altered.
--驗證移動後,分區所在的表空間 Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions; TABLE_NAME PARTITION_NAME TABLESPACE_NAME ------------------------------ -------------------- ------------------------------ TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS

 

 

需要注意的是:

對於組合分區,無法直接移動分區,否則會拋出ORA-14257錯誤,示例如下:

--准備一張list-list的組合分區表
Yumiko_sunny@OA01> CREATE TABLE "EMPLOYEE_LIST_LIST_PART" 2 ( "EMPNO" NUMBER(4,0), 3 "ENAME" VARCHAR2(10), 4 "JOB" VARCHAR2(9), 5 "MGR" NUMBER(4,0), 6 "HIREDATE" DATE, 7 "SAL" NUMBER(7,2), 8 "COMM" NUMBER(7,2), 9 "DEPTNO" NUMBER(2,0) 10 ) 11 PARTITION BY LIST (DEPTNO) 12 SUBPARTITION BY LIST (JOB) 13 ( 14 PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) 15 ( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'), 16 SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT) 17 ), 18 PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) 19 ( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'), 20 SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT) 21 ), 22 PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) 23 ( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'), 24 SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT) 25 ) 26 ); Table created.
--查看當前該組合分區所在表空間的信息 Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME ----------------------- ---------------------- ------------------------ --------------- EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_MAGAGER USERS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_DEFAULT USERS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_MAGAGER USERS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_DEFAULT USERS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER USERS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT USERS
--移動組合分區表的區分 Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS; alter table EMPLOYEE_LIST_LIST_PART move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS * ERROR at line 1: ORA-14257: cannot move partition other than a Range, List, System, or Hash partition

通過上面的演示,可以清楚的看到,對於組合分區,無法直接移動分區至新的表空間。

 

解決辦法:

移動分區表的子分區,然後修改當前所在分區的屬性即可。具體演示如下:

--移動子分區
Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART move subpartition EMPLOYEE_20_JOB_MAGAGER tablespace PARTITION_TS; Table altered. Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART move subpartition EMPLOYEE_20_JOB_DEFAULT tablespace PARTITION_TS; Table altered.
--修改分區的默認屬性 Yumiko_sunny@OA01> ALTER TABLE EMPLOYEE_LIST_LIST_PART MODIFY DEFAULT ATTRIBUTES FOR PARTITION EMPLOYEE_DEPTNO_20 2 tablespace PARTITION_TS; Table altered.
--驗證移動分區後的結果 Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME ----------------------- --------------------- ----------------------- --------------- EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_MAGAGER USERS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_DEFAULT USERS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_MAGAGER PARTITION_TS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_DEFAULT PARTITION_TS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER USERS EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT USERS

可以看到,通過移動子分區的方法,完成了對於組合分區的移動操作。

 

 

4、截斷分區維護操作(truncate)

截斷分區維護操作,相對於傳統的delete操作,刪除數據的效率會更高。而且會降低高水位線。

演示如下:

--查看當前測試表分區情況及分區中的記錄數
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions 2 where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS ------------------------------ ------------------------- --------------- ---------- TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 6 TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3
--執行截斷分區操作 Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION truncate partition TEST_RANGE_SAL_02; Table truncated.
--重新收集最新的測試表的統計信息 Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics; Table analyzed.
--驗證截斷操作後,分區的記錄數變化 Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions 2 where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS ------------------------------ ------------------------- --------------- ---------- TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 0 TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3

從上面的演示中可以看到,通過truncate操作,測試表的TEST_RANGE_SAL_02分區數據被清空。

至此,演示完畢。

 

 

5、刪除分區維護操作(drop)

對於分區的刪除操作,需要注意,在刪除分區後,分區所記錄的數據,不會重分布至其他分區中,而是被一並刪除。

--檢查當前分區表的分區情況,以及數據的分布情況
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions; TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS ------------------------------ ------------------------- --------------- ---------- TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 0 TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3 TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2 TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 0 TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 0
--執行分區的刪除操作 Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_04; Table altered.
--再次檢查分區表的分區情況,以及數據的分布情況 Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions; TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS ------------------------------ ------------------------- --------------- ---------- TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 0 TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3 TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2 TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 0

可以看到,分區的刪除操作不會影響數據的分布情況。

 

 

6、拆分分區維護操作(split)

在“增加分區維護操作”部分,提到了對於存在默認條件的分區表增加分區的的兩種辦法,這裡將介紹通過拆分分區的辦法來增加分區。

需要注意:在目標分區拆分後,被拆分的分區會按照拆分規則,將數據進行重分布

 

演示實例:

首先,將測試表的數據分布還原至初建時的數據分布態。

--清空測試分區表中的所有數據
Yumiko_sunny@OA01> truncate table TEST_RANGE_PARTITION; Table truncated.
--重新加載測試分區表的數據 Yumiko_sunny@OA01> insert into TEST_RANGE_PARTITION select * from emp; 14 rows created. Yumiko_sunny@OA01> commit; Commit complete.
--重新收集測試表的統計信息 Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics; Table analyzed.
--查看此時,數據在分區間的分布情況 Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions; TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS ------------------------------ ------------------------- --------------- ---------- TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 6 TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3 TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2 TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 3

 

查看此時,存在默認條件MAXVALUE的分區TEST_RANGE_SAL_MAX的具體數據信息。

Yumiko_sunny@OA01> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_MAX);

     EMPNO ENAME      JOB              MGR HIREDATE          SAL     COMM    DEPTNO
---------- ---------- --------- ---------- ------------ -------- -------- ---------
      7788 SCOTT      ANALYST         7566 19-APR-87        3000                 20
      7839 KING       PRESIDENT            17-NOV-81        5000                 10
      7902 FORD       ANALYST         7566 03-DEC-81        3000                 20

  

下面針對上面的分區TEST_RANGE_SAL_MAX進行拆分處理,其中:

將SAL>=3000且SAL<4000的數據放入新的分區TEST_RANGE_SAL_04。
將SAL>=4000的數據保留在分區TEST_RANGE_SAL_MAX中。

--針對目標分區,執行拆分分區維護操作
--依據上面的需求,將數據拆分至分區TEST_RANGE_SAL_04以及TEST_RANGE_SAL_MAX中
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION split partition TEST_RANGE_SAL_MAX at (4000) into 2 (partition TEST_RANGE_SAL_04,partition TEST_RANGE_SAL_MAX); Table altered.
--查看此時測試分區表的分區情況,以及數據分布情況 Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions; TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS ------------------------------ ------------------------- --------------- ---------- TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 6 TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3 TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2 TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 2 TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 1

 

驗證分區中實際的數據內容

Yumiko_sunny@OA01> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_04);

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20



Yumiko_sunny@OA01> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_MAX);

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81          5000                    10

可以看到,經過拆分,數據已按之前的需求,分別存儲在兩個分區中。

 

 


7、合並分區維護操作(merge)

合並分區操作,主要是將不同的分區,通過分區的合並,進行整合。

需要注意:

  • 對於list分區,合並的分區無限制要求。
  • 對於range分區,合並的分區必須相鄰,否則無法進行合並操作。
  • 對於hash分區,無法進行合並分區操作

此外,對於range分區,下限值由邊界值較低的分區決定,上限值由邊界值較高的分區決定。

 

演示示例:

通過合並分區技術,將測試表的分區TEST_RANGE_SAL_01以及分區TEST_RANGE_SAL_02進行合並,具體如下: 

--查看當前分區表的分區情況
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions; TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS ------------------------------ ------------------------- --------------- ---------- TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 6 TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3 TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2 TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 2 TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 1
--進行合並分區操作 Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION merge partitions 2 TEST_RANGE_SAL_01,TEST_RANGE_SAL_02 3 into partition TEST_RANGE_SAL_00; Table altered. --驗證合並分區後的結果 Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions; TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS ------------------------------ ------------------------- --------------- ---------- TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3 TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 2 TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 1 TEST_RANGE_PARTITION TEST_RANGE_SAL_00 USERS 8

 

 

8、交換分區維護操作(exchange) 

交換分區技術,主要是將一個非分區表的數據同“一個分區表的一個分區”進行數據交換。支持雙向交換,既可以從分區表的分區中遷移到非分區表,也可以從非分區表遷移至分區表的分區中
原則上,非分區表的結構、數據分布等,要符合分區表的目標分區的定義規則

 

演示如下:

首先,清空測試分區表的數據

Yumiko_sunny@OA01> truncate table TEST_RANGE_PARTITION;
Table truncated.


Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    0

 

 

創建一張基於emp表,sal<2000的測試非分區表emp_test。

Yumiko_sunny@OA01> create table emp_test as select * from emp where sal < 2000;
Table created.


Yumiko_sunny@OA01> select count(*) from emp_test;
  COUNT(*)
----------
         8

注意,此時非分區表的數據量為8條記錄。

 

 

執行交換分區操作,觀察分區表的記錄變化,以及非分區表的記錄變化

--執行分區交換操作
Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test; Table altered. Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions; TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS ------------------------------ ------------------------- --------------- ---------- TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 0 TEST_RANGE_PARTITION TEST_RANGE_SAL_00 USERS 8 TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 0 TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 0 Yumiko_sunny@OA01> select count(*) from emp_test; COUNT(*) ---------- 0

可以看到,通過分去交換,非分區表的數據轉移至分區表中,同時非分區表的記錄被清除。

 

 

再次執行交換分區操作,觀察分區表的記錄變化,以及非分區表的記錄變化

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
Table altered.


Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    0



Yumiko_sunny@OA01> select count(*) from emp_test;

  COUNT(*)
----------
         8

可以看到,此時分區表的數據又再次轉移回至非分區表,證明了前面所述,分區交換技術,既可以從分區表的分區中遷移到非分區表,也可以從非分區表遷移至分區表的分區中。

 

 

若非分區表的數據,不符合分區表的分區規則,此時交換會拋出ORA-14099錯誤。

--清空上面測試非分區表的數據
Yumiko_sunny@OA01> truncate table emp_test; Table truncated.
--加載emp的所有數據至該測試非分區表
--之所以使用測試非分區表,是考慮emp表以後做其他實驗時可能還需要其中的數據
--通過這樣操作,測試非分區表的數據,既存在sal<2000的數據,也存在sal>2000的數據 Yumiko_sunny@OA01> insert into emp_test select * from emp; 14 rows created. Yumiko_sunny@OA01> commit; Commit complete.

--嘗試交換分區,觀察結果 Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test; alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test * ERROR at line 1: ORA-14099: all rows in table do not qualify for specified partition

可以看到,由於TEST_RANGE_SAL_00分區的限制條件為sal<2000,而測試非分區表的數據包含了sal>2000的數據,因此交換失敗。

 

 

解決辦法:

通過without validation子句,可以避免數據校驗,而交換成功。但會存在與分區規則相悖的數據,因此該方法要慎重。

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test without validation;
Table altered.


Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                   14
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0
TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0

  

技術方案擴展思路:

若打算采用交換分區的方法,以實現非分區表到分區表的轉換,可以采用先創建一個只有默認條件的單一分區的分區表,在分區交換數據後,根據實際需要,通過前面提到的“拆分分區”的方法進行分區操作。

 

 

9、收縮分區維護操作(coalesce)

收縮分區維護操作,僅僅可以在hash分區以及組合分區的hash子分區上進行使用

通過使用收縮分區技術,可以收縮當前hash分區的分區數量。

對於hash分區的數據,在收縮過程中,oracle會自動完成數據在分區間的重分布。

 

演示如下:

首先基於emp表的數據,創建一張hash分區表

Yumiko_sunny@OA01> CREATE TABLE "EMPLOYEE_HASH_PART"
  2      ( "EMPNO" NUMBER(4,0),
  3        "ENAME" VARCHAR2(10),
  4        "JOB" VARCHAR2(9),
  5        "MGR" NUMBER(4,0),
  6        "HIREDATE" DATE,
  7        "SAL" NUMBER(7,2),
  8        "COMM" NUMBER(7,2),
  9        "DEPTNO" NUMBER(2,0)
 10      )
 11      PARTITION BY HASH (ENAME)
 12      (
 13      PARTITION EMPLOYEE_PART01,
 14      PARTITION EMPLOYEE_PART02
 15     );  

Table created.


Yumiko_sunny@OA01> insert into EMPLOYEE_HASH_PART select * from emp;
14 rows created.


Yumiko_sunny@OA01> commit;
Commit complete.


Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
EMPLOYEE_HASH_PART             EMPLOYEE_PART02           USERS                    6
EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                    8

 

執行收縮分區操作

Yumiko_sunny@OA01> alter table EMPLOYEE_HASH_PART coalesce partition;
Table altered.


Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                   14

可以看到,通過收縮分區,原本兩個分區整合到一個,而且數據也同時被整合。

 

需要注意:

當hash分區中只有一個分區時,此時無法進行收縮操作。

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                   14



Yumiko_sunny@OA01> alter table EMPLOYEE_HASH_PART coalesce partition;
alter table EMPLOYEE_HASH_PART coalesce partition
            *
ERROR at line 1:
ORA-14285: cannot COALESCE the only partition of this hash partitioned table or index

 

至此,關於分區表的日常維護操作及注意事項總結結束,後續會抽時間總結分區表索引的維護。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved