LoginSignup
3
2

More than 5 years have passed since last update.

@ora_gonsuke777in日本オラクル株式会社

表のオンライン再定義(DBMS_REDEFINITION)で、データを更新しながら RANGE PARTITION＆グローバル索引 -> HASH PARTITION＆ローカル索引への定義変更をやってみる。(Oracle Database)

Last updated at Posted at 2018-09-29

タイトルが長いですが、表のオンライン再定義は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
※ログインが必要です。

3
2
0

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
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
2