パーティショニングに関して②
続きです。
Systemパーティショニング
SYSTEMパーティショニングの作成
CREATE TABLE system_partitioned_tab ( id NUMBER, code VARCHAR2(10), description VARCHAR2(50), created_date DATE ) PARTITION BY SYSTEM ( PARTITION sort_1, PARTITION sort_2 );
データの挿入
INSERT INTO system_partitioned_tab PARTITION (sort_1) VALUES (1, 'ONE', 'ONE', SYSDATE); INSERT INTO system_partitioned_tab PARTITION (sort_2) VALUES (2, 'TWO', 'TOW', SYSDATE); COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB'); col table_name for a25 col partition_name for a20 col high_value for a10 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS ------------------------- -------------------- ---------- ---------- SYSTEM_PARTITIONED_TAB SORT_1 1 SYSTEM_PARTITIONED_TAB SORT_2 1
参照パーティション
参照パーティションの作成
- parent_tabパーティション表を作成し、id列を主キーに設定し、created_date列をパーティションキーとして作成します。
- child_tabはparent_tab_idをparent_tabパーティション表のid列を参照する外部キーとし、参照パーティションとして作成します。
CREATE TABLE parent_tab (
id NUMBER NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT parent_tab_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION part_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY'))
);
CREATE TABLE child_tab (
id NUMBER NOT NULL,
parent_tab_id NUMBER NOT NULL,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE,
CONSTRAINT child_parent_tab_pk PRIMARY KEY (id),
CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id) REFERENCES parent_tab (id)
)
PARTITION BY REFERENCE (child_parent_tab_fk);
データの挿入
- parent_tab表にデータを挿入した後に、child_tab表にデータを挿入します。
- child_tabパーティション表は、parent_tabパーティション表と同一レベルのパーティションで自動的に区切られます。
INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE); INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE); INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE, 12)); INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE); INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE); INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE); COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, 'PARENT_TAB'); EXEC DBMS_STATS.gather_table_stats(USER, 'CHILD_TAB');
データの確認
- 参照パーティション表のHigh_Valueは設定されません。
col table_name for a20 col partition_name for a20 col tablespace_name for a20 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ------------------------------ ----------
CHILD_TAB PART_2017 1
CHILD_TAB PART_2018 2
PARENT_TAB PART_2017 TO_DATE(' 2018-01-01 00:00:00' 2
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
PARENT_TAB PART_2018 TO_DATE(' 2019-01-01 00:00:00' 1
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
仮想列パーティション
- 仮想列をキーとしたパーティション表のこと。
仮想列パーティション表の作成
- NLS_LENGTH_SEMANTICSをCHARに設定する必要性があります。
alter session set nls_length_semantics='CHAR';
CREATE TABLE users (
id NUMBER,
username VARCHAR2(20),
first_letter VARCHAR2(1)
GENERATED ALWAYS AS
(
UPPER(SUBSTR(TRIM(username), 1, 1))
) VIRTUAL
)
PARTITION BY LIST (first_letter)
(
PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),
PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),
PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),
PARTITION part_v_z VALUES ('V','W','X','Y','Z')
);
データの挿入
INSERT INTO users (id, username) VALUES (1, 'Andy Pandy'); INSERT INTO users (id, username) VALUES (1, 'Burty Basset'); INSERT INTO users (id, username) VALUES (1, 'Harry, Hill'); INSERT INTO users (id, username) VALUES (1, 'Iggy Pop'); INSERT INTO users (id, username) VALUES (1, 'Oliber Hardy'); INSERT INTO users (id, username) VALUES (1, 'Peter Pervis'); INSERT INTO users (id, username) VALUES (1, 'Veruca Salt'); INSERT INTO users (id, username) VALUES (1, 'Wiley Cyote'); COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, 'USERS');
データの確認
col table_name for a25 col partition_name for a20 col high_value for a40 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS ------------------------- -------------------- ---------------------------------------- ---------- USERS PART_A_G 'A', 'B', 'C', 'D', 'E', 'F', 'G' 2 USERS PART_H_N 'H', 'I', 'J', 'K', 'L', 'M', 'N' 2 USERS PART_O_U 'O', 'P', 'Q', 'R', 'S', 'T', 'U' 2 USERS PART_V_Z 'V', 'W', 'X', 'Y', 'Z' 2
パーティショニングに関して
Oracle 12cR2がCloudオンリーだけど正式にリリースされて、シャーディングがサポートがされました。今までShared Everythingという基本的な考え方を崩さず様々な機能追加を行ってきたOracleとしては思いっ切った舵をきった感じがします。やはりCloud対応を推し進めていく中でスケールアウト・スケールバックのしやすさをDatabaseにも、ということなのでしょうか。 個人的には依存するコンポーネントが増え、実案件で運用していくにはかなり厳しい印象ですが、、、いくつかの人柱は必要でしょう。
さて、シャーディングは物理的に分散したDBサーバ上で水平分割を行う技術ですが、その元になっている単一DBサーバ上での水平分割であるパーティショニングについてメモ書き程度に残しておきます。
コンポジットパーティショニング
Oracle Database 12cR1までのリリースではサポートしているパーティショニングの方式は以下になります。
- Range
- List
- Hash
- Refresh
これらの方式を組み合わせたパーティショニングをコンポジットパーティショニングといいます。（＊組み合わせによっては実現できないものもあります。）
List-Hash コンポジットパーティショニング
- パーティショニング表の作成
CREATE TABLE list_hash_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY LIST (code)
SUBPARTITION BY HASH (id)
(
PARTITION part_aa VALUES ('AA')
(
SUBPARTITION part_aa_01,
SUBPARTITION part_aa_02
),
PARTITION part_bb VALUES ('BB')
(
SUBPARTITION part_bb_01,
SUBPARTITION part_bb_02
)
);
- 作成したList-Hashコンポジットパーティショニング表にデータを挿入 + 統計情報の収集
DECLARE
l_code VARCHAR2(10);
BEGIN
FOR i IN 1 .. 40 LOOP
IF MOD(i, 2) = 0 THEN
l_code := 'BB';
ELSE
l_code := 'AA';
END IF;
INSERT INTO list_hash_tab (id, code, description, created_date)
VALUES (i, l_code, 'Description for ' || i || ' ' || l_code, SYSDATE);
END LOOP;
COMMIT;
END;
/
-- granularityパラメータは統計情報の収集の粒度を決めるもの（パーティション表の場合のみ）
-- ALLの場合、サブパーティション、パーティション、グローバル全て収集する
EXEC DBMS_STATS.gather_table_stats(USER, 'LIST_HASH_TAB', granularity=>'ALL');
- 作成したパーティション表の情報を表示する
col table_name for a20 col partition_name for a20 col subpartition_name for a20 SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions ORDER BY table_name, partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS -------------------- -------------------- -------------------- ---------- LIST_HASH_TAB PART_AA PART_AA_01 7 LIST_HASH_TAB PART_AA PART_AA_02 13 LIST_HASH_TAB PART_BB PART_BB_01 10 LIST_HASH_TAB PART_BB PART_BB_02 10
INTERVALパーティション
CREATE TABLE interval_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION part_01 VALUES LESS THAN (TO_DATE('01-NOV-2007', 'DD-MON-YYYY'))
);
- INTERVALパーティション表の確認
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB'); col table_name for a20 col partition_name for a20 col high_value for a40 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions ORDER BY table_name, partition_name;
INSERT INTO interval_tab VALUES (1, 'ONE', 'DESC 1', TO_DATE('10-OCT-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (2, 'TWO', 'DESC 2', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
INSERT INTO interval_tab VALUES (3, 'THREE', 'DESC 3', TO_DATE('1-NOV-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'DESC 4', TO_DATE('10-NOV-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
INTERVAL_TAB SYS_P526 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
- データの挿入（パーティションの値を越える場合で、インターバルを変えた場合) 新しいパーティションが作成されます。ただし2ヶ月先が最大値となり、2007-12-01 00:00:00 2008-01-01までの期間のパーティションが飛ばされた状態となります。
INSERT INTO interval_tab VALUES (5, 'FIVE', 'DEC 5', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'DEC 4', TO_DATE('20-JAN-2008', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
INTERVAL_TAB SYS_P526 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
INTERVAL_TAB SYS_P527 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
- データの挿入。期間が空いた（ 2007-12-01 00:00:00 - 2008-01-01 00:00:00）レンジのデータを挿入した場合。新しくパーティションが作成されます。
INSERT INTO interval_tab VALUES (6, 'SIX', 'DESC 6', TO_DATE('04-DEC-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (7, 'SEVEN', 'DESC 7', TO_DATE('30-DEC-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
INTERVAL_TAB SYS_P526 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
INTERVAL_TAB SYS_P527 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
INTERVAL_TAB SYS_P528 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
