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

続きです。

Systemパーティショニング

  • Oracle Databaseによって制御されずにアプリケーション側で制御する必要性のあるパーティショニング
  • 例えば、どのパーティションにデータを挿入するか指定しないとエラーになります。

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

参照パーティション

参照パーティションの作成

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

データの挿入

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

データの確認

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