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');