パーティショニングに関して②
続きです。
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