パーティショニングに関して
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')