表のオンライン再定義(DBMS_REDEFINITION)で、データを更新しながら RANGE PARTITION＆グローバル索引 -> HASH PARTITION＆ローカル索引への定義変更をやってみる。(Oracle Database)
タイトルが長いですが、表のオンライン再定義はDMLによるデータ更新を 制限せずに、表定義(論理構造や物理構造)を変更する機能です。
Oracle Database管理者ガイド 12cリリース1 (12.1)
20.7 表のオンライン再定義
https://docs.oracle.com/cd/E57425_01/121/ADMIN/tables.htm#GUID-92361F74-4796-407D-A3B9-569C6E544E34
今回は下記のパーティション構造とグローバル索引⇒ローカル索引への 変更を検証してみますやで彡(ﾟ)(ﾟ) バージョンは訳あって 12cR1(12.1.0.2) です。
RANGE PARTITION＆グローバル索引の表 ⇒ HASH PARTITION＆ローカル索引の表
1. テスト表の作成とテストデータのINSERT
テスト表(対象表／仮表)を作成して、対象表にテストデータをINSERTします。 1000件を初期セットした後、1秒に1件ずつINSERTしていくやで彡(ﾟ)(ﾟ) ＃最終的には対象表に1180件をINSERTしていきます。
-- Old Table CREATE TABLE TBL_A ( C1 NUMBER , C2 NUMBER ) PARTITION BY RANGE (C1) ( PARTITION P1 VALUES LESS THAN (100), PARTITION P2 VALUES LESS THAN (200), PARTITION PMAX VALUES LESS THAN (MAXVALUE) ); ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY (C1) USING INDEX GLOBAL; CREATE INDEX TBL_A_I1 on TBL_A (C2) GLOBAL; -- New Table CREATE TABLE NEW_TBL_A ( C1 NUMBER , C2 NUMBER ) PARTITION BY HASH (C1) ( PARTITION P1 , PARTITION P2 , PARTITION P3 , PARTITION P4 ); --※仮表の索引と主キーは後で作成 BEGIN -- Test Data INSERT INTO TBL_A SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000; COMMIT; FOR i IN 1001..1180 LOOP INSERT INTO TBL_A VALUES(i, i); COMMIT; DBMS_LOCK.SLEEP(1); END LOOP; END; / Table created. Table altered. Index created. Table created. : （PL/SQLはしばらく動作する）
（※ここから別セッション）この時点でのディクショナリ情報は下記の通りです。
10:26:24 SQL> -- Part_tables 10:26:24 SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS 10:26:24 2 FROM USER_PART_TABLES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_NAME PARTITION STATUS --------------- --------- -------- NEW_TBL_A HASH VALID ★仮表はHASHパーティション TBL_A RANGE VALID ★対象表はRANGEパーティション 10:26:24 SQL> -- Indexes 10:26:24 SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, STATUS, GLOBAL_STATS 10:26:24 2 FROM USER_INDEXES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_OWNER TABLE_NAME INDEX_NAME UNIQUENES STATUS GLO --------------- --------------- --------------- --------- -------- --- AYSHIBAT TBL_A TBL_A_PK UNIQUE VALID YES ★対象表のグローバル索引(主キー) AYSHIBAT TBL_A TBL_A_I1 NONUNIQUE VALID YES ★対象表のグローバル索引 10:26:24 SQL> -- Constraints 10:26:24 SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE 10:26:24 2 FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE '%TBL_A%'; OWNER TABLE_NAME CONSTRAINT_NAME C --------------- --------------- --------------- - AYSHIBAT TBL_A TBL_A_PK P 10:26:26 SQL> 10:26:26 SQL> -- Data Check 10:26:26 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1021 10:26:27 SQL> SELECT COUNT(*) FROM NEW_TBL_A; COUNT(*) ---------- 0
2. DBMS_REDEFINITION.CAN_REDEF_TABLEによる事前チェック
DBMS_REDEFINITION.CAN_REDEF_TABLEプロシージャで表がオンライン再定義可能かチェックします。
-- Check redefinition possibility. EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TBL_A', DBMS_REDEFINITION.CONS_USE_PK); PL/SQL procedure successfully completed.
オンライン表再定義が不可能な場合は、下記のようなエラーが出力されます彡(ﾟ)(ﾟ)
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TBL_B', DBMS_REDEFINITION.CONS_USE_PK); BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TBL_B', DBMS_REDEFINITION.CONS_USE_PK); END; * ERROR at line 1: ORA-12088: cannot online redefine table "AYSHIBAT"."TBL_B" with unsupported datatype ORA-06512: at "SYS.DBMS_REDEFINITION", line 173 ORA-06512: at "SYS.DBMS_REDEFINITION", line 3664 ORA-06512: at line 1
3. DBMS_REDEFINITION.START_REDEF_TABLEプロシージャでオンライン再定義を開始
DBMS_REDEFINITION.START_REDEF_TABLEプロシージャでオンライン再定義を開始します。 対象表⇒仮表への初期データ移行が、この時点でのデータ断面で実行されます。
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TBL_A', 'NEW_TBL_A'); SELECT COUNT(*) FROM TBL_A; SELECT COUNT(*) FROM NEW_TBL_A; 10:26:27 SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TBL_A', 'NEW_TBL_A'); PL/SQL procedure successfully completed. 10:26:29 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1023 10:26:29 SQL> SELECT COUNT(*) FROM NEW_TBL_A; COUNT(*) ---------- 1022 ★仮表にデータが挿入、件数差異は裏で対象表のデータINSERTを実行しているため。
4. 仮表にローカル索引(主キー、非ユニーク索引)を作成
仮表に主キーと非ユニークのローカル索引を作成します。
CREATE TABLE時に作らないのは、上記 3. の初期データ移行の 負荷(処理量)を減らすため。索引無い方がINSERTは速いんで彡(ﾟ)(ﾟ)
ALTER TABLE NEW_TBL_A ADD CONSTRAINT NEW_TBL_A_PK PRIMARY KEY (C1) USING INDEX LOCAL; CREATE INDEX NEW_TBL_A_I1 on NEW_TBL_A (C2) LOCAL; Table altered. Index created.
この時点でのディクショナリ情報は下記の通りです。
10:26:29 SQL> -- Part_tables 10:26:29 SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS 10:26:29 2 FROM USER_PART_TABLES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_NAME PARTITION STATUS --------------- --------- -------- NEW_TBL_A HASH VALID TBL_A RANGE VALID 10:26:29 SQL> -- Indexes 10:26:29 SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, STATUS, GLOBAL_STATS 10:26:29 2 FROM USER_INDEXES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_OWNER TABLE_NAME INDEX_NAME UNIQUENES STATUS GLO --------------- --------------- --------------- --------- -------- --- AYSHIBAT NEW_TBL_A NEW_TBL_A_I1 NONUNIQUE N/A NO ★仮表のローカル索引 AYSHIBAT NEW_TBL_A NEW_TBL_A_PK UNIQUE N/A NO ★仮表のローカル索引 AYSHIBAT TBL_A TBL_A_I1 NONUNIQUE VALID YES AYSHIBAT TBL_A TBL_A_PK UNIQUE VALID YES AYSHIBAT MLOG$_TBL_A I_MLOG$_TBL_A NONUNIQUE VALID YES 10:26:29 SQL> -- Constraints 10:26:29 SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE 10:26:29 2 FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE '%TBL_A%'; OWNER TABLE_NAME CONSTRAINT_NAME C --------------- --------------- --------------- - AYSHIBAT NEW_TBL_A NEW_TBL_A_PK P ★仮表の主キー制約 AYSHIBAT TBL_A TBL_A_PK P
5. DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECTプロシージャによる索引＆主キー制約の関連付け
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECTプロシージャで 対象表と仮表の索引と主キーを関連付けます。
BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( USER -- uname , 'TBL_A' -- orig_table , 'NEW_TBL_A' -- int_table , DBMS_REDEFINITION.CONS_INDEX -- dep_type , USER -- dep_owner , 'TBL_A_PK' -- dep_orig_name , 'NEW_TBL_A_PK' -- dep_int_name ); END; / BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( USER -- uname , 'TBL_A' -- orig_table , 'NEW_TBL_A' -- int_table , DBMS_REDEFINITION.CONS_CONSTRAINT -- dep_type , USER -- dep_owner , 'TBL_A_PK' -- dep_orig_name , 'NEW_TBL_A_PK' -- dep_int_name ); END; / BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( USER -- uname , 'TBL_A' -- orig_table , 'NEW_TBL_A' -- int_table , DBMS_REDEFINITION.CONS_INDEX -- dep_type , USER -- dep_owner , 'TBL_A_I1' -- dep_orig_name , 'NEW_TBL_A_I1' -- dep_int_name ); END; / PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTSプロシージャで 対象表の関連オブジェクトも仮表にコピーします。
ただし今回のように、REGISTER_DEPENDENT_OBJECT で明示的に 関連付けを行うケースだと、この手順は不要かも……彡(-)(-)
SET SERVEROUTPU ON SIZE 1000000; DECLARE error_count pls_integer := 0; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( USER -- uname , 'TBL_A' -- orig_table , 'NEW_TBL_A' -- int_table , 0 -- copy_indexes 0...NO_COPY , FALSE -- copy_triggers , FALSE -- copy_constraints , FALSE -- copy_privileges , FALSE -- ignore_errors , error_count -- num_errors , FALSE -- copy_statistics , FALSE -- copy_mlogs ); DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count)); END; / errors := 0 PL/SQL procedure successfully completed.
6. DBMS_REDEFINITION.FINISH_REDEF_TABLEプロシージャでオンライン再定義を完了する。
DBMS_REDEFINITION.FINISH_REDEF_TABLEプロシージャでオンライン再定義を完了します。
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TBL_A', 'NEW_TBL_A'); PL/SQL procedure successfully completed.
完了後のディクショナリとデータ件数は以下の通りです。
10:26:34 SQL> -- Part_tables 10:26:34 SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS 10:26:34 2 FROM USER_PART_TABLES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_NAME PARTITION STATUS --------------- --------- -------- NEW_TBL_A RANGE VALID TBL_A HASH VALID ★HASHパーティションに変わっている。 10:26:34 SQL> -- Indexes 10:26:34 SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, STATUS, GLOBAL_STATS 10:26:34 2 FROM USER_INDEXES WHERE TABLE_NAME LIKE '%TBL_A%'; TABLE_OWNER TABLE_NAME INDEX_NAME UNIQUENES STATUS GLO --------------- --------------- --------------- --------- -------- --- AYSHIBAT NEW_TBL_A NEW_TBL_A_PK UNIQUE VALID YES AYSHIBAT NEW_TBL_A NEW_TBL_A_I1 NONUNIQUE VALID YES AYSHIBAT TBL_A TBL_A_PK UNIQUE N/A NO ★ローカル索引に変わっている。 AYSHIBAT TBL_A TBL_A_I1 NONUNIQUE N/A NO ★ローカル索引に変わっている。 10:26:35 SQL> -- Constraints 10:26:35 SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE 10:26:35 2 FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE '%TBL_A%'; OWNER TABLE_NAME CONSTRAINT_NAME C --------------- --------------- --------------- - AYSHIBAT TBL_A TBL_A_PK P AYSHIBAT NEW_TBL_A NEW_TBL_A_PK P 10:26:38 SQL> 10:26:38 SQL> -- Redifiniitoned Table Data Check 10:26:38 SQL> SELECT COUNT(*) FROM NEW_TBL_A; COUNT(*) ---------- 1028 10:26:38 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1032 ★件数が増えていってるのは裏でINSERTしているため 10:26:38 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1038 ★件数が増えていってるのは裏でINSERTしているため ： ： 10:32:43 SQL> SELECT COUNT(*) FROM TBL_A; COUNT(*) ---------- 1180 ★最終的なデータ件数 10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P1); COUNT(*) ---------- 285 ★HASHパーティションP1の件数 10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P2); COUNT(*) ---------- 282 ★HASHパーティションP2の件数 10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P3); COUNT(*) ---------- 305 ★HASHパーティションP3の件数 10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P4); COUNT(*) ---------- 308 ★HASHパーティションP4の件数 10:32:36 SQL>
7. まとめ
HASHパーティション化とローカル索引への表定義変更を、 データの更新を止めずに実行できたで！彡(^)(^)
12cR1からのDBMS_REDEFINITIONには、上記で説明した一連のプロシージャを 一まとめにしたREDEF_TABLEプロシージャが提供されています。こちらは別の機会に彡(ﾟ)(ﾟ)
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
12c リリース1 (12.1)
122 DBMS_REDEFINITION
REDEF_TABLEプロシージャ
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_redefi.htm#CHDDDDFG
8. 参考情報
マニュアルやサポートドキュメントも読んでおくんやで彡(ﾟ)(ﾟ)
Oracle Database管理者ガイド 12cリリース1 (12.1)
20.7 表のオンライン再定義
https://docs.oracle.com/cd/E57425_01/121/ADMIN/tables.htm#GUID-92361F74-4796-407D-A3B9-569C6E544E34
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
12c リリース1 (12.1)
122 DBMS_REDEFINITION
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_redefi.htm#CBBJJAIF
MOSドキュメント：How To Partition Existing Table Using DBMS_REDEFINITION (ドキュメントID 472449.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=472449.1
※ログインが必要です。
