タイトルロゴ

記載日 2006/02/25
はじめに

 テーブルにデータをインサートする高速手段としてダイレクト・パス・インサート が有名です。

 ダイレクト・パス・インサートは表から表へのデータ移行時に良く使用される手段ですが、 通常のアプリケーションにおいても、処理を高速化するために利用するケースが増えてきていると思います。 ダイレクト・パス・インサートの長所的特徴と短所的特徴を挙げます。



  ■ 長所的特長

  ・ データベースバッファキャッシュを介さずに直接データファイルにINSERTが行われるので高速です。

  ・ 作成されるREDO情報が最低限の情報のみなので処理が高速です。



  ■ 短所的特長

  ・ ダイレクト・パス・インサート実行により表ロックが起こる。

  ・ 最高水位標(HWM)より後方にデータをINSERTするので、データベースの使用率が悪くなる可能性があります。



 ダイレクト・パス・インサートは、短所的特長に挙げられている通り、いくつかのリスクを冒すことでINSERTの処理を高速にしているのですね。

 今回は、上記特徴を全て検証してみたいと思います。



セクション











検証環境

 
ページのTOPへ

 ダイレクト・パス・インサートは、以下の形式で実行します。 インサート文のヒント句APPENDを指定するだけです。 但し、VALUES句を使用することができません。SELECT文を記載します。
(※つまり、ダイレクト・パス・インサートは、テーブルからテーブルへデータをコピー する様な移行処理向きに提供されている機能なのです。)


INSERT /*+ APPEND */ INTO テーブルA (SELECT ... FROM テーブルB...);

 テーブルBのデータをSELECTしてテーブルAへダイレクト・パス・インサートします。 これを検証するために、以下の二つテーブルを用意することにしました。 前者がテーブルBに相当し、 後者がテーブルAに相当します。

ダイレクト・パス・インサートテストテーブル(元データ格納)
 No.   COLUMN   TYPE   PK   IX   NL   説明 
 1  アルファベット CHAR(21) ダイレクト・パス・インサートテスト用の単純文字列。
 2  数字 NUMBER(10) ダイレクト・パス・インサートテスト用の数字型データ。
 3  日付 DATE ダイレクト・パス・インサートテスト用の日付型データ。


ダイレクト・パス・インサートテストテーブル(データ格納先)
 No.   COLUMN   TYPE   PK   IX   NL   説明 
 1  アルファベット CHAR(21) ダイレクト・パス・インサートテスト用の単純文字列。
 2  数字 NUMBER(10) ダイレクト・パス・インサートテスト用の数字型データ。
 3  日付 DATE ダイレクト・パス・インサートテスト用の日付型データ。

 二つのテーブルは、カラム構成が全く同じでテーブル名だけを変えます。二つのテーブルは、 ここでは表領域を別々にしようと思います。どちらのテーブルも一行辺りのバイト数は 38byte(CHAR(21) + NUMBER(10) + DATE → 21byte + 10byte + 7byte)程度 なので、例えば20万件のレコードを格納するならば、 10000byte程度の容量を持った表領域があれば十分でしょう。

 SYSTEMユーザで、以下の表領域を用意しました。
CREATE TABLESPACE  ← こちらをクリック(※使用する場合は、表領域名とデータファイル名のフルパスを実行環境に合わせて書換える必要があります。)

/* -------------------------------- */
/* ダイレクト・パス・インサート     */
/*                テスト用の表領域  */
/* -------------------------------- */
CREATE TABLESPACE 表領域名1
DATAFILE '表領域名1のデータファイルのフルパス'
SIZE 12000K
AUTOEXTEND OFF
DEFAULT STORAGE ( INITIAL 12000K )
PERMANENT ONLINE ;
/
CREATE TABLESPACE 表領域名2
DATAFILE '表領域名2のデータファイルのフルパス'
SIZE 12000K
AUTOEXTEND OFF
DEFAULT STORAGE ( INITIAL 12000K )
PERMANENT ONLINE ;
/

 次に、上で作成した表領域に、ダイレクト・パス・インサートテスト用のテーブルを作成します。  CREATE TABLE文は、以下のようになります。
CREATE TABLE  ← こちらをクリック(※使用する場合は、表領域名を実行環境に合わせて書換える必要があります。)

/* -------------------------------- */
/* テーブル定義                     */
/* -------------------------------- */
/* ダイレクト・パス・インサートテストテーブル(元データ格納用) */
CREATE TABLE DIRECT_INSERT_FROM_T(
      CHAR_COLUMN  CHAR(21)
    , NUMBER_COLUMN  NUMBER(10)
    , DATE_COLUMN  DATE
) TABLESPACE 表領域名1;
/
/* ダイレクト・パス・インサートテストテーブル(データ格納先用) */
CREATE TABLE DIRECT_INSERT_TO_T(
      CHAR_COLUMN  CHAR(21)
    , NUMBER_COLUMN  NUMBER(10)
    , DATE_COLUMN  DATE
) TABLESPACE 表領域名2;
/

 上で作成したDIRECT_INSERT_FROM_Tテーブルに、ダイレクト・パス・インサートの元データとして 20万件のレコードを入れることにします。
 データ投入スクリプトは、以下のようになります。
INSERT SCRIPT  ← こちらをクリック

/* -------------------------------------------------------------- */
/* ダイレクト・パス・インサートの元データとして20万件のレコードを */
/* インサートするSQLスクリプト                                    */
/* -------------------------------------------------------------- */
DECLARE
    DATA_CNT NUMBER := 200000;
    CHAR_DAT CHAR(21) := 'ABCDEFGHIJKNMLOPQRSTU';
    NUMBER_DAT NUMBER(10) := 9999999999;
    DATE_DAT DATE := TO_DATE('2006/01/01','YYYY/MM/DD');
BEGIN
    /* 20万件のレコードを作成する。 */
    FOR IDX IN 1..DATA_CNT LOOP
        INSERT INTO DIRECT_INSERT_FROM_T(CHAR_COLUMN, NUMBER_COLUMN, DATE_COLUMN)
        VALUES(CHAR_DAT,NUMBER_DAT,DATE_DAT);
    END LOOP;
    COMMIT;
END;
/

 これでテスト用のテーブルとデータは作成できました。作成した環境で、次の二つのSQLの実行速度を測定 してみました。処理時間の計測は、 SQL*PlusのSET TIMING ONを使用した、100分の1秒オーダーで測定することにします。 また、五回の計測条件ができるだけ同じになるように、毎回実行する前に、 DIRECT_INSERT_TO_TのデータをTRUNCATE TABLEしました。

 ■ 通常のインサート

INSERT INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T);

 ■ ダイレクト・パス・インサート

INSERT /*+ APPEND */ INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T);

 測定結果を以下にまとめました。

 ■ 通常のインサート
一回目 二回目 三回目 四回目 五回目 平均
00:00:05.70 00:00:05.04 00:00:06.10 00:00:06.43 00:00:05.64 00:00:05.782

 ■ ダイレクト・パス・インサート
一回目 二回目 三回目 四回目 五回目 平均
00:00:00.67 00:00:00.67 00:00:00.68 00:00:00.67 00:00:00.68 00:00:00.674

 結果を見れば一目瞭然で圧倒的にダイレクト・パス・インサートが高速です。
 
ページのTOPへ

 ダイレクト・パス・インサートによって作成されるREDOログのサイズを通常のインサートによって作成される REDOログのサイズと対象比較します。REDOログのサイズはSQL*PlusのAUTOTRACE機能 を利用します。

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T);

200000 rows created.

Statistics
------------------------------------------------------------------
       1546  recursive calls
     208669  db block gets
       2869  consistent gets
        809  physical reads
  55763092  redo size
        812  bytes sent via SQL*Net to client
        856  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     200000  rows processed

SQL> INSERT /*+ APPEND */ INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T);

200000 rows created.

Statistics
------------------------------------------------------------------
       1071  recursive calls
       1504  db block gets
       1457  consistent gets
       1100  physical reads
     52468  redo size
        796  bytes sent via SQL*Net to client
        870  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     200000  rows processed

SQL>

 なるほど、生成されるREDOログのサイズがこんなに違えば、処理速度にも大差がつきますよね。

 
ページのTOPへ

 ダイレクト・パス・インサート実行後は、トランザクション確定を忘れてはいけません。 もし、トランザクションを確定しなければ、同一セッション、他セッション のどちらのオペレーションにも障害が生じます。

 まずは、同一セッションに生じる障害を紹介します。ダイレクト・パス・インサート実行後に、トランザクション確定 コマンド(COMMITやROLLBACK)を実行しなければ、SELECT、INSERT、DELETE、MERGE文においてことごとく 「ORA-12838」が発生します。

エラー名 オブジェクトは、パラレルで変更された後は読込み/ 変更できません。
原因 同一トランザクションの中で、読込み文か変更文をパラレルまたはダイレクト・ロードで変更した後、表に追加しようとしました。これは許可されません。
処置 トランザクションを書き換えるか、または2 つのトランザクションに分割してください。1 つには最初の変更を含め、もう1 つにはパラレル変更操作を含めてください。

SQL> INSERT /*+ APPEND */ INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T);

200000 rows created.

SQL> SELECT COUNT(*) FROM DIRECT_INSERT_TO_T;
SELECT COUNT(*) FROM DIRECT_INSERT_TO_T;
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> INSERT INTO DIRECT_INSERT_TO_T(CHAR_COLUMN, NUMBER_COLUMN, DATE_COLUMN)
VALUES('abcdefghijklmnopqrstu',1111111111,SYSDATE);
INSERT INTO DIRECT_INSERT_TO_T(CHAR_COLUMN, NUMBER_COLUMN, DATE_COLUMN)
            *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> DELETE FROM DIRECT_INSERT_TO_T;
DELETE FROM DIRECT_INSERT_TO_T
            *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> MERGE INTO DIRECT_INSERT_TO_T A USING DIRECT_INSERT_FROM_T B
ON (A.CHAR_COLUMN = B.CHAR_COLUMN)
WHEN MATCHED THEN UPDATE SET A.NUMBER_COLUMN = B.NUMBER_COLUMN
WHEN NOT MATCHED THEN INSERT(A.CHAR_COLUMN, A.NUMBER_COLUMN, A.DATE_COLUMN)
VALUES(B.CHAR_COLUMN, B.NUMBER_COLUMN, B.DATE_COLUMN);
MERGE INTO DIRECT_INSERT_TO_T A USING DIRECT_INSERT_FROM_T B
                                      *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> COMMIT;

SQL> SELECT COUNT(*) FROM DIRECT_INSERT_TO_T;

  COUNT(*)
----------
    200000  ※トランザクション確定後は正常に検索できます。

1 row selected.

SQL>

 同一セッションにおいて障害が出るのですから、 異なるセッションでも障害がでます。しかし、こちらの障害は 「ORA-12838」の発生ではなく、 以下の様にDMLでの待機処理になります。

 ダイレクト・パス・インサート実行セッション

SQL> INSERT /*+ APPEND */ INTO DIRECT_INSERT_TO_T
  2 (SELECT * FROM DIRECT_INSERT_FROM_T);

200000 rows created.













SQL> COMMIT;

Commit complete.  ※トランザクション確定。

SQL>
 他セッション

SQL> 




SQL> SELECT COUNT(*) FROM DIRECT_INSERT_TO_T;

  COUNT(*)
----------
         0  ※検索は表ロックの影響を受けません。

1 row selected.

SQL> DELETE FROM DIRECT_INSERT_TO_T;


  ※表ロックにより処理が待機します。


200000 rows deleted.  ※表ロック解除により処理待機解除。

SQL>

 ダイレクト・パス・インサート実行後のトランザクション確定は決して忘れてはいけません。


 
ページのTOPへ

 ダイレクト・パス・インサートと、最高水位標(HWM:ハイウォーターマーク ) の関係を理解することは重要です。最高水位標(HWM)とは、テーブルに割当てられたブロック の中で、今までに使用された最後尾のブロックを指す用語です。

 最高水位標(HWM)は、「今までに使用されたか否か」で決定されるマークなので、 そのブロックにデータが入っているか否かは関係ありません。テーブルに10ブロック割り当たっている場合を 例に最高水位標(HWM)の動きを説明します。


 テーブルに一度もデータ挿入していなければ、最高水位標(HWM)は1ブロック目を指しています。
  


 その後、6ブロックまでを使用するデータ挿入があったとします。  最高水位標(HWM)は6ブロック目を指します。
  


 その後、データ削除が実行され、データが格納されていないブロック (※図の淡青ブロック)ができたとします。しかし、最高水位標(HWM)は6ブロック目を指たままになります。
  

 通常のインサートは、最高水位標(HWM)より低いブロックに空いているブロックがないか調べ、 空いてるブロックが見つかった場合は、そのブロックにデータを挿入します。 しかし、ダイレクト・パス・インサートは、最高水位標(HWM)より後ろにデータを挿入します。 空きブロックを探さない分だけ高速にインサートを実行できるということですが、本来データを 格納できるブロックを使用しない為、データベースの使用効率が悪 くなる可能性があります。

 最高水位標(HWM)を調べるには、DBMS_SPACE.UNUSED_SPACEプロシジャーを使用します。 以下に、DBMS_SPACE.UNUSED_SPACEプロシジャーをスクリプト化したものを紹介します。
DBMS_SPACE.UNUSED_SPACE  ← こちらをクリック

/* ----------------------------------- */
/* 最高水位標(HWM)のチェックスクリプト */
/* ----------------------------------- */
DECLARE
    /* DBMS_SPACE.UNUSED_SPACEプロシジャーのインパラメータ */
    TABLE_USER  VARCHAR2(4) := 'NICE';  /* 分析対象となるテーブルの所有者名称 */
    TABLE_NAME  VARCHAR2(18) := 'DIRECT_INSERT_TO_T';  /* 分析対象となるテーブル名称 */
    TABLE_TYPE  VARCHAR2(5) := 'TABLE';  /* ここでは"TABLE"です。
                                            DBMS_SPACE.UNUSED_SPACEプロシジャーは
                                            "INDEX","CLUSTER"についてのHWMも調べることができます。*/

    /* DBMS_SPACE.UNUSED_SPACEプロシジャーのアウトパラメータ */
    TOTAL_BLOCKS  NUMBER;  /* テーブルの全ブロック数 */
    TOTAL_BYTES  NUMBER;  /* テーブルの全バイト数 */
    UNUSED_BLOCKS  NUMBER;  /* 割当てブロック内の未使用ブロック数 */
    UNUSED_BYTES  NUMBER;  /* 割当てブロック内の未使用ブロックのバイト数 */
    LAST_USED_EXTENT_FILE_ID  NUMBER;  /* HWMを持つエクステントを含むデータファイルID */
    LAST_USED_EXTENT_BLOCK_ID  NUMBER;  /* HWMを持つエクステント内の第一ブロックID */
    LAST_USED_BLOCK  NUMBER;  /* HWMを持つエクステント内のHWMブロック番号 */
    PARTITION_NAME  VARCHAR2(30) := '';  /* パーティショニングしている場合のパーティション名 */

BEGIN
    /* DBMS_OUTPUTパッケージを使用することを明記。 */
    DBMS_OUTPUT.ENABLE;

    /* DBMS_SPACE.UNUSED_SPACEの呼出し */
    DBMS_SPACE.UNUSED_SPACE(
          TABLE_USER
        , TABLE_NAME
        , TABLE_TYPE
        , TOTAL_BLOCKS
        , TOTAL_BYTES
        , UNUSED_BLOCKS
        , UNUSED_BYTES
        , LAST_USED_EXTENT_FILE_ID
        , LAST_USED_EXTENT_BLOCK_ID
        , LAST_USED_BLOCK
--        , PARTITION_NAME  /* パーティショニングしていない場合は引数で渡す必要無し。 */
    );

    /* 結果の表示 */
    DBMS_OUTPUT.PUT_LINE('最高水位標(HWM)のチェック');
    DBMS_OUTPUT.PUT_LINE('TABLE_USER = ' || TABLE_USER);
    DBMS_OUTPUT.PUT_LINE('TABLE_NAME = ' || TABLE_NAME);
    DBMS_OUTPUT.PUT_LINE('TABLE_TYPE = ' || TABLE_TYPE);
    DBMS_OUTPUT.PUT_LINE('TOTAL_BLOCKS = ' || TOTAL_BLOCKS);
    DBMS_OUTPUT.PUT_LINE('TOTAL_BYTES = ' || TOTAL_BYTES);
    DBMS_OUTPUT.PUT_LINE('UNUSED_BLOCKS = ' || UNUSED_BLOCKS);
    DBMS_OUTPUT.PUT_LINE('UNUSED_BYTES = ' || UNUSED_BYTES);
    DBMS_OUTPUT.PUT_LINE('LAST_USED_EXTENT_BLOCK_ID = ' || LAST_USED_EXTENT_BLOCK_ID);
    DBMS_OUTPUT.PUT_LINE('LAST_USED_BLOCK = ' || LAST_USED_BLOCK);
END;
/

 このスクリプトの、TOTAL_BLOCKSUNUSED_BLOCKSに着目 します。DIRECT_INSERT_TO_Tテーブルを作成してから一件もレコードを挿入していない場合、 、このスクリプトの結果は以下のようになります。

SQL> @DBMS_SPACE_UNUSED_SPACE.sql
最高水位標(HWM)のチェック
TABLE_USER = NICE
TABLE_NAME = DIRECT_INSERT_TO_T
TABLE_TYPE = TABLE
TOTAL_BLOCKS = 8
TOTAL_BYTES = 65536
UNUSED_BLOCKS = 7
UNUSED_BYTES = 57344
LAST_USED_EXTENT_BLOCK_ID = 17
LAST_USED_BLOCK = 1

PL/SQL procedure successfully completed.

SQL>

 テーブルの全ブロック数が8であるのに対して、 未使用のブロック数が7であることがわかります。

 ここで、上で表示された結果の意味を考えるために、DIRECT_INSERT_TO_Tのテーブル定義文を再確認します。テーブルのCREATEは、 「§1) ダイレクト・パス・インサートが通常のインサートよりもどれだけ高速なのか」 のセクションで行いましたが、かなり簡略化した定義文だったので、テーブル定義時のパラメータの多くが データベースのデフォルト値で設定されているはずです。それらのデフォルト値も確認するつもりで、 DBMS_METADATA.GET_DDLファンクションを使用します。このファンクションを利用することで、 テーブルの定義文を確認できます。

SQL> SET LONG 100000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','DIRECT_INSERT_TO_T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','DIRECT_INSERT_TO_T')
--------------------------------------------------------------------------------

  CREATE TABLE "NICE"."DIRECT_INSERT_TO_T"
   (    "CHAR_COLUMN" CHAR(21),
        "NUMBER_COLUMN" NUMBER(10,0),
        "DATE_COLUMN" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE 表領域名1


1 row selected.

SQL> SHOW PARAMETERS BLOCK_SIZE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL>

 イニシャルエクステントが65536で、ネクストエクステントが1048576です。 データベースのブロックサイズが8192なのでDIRECT_INSERT_TO_Tテーブルの初期のブロック数は65536÷8192=8ブロック になります。このイニシャルエクステントを超えるデータが挿入された場合は、ネクストエクステントのサイズに従って、 テーブルの領域は拡張されます。1048576÷8192=128ブロックずつ拡張することになっています。

 DIRECT_INSERT_TO_Tのテーブル定義文を確認したところで、 DBMS_SPACE.UNUSED_SPACEプロシジャーが返した「全ブロック数が8であるのに対して、 未使用のブロック数が7」 と言う結果の意味を分析します。

 まず、第一に言えることは、DIRECT_INSERT_TO_Tテーブルは一度も領域拡張が行われていない と言うことです。 次に言えることは、最高水位標(HWM)が先頭のブロックを指していると言うことです。 8つあるブロックの中で、未使用のブロックが7つであるとは、1つ目のブロックが最高水位標(HWM)が指すブロック であることを示しています。

 通常のインサートでデータをインサートしたら、最高水位標(HWM)はどのように移動するか調べます。

SQL> INSERT INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T);

200000 rows created.

SQL> COMMIT;

Commit complete.

SQL> @DBMS_SPACE_UNUSED_SPACE.sql
最高水位標(HWM)のチェック
TABLE_USER = NICE
TABLE_NAME = DIRECT_INSERT_TO_T
TABLE_TYPE = TABLE
TOTAL_BLOCKS = 1280
TOTAL_BYTES = 10485760
UNUSED_BLOCKS = 118
UNUSED_BYTES = 966656
LAST_USED_EXTENT_BLOCK_ID = 1161
LAST_USED_BLOCK = 10

PL/SQL procedure successfully completed.

SQL>

 テーブルの領域が拡張して、全ブロック数が1280になっています。 そのうち、未使用のブロック数が118です。 最高水位標(HWM)が、データ挿入に伴って、後ろに移動していることが確認できます。

 次に、DIRECT_INSERT_TO_Tのデータを全部削除し、その後、再度通常のインサートを行います。 最高水位標(HWM)はどうなるでしょうか。

SQL> DELETE FROM DIRECT_INSERT_TO_T;

200000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> @DBMS_SPACE_UNUSED_SPACE.sql
最高水位標(HWM)のチェック
TABLE_USER = NICE
TABLE_NAME = DIRECT_INSERT_TO_T
TABLE_TYPE = TABLE
TOTAL_BLOCKS = 1280
TOTAL_BYTES = 10485760
UNUSED_BLOCKS = 118
UNUSED_BYTES = 966656
LAST_USED_EXTENT_BLOCK_ID = 1033
LAST_USED_BLOCK = 10

PL/SQL procedure successfully completed.

SQL> INSERT INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T);

200000 rows created.

SQL> COMMIT;

Commit complete.

SQL> @DBMS_SPACE_UNUSED_SPACE.sql
最高水位標(HWM)のチェック
TABLE_USER = NICE
TABLE_NAME = DIRECT_INSERT_TO_T
TABLE_TYPE = TABLE
TOTAL_BLOCKS = 1280
TOTAL_BYTES = 10485760
UNUSED_BLOCKS = 118
UNUSED_BYTES = 966656
LAST_USED_EXTENT_BLOCK_ID = 1033
LAST_USED_BLOCK = 10

PL/SQL procedure successfully completed.

SQL>

 データを削除しても、全ブロック数が1280未使用の ブロック数が118のままで、最高水位標(HWM)には何の変化も見受けられません。 これは、最高水位標(HWM)はデータの有無には関係なく、以前に使用されたブロックの最後尾を指してい るからです。

 また、データ削除後に再度データを挿入し直しても、最高水位標(HWM)には変化がありません。 データ挿入は、最高水位標(HWM)より下に存在している空領域で行われてと言うことがわかります。

 最高水位標(HWM)は、一旦上昇すると二度と下げることはできないかというと、そういうわけでもありません。 テーブルの切捨て(TRUNCATE)を行えば下げることができます。 下の例は、TRUNCATE TABLE実行後に、最高水位標(HWM)が初期状態に戻っていることを表す例です。

SQL> @DBMS_SPACE_UNUSED_SPACE.sql
最高水位標(HWM)のチェック
TABLE_USER = NICE
TABLE_NAME = DIRECT_INSERT_TO_T
TABLE_TYPE = TABLE
TOTAL_BLOCKS = 1280
TOTAL_BYTES = 10485760
UNUSED_BLOCKS = 118
UNUSED_BYTES = 966656
LAST_USED_EXTENT_BLOCK_ID = 1033
LAST_USED_BLOCK = 10

PL/SQL procedure successfully completed.

SQL> TRUNCATE TABLE DIRECT_INSERT_TO_T;

Table truncated.

SQL> @DBMS_SPACE_UNUSED_SPACE.sql
最高水位標(HWM)のチェック
TABLE_USER = NICE
TABLE_NAME = DIRECT_INSERT_TO_T
TABLE_TYPE = TABLE
TOTAL_BLOCKS = 8  ※全部ロック数が初期の8ブロックに戻ります。
TOTAL_BYTES = 65536
UNUSED_BLOCKS = 7  ※最高水位標(HWM)が先頭ブロックを指しています。
UNUSED_BYTES = 57344
LAST_USED_EXTENT_BLOCK_ID = 1289
LAST_USED_BLOCK = 1

PL/SQL procedure successfully completed.

SQL>



 最高水位標(HWM)を意識した上で、ダイレクト・パス・インサートについて考えます。

 今回、DIRECT_INSERT_TO_Tテーブルのデータを格納している表領域は、あえて 「AUTOEXTEND OFF」として作成しました。つまり、表領域の容量を超えるデータを DIRECT_INSERT_TO_Tに投入しようとすれば、表領域は自動で拡張することなく、エラーを返します。

 下は、標領域拡張エラーを発生するダイレクト・パス・インサートの例です。 既に最高水位標(HWM)が上昇している段階で、DIRECT_INSERT_TO_Tテーブルのデータを全部削除 (DELETE)しています。通常のインサート ではデータを格納することができるはずですが、ダイレクト・パス・インサートでは 「ORA-01653」が発生します。

SQL> @DBMS_SPACE_UNUSED_SPACE.sql
最高水位標(HWM)のチェック
TABLE_USER = NICE
TABLE_NAME = DIRECT_INSERT_TO_T
TABLE_TYPE = TABLE
TOTAL_BLOCKS = 1280  ※既に20万件のレコードが入っているので最高水位標(HWM)が上昇しています。
TOTAL_BYTES = 10485760
UNUSED_BLOCKS = 118  ※既に20万件のレコードが入っているので最高水位標(HWM)が上昇しています。
UNUSED_BYTES = 966656
LAST_USED_EXTENT_BLOCK_ID = 1033
LAST_USED_BLOCK = 10

PL/SQL procedure successfully completed.

SQL> DELETE FROM DIRECT_INSERT_TO_T;

200000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> INSERT /*+ APPEND */ INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T);
INSERT /*+ APPEND */ INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T)
                          *
ERROR at line 1:
ORA-01653: unable to extend table NICE.DIRECT_INSERT_TO_T by 128 in tablespace 表領域名1
SQL> INSERT INTO DIRECT_INSERT_TO_T (SELECT * FROM DIRECT_INSERT_FROM_T);

200000 rows created.  ※通常のインサートは成功します。

SQL> COMMIT;

Commit complete.

SQL>

「ORA-01653」の表す内容は以下の通りです。

エラー名 表string. string を拡張できません( string 分、表領域string) 。)
原因 表領域の表セグメントへのエクステントの割当てに失敗しました。
処置 ALTER TABLESPACE ADD DATAFILE 文を使用して、指定された表領域に1 つ以上のファイルを追加してください。


 
ページのTOPへ
 ダイレクト・パス・インサートは高速にデータをインサートするテーブルからテーブルへのデータ移行手段です。 しかし、その高速性はシステム開発において利用したくなります。 利用する際には、その為に冒しているリスクを知っておくべきです。ダイレクト・パス・インサート を行う先のテーブルは、必ず、TRUNCATE TABLEと併用可能なシステム設計でなければなりません。 また、表ロックが発生するので、他のセッションを完全に排他することが許される様な 処理(例えば夜間バッチ処理等)でのみ使用すべきだと思います。