search
LoginSignup
3

More than 3 years have passed since last update.

posted at

updated at

Organization

表のオンライン再定義(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
※ログインが必要です。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
3