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

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

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

＜本日の題材＞
DELETEと領域の解放（ORACLE）

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

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

テーブルの定義は、
DESCRIBE T_CUSTOMER

件数を確認すると
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文では、ORACLEのハイウォーターマーク(HWM)に変化はなく、一度確保した領域は解放されないためです。ハイウォーターマーク(HWM)とは、テーブルなどに割り当てたブロックの中で、今までデータが挿入されたことがある最後尾のブロックのことで、その位置が変わらないと領域は解放されません。

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

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

CREATE TABLE COPY_T_CUSTOMER NOLOGGING
AS SELECT * FROM T_CUSTOMER;

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

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

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

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

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

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

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

次に、２．のExport/Importを試してみます。

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

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

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

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

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

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

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

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

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

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

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

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

ALTER TABLE T_CUSTOMER MOVE;

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

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

最後に、４．の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;

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

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

今日は以上まで

にほんブログ村