パーティション表の管理

索引の自動更新

デフォルトでは、パーティション表のメンテナンス操作を実行すると、多くの場合、対応する索引または索引パーティションが無効になる。
ALTER TABLE文でUPDATE INDEXES句を指定することにより、このデフォルト動作を無効にできる。

  • 実表の操作と同時に索引が更新される。後で個別に索引を再作成する必要はない。
  • 索引がUNUSABLEでマークされないため、索引の可用性が向上する。
  • 索引を再作成するために、無効になっている索引の名前を検索する必要がない。

パーティション表の管理

パーティションの追加

ALTER TABLE …ADD PARTITION

ORDER_DATEでレンジ・パーティション化された表へのパーティション追加

-- パーティション表TAB_RANGE_PARTの基本情報を確認する。
PROD(TOM)>select table_name,partition_name,high_value from user_tab_partitions where table_name='TAB_RANGE_PART';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------------------------------------------------------------------
TAB_RANGE_PART                 TAB_RANGE_P1                   TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                                                              R=GREGORIA

TAB_RANGE_PART                 TAB_RANGE_P2                   TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                                                              R=GREGORIA

TAB_RANGE_PART                 TAB_RANGE_P3                   TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                                                              R=GREGORIA

TAB_RANGE_PART                 TAB_RANGE_P4                   TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                                                              R=GREGORIA

-- 新しいパーティションを追加すると同時に索引をメンテナンスする。
PROD(TOM)>ALTER TABLE TAB_RANGE_PART ADD PARTITION TAB_RANGE_PART05 VALUES LESS THAN ( TO_DATE('20090701','YYYYMMDD'))
        TABLESPACE PART05 UPDATE INDEXES;

表が変更されました。

PROD(TOM)>select table_name,partition_name,high_value from user_tab_partitions where table_name='TAB_RANGE_PART';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------------------------------------------------------------------
TAB_RANGE_PART                 TAB_RANGE_P1                   TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                                                              R=GREGORIA

TAB_RANGE_PART                 TAB_RANGE_P2                   TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                                                              R=GREGORIA

TAB_RANGE_PART                 TAB_RANGE_P3                   TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                                                              R=GREGORIA

TAB_RANGE_PART                 TAB_RANGE_P4                   TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                                                              R=GREGORIA

TAB_RANGE_PART                 TAB_RANGE_PART05               TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                                                              R=GREGORIA
-- 索引の状態を確認する。グローバルとローカルと共に有効状態になっている。
PROD(TOM)>select index_name,status,partitioned from user_indexes where table_name='TAB_RANGE_PART';

INDEX_NAME                               STATUS                   PARTITION
---------------------------------------- ------------------------ ---------
PK_TAB_RANGE_PART                        VALID                    NO
IDX_TAB_RANGE_PART_1                     N/A                      YES

PROD(TOM)>select partition_name,status from user_ind_partitions where index_name='IDX_TAB_RANGE_PART_1' order by partition_position;

PARTITION_NAME                 STATUS
------------------------------ ------------------------
TAB_RANGE_P1                   USABLE
TAB_RANGE_P2                   USABLE
TAB_RANGE_P3                   USABLE
TAB_RANGE_P4                   USABLE
TAB_RANGE_PART05               USABLE

PROD(TOM)>

ORDER_IDキーでハッシュ・パーティション化された表へのパーティション追加
PROD(TOM)>ALTER TABLE TAB_HASH_PART ADD PARTITION TAB_HASH_P6 TABLESPACE PART06 UPDATE INDEXES;
 
表が変更されました。
 
PROD(TOM)>

BOOK_TYPEキーでリスト・パーティション化された表へのパーティション追加
デフォルトパーティションが存在する場合、ADD PARTITIONによってORA-14323が発生する。デフォルト・パーティションを分割することはできるからだ。
PROD(TOM)>ALTER TABLE TAB_LIST_PART ADD PARTITION TAB_LIST_P5 VALUES('SQL','PLSQL') TABLESPACE PART05 UPDATE INDEXES;
 
表が変更されました。
 
PROD(TOM)>

コンポジット・パーティション表へのパーティションの追加
ALTER TABLE … ADD PARTITION ( SUBPARTITION …)
コンポジット・パーティション表へのサブパーティションの追加
ALTER TABLE … MODIFY PARTITION … ADD SUBPARTITION … TABLESPACE …

パーティションの結合(ハッシュ/レンジ-ハッシュ)

ALTER TABLE … COALESCE PARTITION

  • ハッシュ・パーティションとレンジ-ハッシュコンポジットパーティションをサポートしている。
  • ハッシュ・パーティションを結合すると、その内容はハッシュ関数で決定された残りの1つ以上のパーティションに再分散される。
  • 結合する特定のパーティションはデータベースによって選択され、その内容が再分散された後に削除される。

ハッシュ・パーティション化されたTAB_HASH_PART表の結合

PROD(TOM)>select table_name,partition_name from user_tab_partitions where table_name='TAB_HASH_PART';
 
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TAB_HASH_PART                  TAB_HASH_P1
TAB_HASH_PART                  TAB_HASH_P2
TAB_HASH_PART                  TAB_HASH_P3
TAB_HASH_PART                  TAB_HASH_P4
TAB_HASH_PART                  TAB_HASH_P5
 
PROD(TOM)>ALTER TABLE TAB_HASH_PART COALESCE PARTITION;
 
表が変更されました。
 
PROD(TOM)>select table_name,partition_name from user_tab_partitions where table_name='TAB_HASH_PART';
 
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TAB_HASH_PART                  TAB_HASH_P1
TAB_HASH_PART                  TAB_HASH_P2
TAB_HASH_PART                  TAB_HASH_P3
TAB_HASH_PART                  TAB_HASH_P4
 
PROD(TOM)>

レンジ-ハッシュ・コンポジットパーティション化された表のパーティション結合
PROD(TOM)>select partition_name, subpartition_name from user_tab_subpartitions 
    where table_name='TAB_RANGE_HASH_PART' and partition_name = 'TAB_RANGE_HASH_P5';
 
PARTITION_NAME                 SUBPARTITION_NAME
------------------------------ ------------------------------------------------------------------------------------------
TAB_RANGE_HASH_P5              SYS_SUBP171
TAB_RANGE_HASH_P5              SYS_SUBP172
TAB_RANGE_HASH_P5              SYS_SUBP173
TAB_RANGE_HASH_P5              SYS_SUBP174
TAB_RANGE_HASH_P5              SYS_SUBP175
TAB_RANGE_HASH_P5              SYS_SUBP176
TAB_RANGE_HASH_P5              SYS_SUBP177
TAB_RANGE_HASH_P5              SYS_SUBP178
 
8行が選択されました。
 
PROD(TOM)>ALTER TABLE TAB_RANGE_HASH_PART MODIFY PARTITION TAB_RANGE_HASH_P5 COALESCE SUBPARTITION UPDATE INDEXES;
 
表が変更されました。
 
PROD(TOM)>select partition_name, subpartition_name from user_tab_subpartitions 
        where table_name='TAB_RANGE_HASH_PART' and partition_name = 'TAB_RANGE_HASH_P5';
 
PARTITION_NAME                 SUBPARTITION_NAME
------------------------------ ------------------------------------------------------------------------------------------
TAB_RANGE_HASH_P5              SYS_SUBP171
TAB_RANGE_HASH_P5              SYS_SUBP172
TAB_RANGE_HASH_P5              SYS_SUBP173
TAB_RANGE_HASH_P5              SYS_SUBP174
TAB_RANGE_HASH_P5              SYS_SUBP175
TAB_RANGE_HASH_P5              SYS_SUBP176
TAB_RANGE_HASH_P5              SYS_SUBP177
 
7行が選択されました。
 
PROD(TOM)>
パーティションの削除

ALTER TABLE … DROP PARTITION

  • レンジ・パーティション表、リスト・パーティション表またはレンジ-リスト・コンポジット・パーティション表からは、パーティションを削除できる。
  • ハッシュ・パーティション表、またはレンジ-ハッシュ・パーティション表のハッシュ・サブパーティションでは、かわりに結合操作を実行する必要がある。
  • パーティション内のデータを保つ場合は、DROP PARTITION文のかわりにMERGE PARTITION文を使用する。

レンジパーティション化された表のパーティションTAB_RANGE_PART05を削除する。

PROD(TOM)>ALTER TABLE TAB_RANGE_PART DROP PARTITION TAB_RANGE_PART05;
 
表が変更されました。
 
PROD(TOM)>
パーティションの交換

ALTER TABLE … EXCHANGE PARTITION … WITH TABLE …
INCLUDING INDEXES:ローカル索引の交換
WITH VALIDATION:行のマッピングが正しいかどうかの検証を実行する。
WITHOUT VALIDATION:行のマッピングが正しいかどうかの検証を実行しない。表に使用可能な主キーまたは一意制約がある場合、WITH VALIDATIONと同じ方法で実行される。

  • パーティション(またはサブパーティション)を非パーティション表に変換したり、非パーティション表をパーティション表のパーティション(サブパーティション)に変換できる。
  • ハッシュ・パーティション表からレンジ-ハッシュ・パーティション表のパーティションへの変換や、レンジ-ハッシュ・パーティション表のパーティションからハッシュ・パーティション表への変換が可能である。
  • リスト・パーティション表からレンジ-リスト・パーティション表のパーティションへの変換や、レンジ-リスト・パーティション表のパーティションからリスト・パーティション表への変換も可能である。

パーティション表と非パーティション表の交換

--# ハッシュ・パーティション表を作成し、表に10万件のテストデータをインサートする
PROD(TOM)>CREATE TABLE TAB_RANGE_PART (
    ORDER_ID        NUMBER,
    ORDER_DATE      DATE DEFAULT SYSDATE,
    BOOK_NO         VARCHAR(20) NOT NULL,
    BOOK_TYPE       VARCHAR(20) NOT NULL,
    BOOK_CNT        NUMBER    NOT NULL,
    REMARKS         VARCHAR2(40))
    LOGGING
    PCTFREE    20
    PARTITION BY RANGE (ORDER_DATE) (
        PARTITION TAB_RANGE_PART01    VALUES LESS THAN ( TO_DATE('20090201','YYYYMMDD')) TABLESPACE PART01,
        PARTITION TAB_RANGE_PART02    VALUES LESS THAN ( TO_DATE('20090301','YYYYMMDD')) TABLESPACE PART02,
        PARTITION TAB_RANGE_PART03    VALUES LESS THAN ( TO_DATE('20090401','YYYYMMDD')) TABLESPACE PART03,
        PARTITION TAB_RANGE_PART04    VALUES LESS THAN ( TO_DATE('20090501','YYYYMMDD')) TABLESPACE PART04,
        PARTITION TAB_RANGE_PART05    VALUES LESS THAN ( MAXVALUE ) TABLESPACE PART05);

表が作成されました。

PROD(TOM)>insert into tab_range_part select * from gen_partition_data;

100000行が作成されました。

PROD(TOM)>commit;

コミットが完了しました。

--# 上記のパーティション表と同じ構造を持つ非パーティション表を作成し、空のままにする。
PROD(TOM)>create table test as select * from tab_range_part where rownum = 0;

表が作成されました。

--# ハッシュ・パーティション表のTAB_RANGE_PART04と非パーティション表を交換する。
PROD(TOM)>ALTER TABLE TAB_RANGE_PART EXCHANGE PARTITION TAB_RANGE_PART04 WITH TABLE TEST;

表が変更されました。

--# ハッシュ・パーティションのTAB_RANGE_PART04のデータを非パーティション表に移動した。
PROD(TOM)>select count(*) from test;

  COUNT(*)
----------
     20010

PROD(TOM)>select count(*) from tab_range_part partition(tab_range_part04);

  COUNT(*)
----------
         0

PROD(TOM)>

以上の状態で、再度にALTER TABLE TAB_RANGE_PART EXCHANGE PARTITION TAB_RANGE_PART04 WITH TABLE TESTを実行してみる。
PROD(TOM)>ALTER TABLE TAB_RANGE_PART EXCHANGE PARTITION TAB_RANGE_PART04 WITH TABLE TEST;

表が変更されました。

PROD(TOM)>select count(*) from test;

  COUNT(*)
----------
         0

PROD(TOM)>select count(*) from tab_range_part partition(tab_range_part04);

  COUNT(*)
----------
     20010

PROD(TOM)>
パーティションのマージ

ALTER TABLE … MERGE PARTITION … INTO PARTITION

  • ハッシュ・パーティション表や、レンジ-ハッシュ・パーティション表のハッシュ・サブパーティションには使用できない。
  • レンジ・パーティションの場合、隣接していないレンジ・パーティションはマージできない。

レンジ・パーティション表のマージ

--# レンジ・パーティション表とテストデータを作成する
SQL> CREATE TABLE TAB_RANGE_PART (
    ORDER_ID        NUMBER,
    ORDER_DATE      DATE DEFAULT SYSDATE,
    BOOK_NO         VARCHAR(20) NOT NULL,
    BOOK_TYPE       VARCHAR(20) NOT NULL,
    BOOK_CNT        NUMBER    NOT NULL,
    REMARKS         VARCHAR2(40))
    LOGGING
    PCTFREE    20
    PARTITION BY RANGE (ORDER_DATE) (
        PARTITION TAB_RANGE_PART01    VALUES LESS THAN ( TO_DATE('20090201','YYYYMMDD')) TABLESPACE PART01,
        PARTITION TAB_RANGE_PART02    VALUES LESS THAN ( TO_DATE('20090301','YYYYMMDD')) TABLESPACE PART02,
        PARTITION TAB_RANGE_PART03    VALUES LESS THAN ( TO_DATE('20090401','YYYYMMDD')) TABLESPACE PART03,
        PARTITION TAB_RANGE_PART04    VALUES LESS THAN ( TO_DATE('20090501','YYYYMMDD')) TABLESPACE PART04,
        PARTITION TAB_RANGE_PART05    VALUES LESS THAN ( MAXVALUE ) TABLESPACE PART05)
    ENABLE ROW MOVEMENT;

Table created.

SQL> insert into tab_range_part select * from gen_test_data;

200000 rows created.

SQL> commit;

Commit complete.

--# TAB_RANGE_PART03とTAB_RANGE_PART04をTAB_RANGE_PART04にマージする。
SQL> ALTER TABLE TAB_RANGE_PART MERGE PARTITIONS TAB_RANGE_PART03,TAB_RANGE_PART04 INTO PARTITION TAB_RANGE_PART04;

Table altered.

SQL> SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TAB_RANGE_PART';

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_PART01               TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-M PART01
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART02               TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-M PART02
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART04               TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-M USERS
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART05               MAXVALUE                                 PART05

--# TAB_RANGE_PART04とTAB_RANGE_PART05をTAB_RANGE_PART05にマージする
SQL> ALTER TABLE TAB_RANGE_PART MERGE PARTITIONS TAB_RANGE_PART04,TAB_RANGE_PART05 INTO PARTITION TAB_RANGE_PART05 TABLESPACE PART04;

Table altered.

SQL> SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TAB_RANGE_PART';

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_PART01               TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-M PART01
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART02               TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-M PART02
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART05               MAXVALUE                                 PART04

SQL>

レンジ-リスト・コンポジットパーティション表のマージ
--# レンジ-リスト・コンポジットパーティション表とテストデータを作成する。
SQL> CREATE TABLE TAB_RANGE_LIST_PART (
    ORDER_ID        NUMBER,
    ORDER_DATE      DATE DEFAULT SYSDATE,
    BOOK_NO         VARCHAR(20) NOT NULL,
    BOOK_TYPE       VARCHAR(20) NOT NULL,
    BOOK_CNT        NUMBER    NOT NULL,
    REMARKS         VARCHAR2(40))
    PARTITION BY RANGE(ORDER_DATE) SUBPARTITION BY LIST(BOOK_TYPE)
    (
        PARTITION TAB_RANGE_LIST_P1    VALUES LESS THAN ( TO_DATE('20090201','YYYYMMDD')) TABLESPACE PART01
        (
            SUBPARTITION TAB_RANGE_LIST_P1_SP1 VALUES('CONCEPTS','ADMIN','RMAN'),
            SUBPARTITION TAB_RANGE_LIST_P1_SP2 VALUES('RAC','DG'),
            SUBPARTITION TAB_RANGE_LIST_P1_SP3 VALUES('DWH'),
            SUBPARTITION TAB_RANGE_LIST_P1_SP4 VALUES(DEFAULT)
        ),
        PARTITION TAB_RANGE_LIST_P2    VALUES LESS THAN ( TO_DATE('20090301','YYYYMMDD')) TABLESPACE PART02
        (
            SUBPARTITION TAB_RANGE_LIST_P2_SP1 VALUES('CONCEPTS','ADMIN','RMAN'),
            SUBPARTITION TAB_RANGE_LIST_P2_SP2 VALUES('RAC','DG'),
              2    3    4    5    6  SUBPARTITION TAB_RANGE_LIST_P2_SP3 VALUES(DEFAULT)
        ),
        PARTITION TAB_RANGE_LIST_P3    VALUES LESS THAN ( TO_DATE('20090401','YYYYMMDD')) TABLESPACE PART03
        (
            SUBPARTITION TAB_RANGE_LIST_P3_SP1 VALUES('CONCEPTS','ADMIN','RMAN'),
            SUBPARTITION TAB_RANGE_LIST_P3_SP2 VALUES(DEFAULT)
        ),
        PARTITION TAB_RANGE_LIST_P4    VALUES LESS THAN ( TO_DATE('20090501','YYYYMMDD')) TABLESPACE PART04
        (
            SUBPARTITION TAB_RANGE_LIST_P4_SP1 VALUES('CONCEPTS','ADMIN','RMAN'),
            SUBPARTITION TAB_RANGE_LIST_P4_SP2 VALUES('RAC','DG'),
            SUBPARTITION TAB_RANGE_LIST_P4_SP3 VALUES('DWH'),
            SUBPARTITION TAB_RANGE_LIST_P4_SP4 VALUES(DEFAULT)
        ),
        PARTITION TAB_RANGE_LIST_P5    VALUES LESS THAN ( MAXVALUE ) TABLESPACE PART05
        (
            SUBPARTITION TAB_RANGE_LIST_P5_SP1 VALUES('CONCEPTS'),
            SUBPARTITION TAB_RANGE_LIST_P5_SP2 VALUES('RAC','DG'),
            SUBPARTITION TAB_RANGE_LIST_P5_SP3 VALUES('DWH'),
            SUBPARTITION TAB_RANGE_LIST_P5_SP4 VALUES('ADMIN','RMAN'),
            SUBPARTITION TAB_RANGE_LIST_P5_SP5 VALUES(DEFAULT)
        )
    );

Table created.

SQL> insert into tab_range_list_part select * from gen_test_data;

200000 rows created.

--# TAB_RANGE_LIST_P3とTAB_RANGE_LIST_P4をTAB_RANGE_LIST_P4にマージする。
SQL> ALTER TABLE TAB_RANGE_LIST_PART MERGE PARTITIONS TAB_RANGE_LIST_P3,TAB_RANGE_LIST_P4 
    INTO PARTITION TAB_RANGE_LIST_P4 TABLESPACE PART04;

Table altered.

SQL> SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TAB_RANGE_LIST_PART';

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_LIST_P1              TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-M PART01
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P2              TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-M PART02
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P4              TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-M PART04
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P5              MAXVALUE                                 PART05

SQL> SELECT SUBPARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_SUBPARTITIONS 
    WHERE TABLE_NAME = 'TAB_RANGE_LIST_PART' AND PARTITION_NAME='TAB_RANGE_LIST_P4' ORDER BY SUBPARTITION_POSITION;

SUBPARTITION_NAME              HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_LIST_P4_SP1          'CONCEPTS', 'ADMIN', 'RMAN'              PART04
TAB_RANGE_LIST_P4_SP2          'RAC', 'DG'                              PART04
TAB_RANGE_LIST_P4_SP3          'DWH'                                    PART04
TAB_RANGE_LIST_P4_SP4          DEFAULT                                  PART04

--# サブパーティションTAB_RANGE_LIST_P4_SP2とTAB_RANGE_LIST_P4_SP3をマージする。
SQL> ALTER TABLE TAB_RANGE_LIST_PART MERGE SUBPARTITIONS TAB_RANGE_LIST_P4_SP2,TAB_RANGE_LIST_P4_SP3 
    INTO SUBPARTITION TAB_RANGE_LIST_P4_SP3 TABLESPACE PART04 UPDATE INDEXES;

Table altered.

SQL> SELECT SUBPARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_SUBPARTITIONS 
    WHERE TABLE_NAME = 'TAB_RANGE_LIST_PART' AND PARTITION_NAME='TAB_RANGE_LIST_P4' ORDER BY SUBPARTITION_POSITION;

SUBPARTITION_NAME              HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_LIST_P4_SP1          'CONCEPTS', 'ADMIN', 'RMAN'              PART04
TAB_RANGE_LIST_P4_SP3          'RAC', 'DG', 'DWH'                       PART04
TAB_RANGE_LIST_P4_SP4          DEFAULT                                  PART04

SQL>
パーティションの分割

ALTER TABLE … SPLIT PARTITION … AT … INTO (…)

  • ハッシュ・パーティションまたはサブパーティションには使用できない。

レンジ-リストコンポジットパーティション表の分割とサブパーティションの分割

-- レンジパーティションの確認
SQL> SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TAB_RANGE_LIST_PART';

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_LIST_P1              TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-M PART01
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P2              TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-M PART02
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P4              TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-M PART04
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P5              MAXVALUE                                 PART05

SQL> ALTER TABLE TAB_RANGE_LIST_PART SPLIT PARTITION TAB_RANGE_LIST_P4 AT (TO_DATE('20090401','YYYYMMDD')) 
    INTO (PARTITION TAB_RANGE_LIST_P3 TABLESPACE PART03,PARTITION TAB_RANGE_LIST_P4 TABLESPACE PART04);

Table altered.

SQL> SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_PARTITIONS 
    WHERE TABLE_NAME='TAB_RANGE_LIST_PART'  ORDER BY PARTITION_POSITION;

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_LIST_P1              TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-M PART01
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P2              TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-M PART02
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P3              TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-M PART03
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P4              TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-M PART04
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_LIST_P5              MAXVALUE                                 PART05

--# パーティション分割後のサブパーティション状況(定義は分割前のパーティションから継承された)
SQL> SELECT PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE FROM USER_TAB_SUBPARTITIONS 
    WHERE TABLE_NAME = 'TAB_RANGE_LIST_PART' AND PARTITION_NAME IN ('TAB_RANGE_LIST_P3','TAB_RANGE_LIST_P4') 
    ORDER BY 1,SUBPARTITION_POSITION;

PARTITION_NAME                 SUBPARTITION_NAME              HIGH_VALUE
------------------------------ ------------------------------ ----------------------------------------
TAB_RANGE_LIST_P3              TAB_RANGE_LIST_P3_SP1          'CONCEPTS', 'ADMIN', 'RMAN'
TAB_RANGE_LIST_P3              TAB_RANGE_LIST_P3_SP3          'RAC', 'DG', 'DWH'
TAB_RANGE_LIST_P3              TAB_RANGE_LIST_P3_SP4          DEFAULT
TAB_RANGE_LIST_P4              TAB_RANGE_LIST_P4_SP1          'CONCEPTS', 'ADMIN', 'RMAN'
TAB_RANGE_LIST_P4              TAB_RANGE_LIST_P4_SP3          'RAC', 'DG', 'DWH'
TAB_RANGE_LIST_P4              TAB_RANGE_LIST_P4_SP4          DEFAULT

6 rows selected.

--# サブパーティションRANGE_LIST_P4_SP1を分割する。
SQL> ALTER TABLE TAB_RANGE_LIST_PART SPLIT SUBPARTITION TAB_RANGE_LIST_P4_SP1 VALUES('RMAN') 
    INTO (SUBPARTITION TAB_RANGE_LIST_P4_SP1 TABLESPACE PART01,SUBPARTITION TAB_RANGE_LIST_P4_SP2 TABLESPACE PART02);

Table altered.

SQL> SELECT PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE FROM USER_TAB_SUBPARTITIONS
        WHERE TABLE_NAME = 'TAB_RANGE_LIST_PART' AND PARTITION_NAME IN ('TAB_RANGE_LIST_P3','TAB_RANGE_LIST_P4')
        ORDER BY 1,SUBPARTITION_POSITION;

PARTITION_NAME                 SUBPARTITION_NAME              HIGH_VALUE
------------------------------ ------------------------------ ----------------------------------------
TAB_RANGE_LIST_P3              TAB_RANGE_LIST_P3_SP1          'CONCEPTS', 'ADMIN', 'RMAN'
TAB_RANGE_LIST_P3              TAB_RANGE_LIST_P3_SP3          'RAC', 'DG', 'DWH'
TAB_RANGE_LIST_P3              TAB_RANGE_LIST_P3_SP4          DEFAULT
TAB_RANGE_LIST_P4              TAB_RANGE_LIST_P4_SP1          'RMAN'
TAB_RANGE_LIST_P4              TAB_RANGE_LIST_P4_SP2          'CONCEPTS', 'ADMIN'
TAB_RANGE_LIST_P4              TAB_RANGE_LIST_P4_SP3          'RAC', 'DG', 'DWH'
TAB_RANGE_LIST_P4              TAB_RANGE_LIST_P4_SP4          DEFAULT

7 rows selected.

SQL>
パーティションの切捨て

ALTER TABLE … TRUNCATE PARTITION
ALTER TABLE … TRUNCATE SUBPARTITION

PROD(TOM)>SELECT COUNT(*) FROM TAB_RANGE_PART PARTITION(TAB_RANGE_PART03);

  COUNT(*)
----------
     20677

--# DROP STORAGEによって、削除される行が占めていた領域は、表領域内の他のスキーマ・オブジェクトで使用可能になる。
PROD(TOM)>ALTER TABLE TAB_RANGE_PART TRUNCATE PARTITION TAB_RANGE_PART03 DROP STORAGE UPDATE INDEXES;

表が切り捨てられました。

PROD(TOM)>SELECT COUNT(*) FROM TAB_RANGE_PART PARTITION(TAB_RANGE_PART03);

  COUNT(*)
----------
         0

PROD(TOM)>
パーティションの移動

ALTER TABLE … MOVE PARTITION

  • データの再クラスタ化による断片化の低減
  • 別の表領域へのパーティションの移動
  • 作成時間属性の変更
  • 表圧縮を使用した圧縮形式でのデータの格納
PROD(TOM)>SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TAB_RANGE_PART' ORDER BY PARTITION_POSITION;

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_PART01               TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-M PART01
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART02               TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-M PART02
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART03               TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-M PART03
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART04               TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-M PART04
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART05               MAXVALUE                                 PART05

PROD(TOM)>ALTER TABLE TAB_RANGE_PART MOVE PARTITION TAB_RANGE_PART01 TABLESPACE PART06 NOLOGGING COMPRESS;

表が変更されました。

PROD(TOM)>SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TAB_RANGE_PART' ORDER BY PARTITION_POSITION;

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_PART01               TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-M PART06
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART02               TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-M PART02
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART03               TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-M PART03
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART04               TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-M PART04
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART05               MAXVALUE                                 PART05

PROD(TOM)>
パーティションの名前変更

ALTER TABLE … RENAME PARTITION … TO …

PROD(TOM)>ALTER TABLE TAB_RANGE_PART RENAME PARTITION TAB_RANGE_PART01 TO TAB_RANGE_PART_P1;

表が変更されました。

PROD(TOM)>SELECT PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TAB_RANGE_PART' ORDER BY PARTITION_POSITION;

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
TAB_RANGE_PART_P1              TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-M PART06
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART02               TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-M PART02
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART03               TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-M PART03
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART04               TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-M PART04
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TAB_RANGE_PART05               MAXVALUE                                 PART05

PROD(TOM)>
リスト・パーティションの変更

値の追加:ALTER TABLE … MODIFY SUBPARTITION … ADD VALUES ('値'{,'値'..})
値の削除:ALTER TABLE … MODIFY SUBPARTITION … DROP VALUES ('値'{,'値'..})

  • デフォルトのリスト・パーティションには値を追加できない。
  • デフォルト・パーティションがあると、他のパーティションに値を追加するときのパフォーマンスに影響する可能性がある。
  • デフォルト・パーティションがあると、他のパーティションに値を追加するときのパフォーマンスに影響する可能性がエラーになる。

パーティション表の情報表示

ビュー 説明
DBA_PART_TABLES DBAビューには、データベース内にあるすべてのパーティション表のパーティション化情報が表示される。
DBA_TAB_PARTITIONS DBMS_STATSパッケージまたはANALYZE文で生成されるパーティション・レベルのパーティション化情報、パーティションの記憶域パラメータおよびパーティションの統計が表示される。
DBA_TAB_SUBPARTITIONS DBMS_STATSパッケージまたはANALYZE文で生成されるサブパーティション・レベルのパーティション化情報、サブパーティションの記憶域パラメータおよびサブパーティションの統計が表示される。
DBA_PART_KEY_COLUMNS パーティション表のパーティション化キー列が表示される。
DBA_SUBPART_KEY_COLUMNS コンポジット・パーティション表のサブパーティション化キー列が表示される。
DBA_PART_COL_STATISTICS 表のサブパーティションについて、列の統計およびヒストグラム情報が表示される。
DBA_SUBPART_COL_STATISTICS 表のサブパーティションについて、列の統計およびヒストグラム情報が表示される。
DBA_PART_HISTOGRAMS 表のパーティションのヒストグラムに関するヒストグラム・データが表示される。
DBA_SUBPART_HISTOGRAMS 表のサブパーティションのヒストグラムに関するヒストグラム・データが表示される。
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License