データベース
 Computer >> コンピューター >  >> プログラミング >> データベース

DBMS_REDEFINITIONパッケージを使ったOracleテーブルのオンライン再定義の手順

オンラインでのテーブル再定義（Online Table Redefinition）を利用すると、本番環境のOracle®テーブルを、データを利用不能にすることなく再構築できます。一時表（テンポラリテーブル）を使ってデータを移動させる方法に慣れている方も多いかもしれませんが、実はもっと優れたソリューションが存在します。

はじめに

テーブルを再構築する間、ステージングしたデータを移動させる従来の手法では、一定期間テーブルとデータの両方が利用できなくなり、ビジネスにとって好ましくない状況が生じます。そんなときに活躍するのがDBMS_REDEFINITIONパッケージです。以下のイメージをご覧ください。

DBMS_REDEFINITIONパッケージを使ったOracleテーブルのオンライン再定義の手順

目的

以下のような理由から、Oracleテーブルの論理構造や物理構造を定期的に変更する必要があります。

  • クエリやDML（Data Manipulation Language）のパフォーマンス向上
  • アプリケーション変更への対応
  • ストレージ管理

Oracle Databaseには、テーブルの可用性に大きな影響を与えることなくテーブル構造を変更できる仕組みが備わっています。これが「オンラインでのテーブル再定義」です。従来の再定義手法と比較して、大幅なパフォーマンス向上が期待できます。

オンラインでテーブルを再定義している間も、そのほとんどの期間においてクエリとDMLの両方が実行可能です。テーブルが排他モードでロックされるのはごく短時間だけであり、その時間はテーブルのサイズや再定義の複雑さに依存しません。再定義プロセスはユーザーに対して完全に透過的です。

なお、オンラインでのテーブル再定義には、再定義対象のテーブルが現在使用している容量とほぼ同等の空き領域が必要です。

テーブルを再編成する方法は数多くありますが、ダウンタイムが課題となる状況では、DBMS_REDEFINITIONパッケージが最適な選択肢となります。

テーブルをオンラインで再定義する手順

以下の手順に従って、テーブルをオンラインで再定義します。

  1. 再定義方式を選択する：by key（主キー方式）またはby rowids（ROWID方式）のいずれかを選びます。

    by key（主キー方式）：再定義に使用する主キーまたは疑似主キーを選択します。疑似主キーとは、すべての構成列にNOT NULL制約が設定された一意キーのことです。この方式では、再定義前後のテーブルが同じ主キー列で構成されている必要があります。これが推奨されるデフォルトの再定義方式です。

    by rowid（ROWID方式）：利用可能なキーがない場合に使用します。この方式では、再定義後のテーブルにM_ROW$$という非表示列が追加されます。再定義完了後、この列を削除するか、未使用（UNUSED）としてマークする必要があります。COMPATIBLE初期化パラメータが10.2.0以上に設定されている場合は、再定義の最終フェーズで自動的にこの列がUNUSEDに設定されます。その後、ALTER TABLE ... DROP UNUSED COLUMNS文で削除できます。なお、この方式は索引構成表（IOT）では使用できません。

  2. CAN_REDEF_TABLEプロシージャを呼び出して、テーブルがオンライン再定義可能かどうかを確認します。対象テーブルがオンライン再定義の候補でない場合、このプロシージャはエラーを発生させ、再定義できない理由を示します。

  3. 必要な論理属性・物理属性をすべて備えた空の中間テーブルを、再定義対象テーブルと同じスキーマ内に作成します。

  4. 中間テーブルに、元テーブルのすべての索引・制約・権限付与・トリガーを作成しておく必要はありません。これらはCOPY_TABLE_DEPENDENTSプロシージャを使えば自動的にコピーできます。

  5. 大規模なテーブルでパフォーマンスを改善したい場合は、次のコマンドで並列処理を設定できます。

     ALTER SESSION force parallel dml parallel degree-of-parallelism;

 ALTER SESSION force parallel query parallel degree-of-parallelism;

  6. FINISH_REDEF_TABLEコマンドでテーブルの再定義を完了させます。このプロシージャの実行中、元のテーブルは短時間だけ排他モードでロックされますが、その時間は元テーブルのデータ量に依存しません。ただし、FINISH_REDEF_TABLEは、保留中のDML操作がすべてコミットされるのを待ってから再定義を完了させます。

  7. ROWID方式で再定義を行い、かつCOMPATIBLE初期化パラメータが10.1.0以下の場合は、再定義後のテーブルに追加された非表示列M_ROW$$を削除する必要があります。次のコマンドで列をUNUSEDに設定することもできます。

     ALTER TABLE <table_name> SET UNUSED (M_ROW$$);

    COMPATIBLEが10.2.0以上の場合、再定義完了時にこの非表示列は自動的にUNUSEDに設定されます。その後、ALTER TABLE ... DROP UNUSED COLUMNS文で削除してください。中間テーブルに対する長時間実行のクエリが完了するのを待ち、その後に中間テーブルを削除します。

サンプル：テーブル再定義の実例

以下では、サンプルテーブルの再定義における各種コマンドと出力例を紹介します。

sqlplusの起動

次のようにsqlplusを起動します。

[oracle@vm215 ~]$ sqlplus amit/amit

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 29 05:44:44 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

デモ用テーブルの作成

AMITスキーマ配下に、デモ用テーブルTEST1を作成します。

SQL> CREATE TABLE TEST1 ( ID        NUMBER(10) ,
                      ENAME     VARCHAR2(10),
                      SAL       NUMBER(10) ) ;

大量行の挿入

次に、大量の行を挿入します。AMITスキーマでPGA_AGGREGATE_TARGETを最大値に設定しておくとよいでしょう。

SQL> INSERT INTO AMIT.TEST1 SELECT ROWNUM, 'T'|| ROWNUM,
DBMS_RANDOM.VALUE(100000, 999999) FROM DUAL CONNECT BY LEVEL < 1000000;

999999 ROWS CREATED.

SQL> COMMIT;

COMMIT COMPLETE.

テスト用の依存オブジェクトの作成

オンライン再定義の動作を確認できるよう、テーブルTEST1に関連する依存オブジェクトを作成します。

ビューの作成

SQL> CREATE VIEW TEST1_VW AS SELECT * FROM TEST1 ;

VIEW CREATED.

シーケンスの作成

SQL> CREATE SEQUENCE TEST_SEQ ;

SEQUENCE CREATED.

プロシージャの作成

CREATE OR REPLACE PROCEDURE PROC1 (P_ID IN NUMBER)
   AS V_ID  NUMBER ;

BEGIN
  SELECT SAL
  INTO   V_ID
  FROM   TEST1
  WHERE  ID = P_ID;
END;
/

PROCEDURE CREATED.

DMLトリガーの作成

SQL> CREATE OR REPLACE TRIGGER AMIT_TRIG
     BEFORE INSERT OR UPDATE ON TEST1
     FOR EACH ROW

DECLARE
      X       NUMBER;
BEGIN
     SELECT COUNT(*) INTO X
     FROM TEST1
     WHERE ID = :NEW.ID;
   IF X > 0 THEN
       RAISE_APPLICATION_ERROR(-20501, 'ID' || :NEW.ID || ' ALREADY EXISTS');
   END IF;
END;
/

TRIGGER CREATED.

主キーの作成

SQL> ALTER TABLE TEST1 ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID) ;

TABLE ALTERED.

再定義前の状態確認

SQL> COLUMN OBJECT_NAME FORMAT A20
    SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS ORDER BY OBJECT_NAME;SQL>

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
AMIT_TRIG            TRIGGER             VALID
PROC1                PROCEDURE           VALID
TEST1                TABLE               VALID
TEST1_ID_PK          INDEX               VALID
TEST1_VW             VIEW                VALID
TEST_SEQ             SEQUENCE            VALID

6 ROWS SELECTED.

テーブルの再定義可否チェック

次の例では、テーブルがrowidsまたはprimary keyのどちらの方式でもオンライン再定義可能かを確認しています。

主キーを使用する場合

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

ROWIDを使用する場合

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

中間テーブルのレプリカ作成

次の例では、依存オブジェクトを持たない新しい中間テーブルのレプリカを作成しています。

SQL> CREATE TABLE TEST1_REORG AS SELECT * FROM TEST1 WHERE ROWNUM=5 ;

TABLE CREATED.

SQL> SELECT COUNT(*) FROM TEST1_REORG ;

  COUNT(*)
 ----------
     0

SQL>  SELECT COUNT(*) FROM TEST1;

  COUNT(*)
 ----------
   999999

データベースへの接続

次の例では、テーブル再定義タスクを実行するために、権限を持つユーザーで接続しています。

[oracle@vm215 ~]$ sqlplus / as sysdba

Sql*plus: release 11.2.0.3.0 production on sat oct 29 05:16:48 2016

Copyright (c) 1982, 2011, oracle.  All rights reserved.

Connected to:
Oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production
With the partitioning, olap, data mining and real application testing options

再定義の開始

次の例では、主キーを使用して再定義を開始しています。

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

依存オブジェクトのコピー

次の例では、マテリアライズドビュー、主キー、ビュー、シーケンス、トリガーなどの依存オブジェクトを自動的にコピーしています。COPY_TABLE_DEPENDENTSコマンド実行時の主キー違反を回避するため、IGNORE_ERRORをTRUEに設定しています。

SQL> DECLARE
      N PLS_INTEGER;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('AMIT', 'TEST1','TEST1_REORG',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N);
END;
/

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

エラーの確認

次の例では、DBA_REDEFINITION_ERRORSビューでエラーの有無を確認しています。

SQL> COL OBJECT_NAME FOR A25
SET LIN200 PAGES 200
COL DDL_TEXT FOR A60

SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT
FROM DBA_REDEFINITION_ERRORS;

NO ROWS SELECTED

両テーブルの検証

次の例では、両テーブルの行数を検証し、中間テーブルと同期しています。

SQL> SELECT COUNT(*) FROM AMIT.TEST1_REORG ;

 COUNT(*)
----------
  999999

SQL> SELECT COUNT(*) FROM AMIT.TEST1 ;

 COUNT(*)
----------
  999999

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('AMIT', 'TEST1', 'TEST1_REORG');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

再定義の完了

次の例では、再定義を完了させています。

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('AMIT', 'TEST1', 'TEST1_REORG');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> COLUMN OBJECT_NAME FORMAT A40
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE OWNER='AMIT';

OBJECT_NAME           OBJECT_TYPE         STATUS
--------------------- ------------------- -------
TEST1_VW              VIEW                INVALID
TEST_SEQ              SEQUENCE            VALID
PROC1                 PROCEDURE           VALID
TEST1                 TABLE               VALID
TEST1_REORG           TABLE               VALID
TEST1_ID_PK           INDEX               VALID
TMP$$_TEST1_ID_PK0    INDEX               VALID
TMP$$_AMIT_TRIG0      TRIGGER             INVALID
AMIT_TRIG             TRIGGER             INVALID

9 ROWS SELECTED.

エラー確認とスキーマの再コンパイル

前のステップで無効（INVALID）になったトリガーが存在するため、完全な依存関係を考慮してスキーマを再コンパイルします。

SQL> EXEC UTL_RECOMP.RECOMP_SERIAL('AMIT') ;

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT';

OBJECT_NAME                              OBJECT_TYPE         STATUS
---------------------------------------- ------------------- -------
TEST1_VW                                 VIEW                VALID
TEST_SEQ                                 SEQUENCE            VALID
PROC1                                    PROCEDURE           VALID
TEST1                                    TABLE               VALID
TEST1_REORG                              TABLE               VALID
TEST1_ID_PK                              INDEX               VALID
TMP$$_TEST1_ID_PK0                       INDEX               VALID
TMP$$_AMIT_TRIG0                         TRIGGER             VALID
AMIT_TRIG                                TRIGGER             VALID

9 ROWS SELECTED.

中間テーブルの削除

次の例では、中間テーブルを削除しています。

SQL> DROP TABLE AMIT.TEST1_REORG;

TABLE DROPPED.

まとめ

テーブル構造を変更しながら、同時にエンドユーザーからのアクセスも維持したい場合は、DBMS_REDEFINITIONを使用しましょう。

この機能により、ダウンタイムなしでデータを再編成でき、OLTP（オンライントランザクション処理）環境においてダウンタイムがもたらす課題を回避できます。

ご意見やご質問がある場合は、フィードバックタブをご利用ください。

  1. Accessのテーブルデザイナーを使ってテーブルを作成する方法

    ```html Microsoft Office Accessは、データをより効率的に分析・管理するために、関連するテーブルの集合としてデータを保存します。そのため、Accessデータベースは「関連するテーブルの集まり」と捉えることができます。テーブルデザイナーを使ったテーブル作成は、手順さえ把握していれば決して難しくありません。 Accessのテーブルデザイナーでテーブルを作成する手順 Accessにおけるテーブルとは、行と列で構成された、主題ごとに整理されたリストのことです。テーブルの各行は「レコード」、各列は「フィールド」と呼ばれます。同じテーブル内に同名のフィールドを複数持つことはでき

  2. Oracle外部テーブル（External Table）の使い方：CSVデータをSQLで照会・活用する手順

    外部テーブル（External Table）は、データベースの外部に保存されたフラットファイルからデータを受け取り、Oracle®がそのファイル内のデータを照会できるようにする機能です。 はじめに Oracleは、SQL*Loaderがサポートするあらゆるファイル形式を解析できます。外部テーブルに対してはDML（データ操作言語）による直接的な更新操作は行えませんが、照会・結合・ソートといった操作にはDMLを利用できます。さらに、外部テーブルを対象にビューやシノニムを作成することも可能です。 外部テーブル機能を活用するには、ORACLE_LOADERアクセスドライバを使用し、データファイルがテキ