DELETEと領域の解放(ORACLE)


ITコーディネータのシュウです。

防災疑似体験

先日、新橋のほうに仕事で行った際に、近くのビルでVR(バーチャルリアリティ)映像を使った災害疑似体験ができる防災体験車に乗って体験する機会がありました。
昼休みくらいに、近くに来ているということでせっかくだからと行ってみたところ、東京消防庁が保有する防災体験車が止まっていて、8人くらいずつが乗って、ヘッドマウントディスプレイを被って、マンションにいるときに、震度7程度の地震が起きた場合の疑似体験ができました。
時間は3分ほどでしたが、上から物が落ちるは家具が倒れるは、ガラスは割れ、ドアは開かなくなるし、外の景色も大変なことに! いやあ~想像していたよりも迫力もあり、酔いそうな気分に! とても立っていられません。震度7だとこんなに凄いんですね。

地震大国である日本、いつそういう大きな地震に遭遇するか知れないので、とっさにどう動いたらいいか? なかなか想像がつかないですよね。一度は訓練として体験してみる価値があると思いました。

<本日の題材>
DELETEと領域の解放(ORACLE)

ORACLEを使用している場合に、使用している表領域のサイズがかなり大きくなってしまったので、データを削除して使用できる領域を増やそうと考える場合があると思います。しかし、DELETE文で削除してコミットしたのに、表領域のサイズが変わらないという状況に出くわして、困ってしまう場合があると思います。今回は、この内容について取り上げてみたいと思います。

例)
できるだけサイズが大きいテーブルで確認したいので、以前、ビットマップインデックスの題材のときに作成した、「T_CUSTOMER」という300万件のテーブルを使ってみます。

テーブルの定義は、
DESCRIBE T_CUSTOMER

describe

件数を確認すると
SELECT COUNT(*) FROM T_CUSTOMER;

件数確認

 件数は300万件。データの中身を一部確認してみると、
SELECT * FROM T_CUSTOMER
WHERE C_ID <= 30
ORDER BY C_ID;

 データ中身

C_ID列は、シーケンシャルに番号が増えていってます。
このテーブルが占めているサイズを確認すると、

SELECT SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1024/1024 "MB"
FROM USER_SEGMENTS
WHERE SEGMENT_NAME LIKE '%T_CUSTOMER%'
GROUP BY SEGMENT_NAME, SEGMENT_TYPE
ORDER BY SEGMENT_TYPE DESC, SEGMENT_NAME;

テーブルサイズ確認

テーブル「T_CUSTOMER」が64MB、インデックス(主キー)「PK_T_CUSTOMER」が47MBであることが確認できます。

このテーブルから、200万件のデータを削除してみます。
DELETE FROM T_CUSTOMER
WHERE C_ID <= 2000000;

データDelete

この時点で、このテーブルの占めているサイズを再度確認してみます。

テーブルサイズ確認

確認したところ、サイズは全く変化がないことが確認できました。
この理由は、DELETE文では、ORACLEのハイウォーターマーク(HWM)に変化はなく、一度確保した領域は解放されないためです。ハイウォーターマーク(HWM)とは、テーブルなどに割り当てたブロックの中で、今までデータが挿入されたことがある最後尾のブロックのことで、その位置が変わらないと領域は解放されません。

このハイウォーターマーク(HWM)を低下させる方法としては、以下のような方法があります。
1.TRUNCATE でデータを削除する。
  ただし、データを全件削除することになります。
  (※最初に CREATE TABLE AS SELECT で元のテーブルを別テ
   ーブルにコピーしておけば、TRUNCATE後にデータを流し
   込むことはできます)
2.Export/Importでデータを復元する。
  Exportした後に、一度テーブルをDROPして削除してから
  Importします。
3.ALTER TABLE MOVE でテーブルを新しいセグメントに移動し
  て再作成する。
4.ALTER TABLE SHRINK SPACE による断片化の解消。

それでは、1.のTRUNCATE文でデータを削除してみます。
その前に、データを戻せるように別テーブルにコピーします。

CREATE TABLE COPY_T_CUSTOMER NOLOGGING
AS SELECT * FROM T_CUSTOMER;

Create_table_as_select

TRUNCATE文でデータを削除します。
TRUNCATE TABLE T_CUSTOMER;

truncate

処理時間もDELETE文に比べたら、全然速いです。

この時点で再度、テーブルのサイズを確認してみます。

truncate後のテーブルサイズ

TRUNCATEしたら、テーブル、インデックスとも、0.625MB ということで、領域が大きく解放されたことが確認できます。また、最初にデータをコピーして作成した COPY_T_CUSTOMER テーブルも、22MBということで、元の64MBよりは小さく作成されることもわかります。

この後、コピーしたデータを戻してCOMMITし、コピーした不要なテーブルを削除します。
INSERT INTO T_CUSTOMER SELECT * FROM COPY_T_CUSTOMER;
COMMIT;
DROP TABLE COPY_T_CUSTOMER;

データinsert

再度、テーブルのサイズを確認してみます。

insert後のテーブルサイズ

確かに、T_CUSTOMERのテーブルのサイズは、22MB、インデックスも30MBとなりました。

次に、2.のExport/Importを試してみます。

再度、データを300万件作成します。これは、以前、バルク処理の題材のときの方法で作成します。

300万件登録

再度、テーブルのサイズを確認してみます。

テーブルサイズ確認

このテーブルから、再度200万件のデータを削除します。

200万件削除

この後、Export/Importによってテーブルを作成しなおします。

expdp blog_test/パスワード directory=DP_DIR tables=t_customer dumpfile=t_customer.dmp
(※処理を行う前にディレクトリの作成とディレクトリへのread/write権限の付与が必要)

expdp

 表を削除します。
DROP TABLE T_CUSTOMER;

Drop_table

先ほどエクスポートしたデータをインポートします。

impdp blog_test/パスワード directory=DP_DIR dumpfile=t_customer.dmp

impdp

ここで、テーブルのサイズを確認してみます。

import後のテーブルサイズ

T_CUSTOMERのテーブルのサイズは、22MB、インデックスも18MBとなり、領域が解放されたことが確認できました。

次に、3.のテーブルを新しいセグメントに複製し、既存のものを削除するという方法(テーブルの移動)による再作成を行ってみます。

まずは、再度300万件のデータに戻し、その後、200万件のデータを削除します。(動作については省略)
このときのテーブル・インデックスのサイズは、テーブルが64MB、インデックスが47MB。

ALTER TABLE T_CUSTOMER MOVE;

alter_table_move

再度、テーブルのサイズを確認してみます。

alter_move後のテーブルサイズ

テーブルのサイズは、テーブルが64MBから22MBに小さくなっていることが確認できました。
LONG / LONG RAW のある表は、この方法での移動はできないとのこと。

最後に、4.のALTER TABLE SHRINK SPACE による断片化の解消を試してみたいと思います。
こちらもまずは、再度300万件のデータに戻し、その後、200万件のデータを削除します。(動作については省略)
このときのテーブル・インデックスのサイズは、テーブルが64MB、インデックスが47MB。

断片化の解消をしたいテーブル「T_CUSTOMER」に対して、以下のSQLを順に実行します。

ALTER TABLE T_CUSTOMER ENABLE ROW MOVEMENT;
ALTER TABLE T_CUSTOMER SHRINK SPACE CASCADE;
ALTER TABLE T_CUSTOMER DISABLE ROW MOVEMENT;

alter_table_shrink

2番目のSHRINK SPACE CASCADE の処理に多少時間がかかりました。
ここで、テーブルのサイズを確認してみます。

alter_shrink後のテーブルサイズ

T_CUSTOMERのテーブルのサイズは、21.625MB、インデックスも16MBとなり、領域が解放されたことが確認できました。
なお、レコード数が少ない場合においては、ハイウォーターマークが思ったより低下しない場合もあり、これは、行移行や行連鎖の状態によって効果は異なるようです。連鎖状態を解消してからであれば、効果は高いということです。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

「DELETEと領域の解放(ORACLE)」への2件のフィードバック

  1. 古いブログ記事にコメント申し訳ありません。
    Oracle初心者なのですが、とてもためになりました。
    上記コマンドを順に実行し、データ削除まで正常に出来ましたが、「ALTER TABLE T_CUSTOMER SHRINK SPACE CASCADE;」を実行するところで、「行1でエラーが発生しました。:ORA-10635: Invalid segment or tablespace type」となってしまいます。
    ググってみましたが原因が分からず、原因分かりますでしょうか。お忙しいところ申し訳ございません。
    Oracleは11gというものをインストールしております。

    1. SHRINK SPACE による断片化の解消ができるためには、表領域がローカル管理表領域、
      自動セグメント領域管理の場合である必要があります。
      そうでない場合に、
      「ORA-10635: Invalid segment or tablespace type」
      というエラーが出るということです。
      データディクショナリのUSER_TABLESPACES、もしくはDBA_TABLESPACESの
      EXTENT_MANAGEMENT が LOCAL、SEGMENT_SPACE_MANAGEMENT が AUTO になって
      いることを確認してみてください。そうでなければ変更する必要があります。

ITC シュウちゃん にコメントする コメントをキャンセル

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>