パーティショニングに関して

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