表のオンライン再定義について

*以下は、サポート契約締結中のお客様に毎月配信しているサポートレターより一部抜粋して掲載しています。

表のオンライン再定義について

Oracle Database では、DML 処理を繰り返すことで表が断片化します。断片化によりパフォーマンスが劣化する可能性があるため、定期的に状況を確認し、必要に応じてメンテナンスを検討することが望ましいです。一般的に、断片化を解消する際には、対象表へのトランザクション処理を停止させる必要があります。トランザクション処理を停止させずに(オンラインで)解消する方法としては、セグメント縮小(SHRINK)と表のオンライン再定義があります。
表のオンライン再定義は、セグメント縮小が行えない場合でも断片化を解消できます。そこで、今回は表のオンライン再定義による断片化の解消についてご紹介します。

表のオンライン再定義による断片化の解消

1. 表の断片化について

表の断片化とは、DML 処理によりサイズが異なるエクステントの割り当てと削除を繰り返すことで連続領域が減り、再利用できない歯抜け領域が生じることです。表の断片化が進行すると、読み取り時に余分な I/O が発生するため、パフォーマンスに影響を及ぼす可能性があります。管理者は定期的に断片化の状況を確認し、必要に応じてメンテナンスを検討することが望ましいです。

表の断片化の解消方法は以下の通りです。

表のオンライン再定義: DBMS_REDEFINITION パッケージを使用して表の再定義を行います
セグメント縮小(SHRINK): ALTER TABLE … SHRINK SPACE で、セグメントを縮小させます
Export / Import: 表をエクスポートし、その後インポートすることで表の再作成を行います
表の再作成: CREATE TABLE AS SELECT で表の再作成を行います
表領域移動: ALTER TABLE … MOVE TABLESPACE でセグメントを他の表領域に移動します

※ 上記のうち、オンライン処理できる方法はセグメント縮小と表のオンライン再定義です。

2. 表のオンライン再定義について

表のオンライン再定義は Enterprise Edition で使用可能な機能です。再定義対象の元表と同じ定義の仮表を作成し、元表から仮表へデータや制約等をコピーします。その後、仮表のデータや制約等を用いて元表の再作成を行います。表の再作成が行われると断片化は解消されます。表のオンライン再定義は表の断片化解消の他に表定義の変更や記憶域の変更に利用できます。

表のオンライン再定義は下記の手順で行います。

手順１）	表のオンライン再定義が実行できるか確認します
手順２）	仮表を作成します
手順３）	再定義を開始してデータをコピーします
手順４）	索引や制約をコピーします
手順５）	再定義を終了し元表を再作成します
手順６）	２）の仮表を削除します

表のオンライン再定義は、もう一つのオンライン処理が可能なセグメント縮小と比べて以下の点において優れています。

索引(B^*Tree)の場合、階層の高さが最適化されます
行移行が解消されます
SECUREFILE LOB(列) を含む表に対して実行できます
自動セグメント領域管理方式(ASSM)を利用していない表に対して実行できます

ただし、表のオンライン再定義は以下の条件を満たしている必要があります。

Enterprise Edition の Database を使用している
索引を含めた現在の使用領域の 2 倍以上の空き領域がある
（セグメント縮小は空き領域を必要としません。）

DBMS_REDEFINITION について

表のオンライン再定義を実行するには DBMS_REDEFINITION パッケージを使用します。以下に DBMS_REDEFINITION パッケージ使用に必要な権限と表のオンライン再定義の使用手順をご説明します。

1. ユーザに必要な権限

DBMS_REDEFINITION パッケージ使用に必要な権限は下記の通りです。

オンライン再定義をユーザーのスキーマの表に実行するために必要な権限

EXECUTE_CATALOG_ROLE
CREATE TABLE
CREATE MATERIALIZED VIEW

オンライン再定義を他スキーマの表に実行するために必要な権限

EXECUTE_CATALOG_ROLE
CREATE ANY TABLE
GRANT ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

COPY_TABLE_DEPENDENTS を実行する場合に必要な権限

CREATE ANY TRIGGER
CREATE ANY INDEX

※ COPY_TABLE_DEPENDENTS の使用については後述します。

2. 表のオンライン再定義の使用手順

test_tbs 表領域の scott スキーマの test 表に対してオンライン再定義を使用する場合の例を示します。

test 表の定義は下記の通りです。

Name      Type
 --------- ----------------------------
EMPNO     NUMBER(5)    <- Primary key
 ENAME     VARCHAR2(15) <- NOT NULL
 JOB       VARCHAR2(10)
 DEPTNO    NUMBER(3)

手順１）

CAN_REDEF_TABLE をコールして、表のオンライン再定義が可能かどうかを確認します。再定義可能な場合は、プロシージャが正常に完了します。再定義できない場合は、エラーが表示されます。

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        =>'scott',
    tname        =>'testf',
    options_flag => dbms_redefinition.cons_use_pk);
END;
/

※ options_flag は、dbms_redefinition.cons_use_pk (キー別)、もしくは、dbms_redefinition.cons_use_rowid (ROWID 別)を指定します。今回の例では、EMPNO 列が主キーなので、キー別を指定します。キー別、ROWID 別については下記の通りです。

・dbms_redefinition.cons_use_pk(キー別)
キー別は表に主キー、または疑似主キーが存在する場合に使用できます。
(疑似主キーとは、すべてのコンポーネント列に NOT NULL 制約がある一意のキーです。)
・dbms_redefinition.cons_use_rowid(ROWID 別）
主キー、もしくは疑似主キーが存在しない場合に ROWID 別を指定します。
(ROWID 別を選択した場合、再定義プロセス終了後に非表示列の M_ROW$$ が追加されます。手順６）で M_ROW$$ を削除します。)

手順２）

元表と同じスキーマ内に仮表(元表と同定義の表)を作成します。(索引や制約等は後の手順 4)でコピーします。)

CREATE TABLE scott.int_test
        (empno      NUMBER(5),
         ename      VARCHAR2(15),
         job        VARCHAR2(10),
         deptno     NUMBER(3))
    TABLESPACE test_tbs;

手順３）

START_REDEF_TABLE をコールして、再定義プロセスを開始します。

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
     uname=> 'scott',
     orig_table   => 'test',
     int_table    => 'int_tset',
     options_flag => dbms_redefinition.cons_use_pk);
 END;
 /

orig_table	：元表の名前
int_table	：仮表の名前

※ 再定義プロセスの開始から終了まで、元表に DML 処理が可能です。

手順４）

COPY_TABLE_DEPENDENTS をコール(もしくは、手動コピー)して、索引や制約等を再定義される元表から仮表へコピーします。

DECLARE
 num_errors PLS_INTEGER;
 BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
     uname => 'scott',
     orig_table    => 'test',
     int_table     => 'int_test',
     copy_indexes  => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
     num_errors    => num_errors);
 END;
 /

copy_indexes	：索引をコピーするかどうかを指定（索引をコピーする場合は DBMS_REDEFINITION.CONS_ORIG_PARAMS を選択し、コピーしない場合は 0 を指定する）
num_errors	：コピー中に発生したエラーの数を出力

手順５）

FINISH_REDEF_TABLE をコールして、再定義プロセスを終了します。

BEGIN
   DBMS_REDEFINITION.FINISH_REDEF_TABLE(
     uname=> 'scott',
     orig_table   => 'test',
     int_table    => 'int_test');
 END;
 /

手順６）

仮表を削除します。( 手順 3)で ROWID 別の再定義方法を選択した場合、非表示列を削除します。）

drop table int_test;

まとめ

表のオンライン再定義は、トランザクション処理を停止せず、かつ、SECUREFILE LOB(列)を含む表や自動セグメント領域管理方式(ASSM)を利用していない表の断片化を解消することができます。断片化解消手段の 1 つとして、表のオンライン再定義の使用をご検討いただければと存じます。

参考情報

Oracle Database管理者ガイド 12cリリース1 (12.1)
https://docs.oracle.com/cd/E57425_01/121/ADMIN/tables.htm#i1006754

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 12cリリース1 (12.1)
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_redefi.htm

Document ID：1751631.1
https://docs.oracle.com/cd/E57425_01/121/SQLRF/functions198.htm

Oracle® Databaseリファレンス 12c リリース1 (12.1)
Oracle データベースの断片化の確認方法と解消方法(KROWN:145984)

(オラクル事業部サポートセンター亀井)

表のオンライン再定義について

表のオンライン再定義について

表のオンライン再定義による断片化の解消

1. 表の断片化について

2. 表のオンライン再定義について

DBMS_REDEFINITION について

1. ユーザに必要な権限

2. 表のオンライン再定義の使用手順

手順１）

手順２）

手順３）

手順４）

手順５）

手順６）

まとめ

参考情報

関連情報

表のオンライン再定義について