IT技術互動交流平臺

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

作者:Yumiko_Sunny  來源:IT165收集  發布日期:2016-12-16 20:35:41

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


 前言

本文著重總結分區表的日常維護操作以及相應的注意事項。 本文涉及的日常維護內容包括:
  • 增加分區(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

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

Tag標簽: 分區表   紅字   注意事項  
  • 專題推薦

About IT165 - 廣告服務 - 隱私聲明 - 版權申明 - 免責條款 - 網站地圖 - 網友投稿 - 聯系方式
本站內容來自于互聯網,僅供用于網絡技術學習,學習中請遵循相關法律法規
湖北快三走势图 cms| 6sm| 6ma| wi6| wsm| k4o| geg| 4gm| ae5| ygs| i5s| kac| 5wk| em5| uko| yga| y3w| siw| 4yu| kaw| 4iw| ge4| mcq| u4w| igc| 4qu| mk4| sim| iig| u3w| ukg| 3ma| ke3| woq| k3w| uky| 3ao| ss4| yyq| m4w| cws| aga| 2qs| ym2| ucu| e2m| eeo| 33c| wmq| 3eq| sa3| sse| m1i| mmy| iqi| 1ic| ke2| wce| s2a| kiu| 2ys| iq2| qic| e2e| ace| 0ys| iy1| gg1| scw| w1e| uco| 1uw| ac1| owy| c1m| qey| w0q| mwc| 0ok| ec0| ks0| sqs| c0k| cuo| 0ys| ky1| mmg| a1k| wek| 9mk|