ORACLE環境のパーティションについてまとめる

パーティションを指定したtable作成

パーティションの種類は以下から選ぶことができる。
{HASH | RANGE [INTERVAL]| LIST}

-- 普通のpartitionの場合
CREATE TABLE sales
(
    product_id NUMBER(4) NOT NULL,sales_date DATE,
    Customer_id VARCHAR2(40))
    PARTITION BY RANGE(sales_date)
    (
        PARTITION sales_p1 VALUES LESS THAN(TO_DATE(' 2011/04/01 ','YYYY/MM/DD')),
        PARTITION sales_p2 VALUES LESS THAN(TO_DATE(' 2011/07/01 ','YYYY/MM/DD')),
        PARTITION sales_p3 VALUES LESS THAN(TO_DATE(' 2011/10/01 ','YYYY/MM/DD')),
        PARTITION sales_p4 VALUES LESS THAN(TO_DATE(' 2012/01/01 ','YYYY/MM/DD'))
    );

-- コンポジットパーティション
-- 複数keyでパーティション作成する場合
CREATE TABLE stable (
    sdate DATE /* 売り上げ日 */, 
    reg VARCHAR2(20) /* 地域 */, 
    cost NUMBER /* 値段 */)
    PARTITION BY RANGE (sdate)/* メインPartation */
    SUBPARTITION BY LIST (reg) /* サブ */
    (
        PARTITION P2009Q1 VALUES LESS THAN(to_date('2009-04-01','YYYY-MM-DD'))
        (
            SUBPARTITION P2009Q1_kanto VALUES ('kanagawa','Tokyo'),
            SUBPARTITION P2009Q1_kansai VALUES ('Osaka','kyoto')
        ),
        PARTITION P2009Q2 VALUES LESS THAN(to_date('2009-07-01','YYYY-MM-DD'))
        (
            SUBPARTITION P2009Q2_kanto VALUES ('kanagawa','Tokyo'),
            SUBPARTITION P2009Q2_kansai VALUES ('Osaka','kyoto')
        )
    );

後から追加する場合

-- RANGEだと追加できる方向(Less thanとかの大きさ)が決まってるっぽい。。
ALTER TABLE stable ADD PARTITION P201005 VALUES LESS THAN (TO_DATE('2010/4/01','YYYY/MM/DD'));
-- subpartitionもこんな感じ
ALTER TABLE stable 
ADD PARTITION P201105 
VALUES LESS THAN (TO_DATE('2011/4/01','YYYY/MM/DD'))
    (
        SUBPARTITION P2011Q2_kanto VALUES ('kanagawa','Tokyo')
    );

-- 後からsubpartiotionを弄る場合(こんな感じ)
ALTER TABLE stable MODIFY PARTITION P201105 
ADD SUBPARTITION P2021Q2_kansai VALUES ('Osaka','kyoto');

-- insertの確認
insert into stable (sdate, reg, cost) values (TO_DATE('2008/04/2','YYYY/MM/DD'),'kanagawa',100) ;
insert into stable (sdate, reg, cost) values (TO_DATE('2008/06/2','YYYY/MM/DD'),'Osaka',200) ;
insert into stable (sdate, reg, cost) values (TO_DATE('2009/06/2','YYYY/MM/DD'),'Tokyo',300) ;
insert into stable (sdate, reg, cost) values (TO_DATE('2009/06/2','YYYY/MM/DD'),'kyoto',400) ;

-- 抽出
SELECT * FROM STABLE PARTITION (P2009Q1);
SELECT * FROM STABLE PARTITION (P2009Q2);
SELECT * FROM STABLE SUBPARTITION (P2009Q1_kanto);
SELECT * FROM STABLE SUBPARTITION (P2009Q2_kansai);

USERの使えるテーブル、パーティション確認方法

🚨 NUM_ROWSはDBMS_STATSパッケージを使用して表の統計情報を収集した場合にかぎり、移入されるらしい

SELECT TABLE_NAME FROM USER_TABLES WHERE  PARTITIONED = 'YES';

-- partitionは以下
SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS;
SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS;

-- subpartitionはこんな感じ
SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS FROM USER_TAB_SUBPARTITIONS;

PARTITIONを指定しての操作

PARTITION,SUBPARTITIONで指定するらしい例題はSELECTだが、他のCRUD系操作もできるはず。

-- table内のpartitionの指定はこんな感じ
SELECT * FROM stable PARTITION(P2009Q1);
SELECT * FROM stable SUBPARTITION (P2009Q2_KANTO);

-- Docker環境でうまく動かない。。(実機は知らんが)　もしかしてMySQLの構文？
SELECT * FROM stable PARTITION(P2009Q1,P2009Q2);

-- partitionの複数指定ができない場合はunionでも、、、
SELECT * FROM STABLE PARTITION(P2009Q1)
UNION ALL SELECT * FROM STABLE PARTITION(P2009Q1);

パーティションの一括削除

参考公式

ALTER TABLE <table名> TRUNCATE PARTITION <パーティション名> ;
ALTER TABLE <table名> TRUNCATE SUBPARTITION <サブパーティション名> ;


-- DROP(省略可)/REUSE　を指定することができる。
-- DROP:割り当て済みの領域を削除(他のスキーマとかが使える)
-- RESUSE:割り当て済みの領域を保持
ALTER TABLE <table名> TRUNCATE PARTITION <パーティション名> DROP STORAGE;
ALTER TABLE <table名> TRUNCATE PARTITION <パーティション名> REUSE STORAGE;

パーティションメンテナンス

-- listパーティションのメンテ
ALTER TABLE <table名> MODIFY PARTITION <Partition名> ADD VALUES ('OK', 'KS');
ALTER TABLE <table名> MODIFY PARTITION <listPartition名> DROP VALUES ('OK', 'KS');

-- パーティション名のリネーム
ALTER TABLE <table名> RENAME PARTITION sys_p636 TO tanks;

-- パーティション確認
SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE FROM USER_TAB_SUBPARTITIONS;

-- list subpartitionメンテ
-- ADD
alter table STABLE MODIFY SUBPARTITION P2009Q1_KANTO ADD VALUES ('chiba');  
-- DROP
alter table STABLE MODIFY SUBPARTITION P2009Q1_KANTO Drop VALUES ('chiba');