kagamihogeの日記

kagamihogeの日記です。

truncateとdeleteのちがいを使用ブロック数から見てみる

テーブル切捨てのtruncateと、条件無しでdeleteは、どちらも結果的にテーブルから全行削除される。しかし、両者の動作は異なるもので、想定される使い方も異なる。Oracleの運用ミス事例でもしばしば取り上げられる。システムはなぜダウンするのかのp.232「2つの削除コマンドの使い分けを誤る」など。

というわけで、実際にSQLを実行しながら両者の違いを見てみるが、使用ブロック数に着目しながら確認してみる。

準備

このエントリ内で使用するクエリについて。

「テスト用のテーブル作成」と書いた場合、下記クエリを指す、とする。

drop table hoge purge;
create table hoge (hoge_id integer not null);

「10000件データ挿入する」と書いた場合、下記クエリを指す、とする。

insert into hoge
select 
  rownum
from
  (select rownum from all_catalog where rownum <= 1000),
  (select rownum from all_catalog where rownum <= 10);
commit;

「テーブルのブロック確保数を確認する」と書いた場合、下記クエリを指す、とする。

select segment_name, bytes, blocks, extents
from   user_segments
where  segment_name IN ('HOGE');

「テーブルの使用・未使用ブロック数を確認する」と書いた場合、下記クエリを指す、とする。

analyze table kagamihoge.hoge compute statistics;
select blocks, empty_blocks 
from   dba_tables
where  table_name = 'HOGE';

「テーブルフルスキャン」と書いた場合、下記クエリを指す、とする。

select * from hoge; 

「delete」と書いた場合、下記クエリをさす、とする。

delete from hoge;
commit;

「truncate」と書いた場合、下記クエリをさす、とする。

truncate table hoge drop storage;

確認していくこと

前提データの確認

まず、テーブルを作成して10000件データ挿入した直後のテーブルのブロック確保数、使用・未使用ブロック数を確認するを確認する。

BYTES BLOCKS EXTENTS BLOCKS(DBA_TABLES) EMPTY_BLOCKS(DBA_TABLES)
196608 24 3 20 4

テーブルフルスキャンの自動トレース統計を取っておく。

統計
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        714  consistent gets
         21  physical reads
          0  redo size
     148795  bytes sent via SQL*Net to client
       7690  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

テーブルフルスキャンの自動トレース統計を取るときには、下記でバッファキャッシュをクリアする。

alter system flush buffer_cache;
deleteとtruncate直後の使用ブロック数

下記それぞれの手順を行ったあと、テーブルのブロック確保数、使用・未使用ブロック数を確認する。

  1. テスト用のテーブル作成→10000件データ挿入する→delete
  2. テスト用のテーブル作成→10000件データ挿入する→truncate
種類 BYTES BLOCKS EXTENTS BLOCKS(DBA_TABLES) EMPTY_BLOCKS(DBA_TABLES)
delete 196608 24 3 20 4
truncate 65536 8 1 0 8

deleteの場合、削除した前後で使用ブロック数が変化しないことがわかる。対して、truncateの場合、使用ブロックが切り捨てられたことがわかる。

全行削除された状態から、もう一度10000件データ挿入し、テーブルのブロック確保数、使用・未使用ブロック数を確認する。

種類 BYTES BLOCKS EXTENTS BLOCKS(DBA_TABLES) EMPTY_BLOCKS(DBA_TABLES)
delete 196608 24 3 20 4
truncate 196608 24 3 20 4

deleteは使用ブロック数が変化しないので、先ほどまで使用していたブロックを使用したことが分かる。対して、truncateは改めてブロック*1を確保し直したことがわかる。

以上のことから、deleteとtruncateの違いの1つは、ブロックの解放の有無が上げられる。

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

ブロック解放の有無で気をつけなければならないもの1つが、ダイレクトパスインサートである。ダイレクトパスインサートの説明として、HWM以降にデータを挿入する、というのがある。deleteではHWMは変化しないため、領域をムダに取ってしまう可能性がある。それを確認する。

下記それぞれの手順を行ったあと、テーブルのブロック確保数、使用・未使用ブロック数を確認する。

  1. テスト用のテーブル作成→10000件データ挿入する→delete→APPENDヒント付きで10000件データ挿入する
  2. テスト用のテーブル作成→10000件データ挿入する→truncate→APPENDヒント付きで10000件データ挿入する

なお、APPENDヒント付きで〜は下記のクエリを使用する。

insert /*+ APPEND */ into hoge
select 
  rownum
from
  (select rownum from all_catalog where rownum <= 1000),
  (select rownum from all_catalog where rownum <= 10);
commit;

各手順実行後の、テーブルのブロック確保数、使用・未使用ブロック数を確認する。

種類 BYTES BLOCKS EXTENTS BLOCKS(DBA_TABLES) EMPTY_BLOCKS(DBA_TABLES)
delete 393216 48 6 41 7
truncate 196608 24 3 20 4

deleteは、約2倍分になっている。deleteは、10000件分のブロック確保時のHWMはそのままなので、その位置から更に10000件データ挿入すれば、約2倍になる計算である。対して、truncateは切り捨てられるので、使用するブロックは結局同じ数値になるので、確保ブロック数も変化しない。

テーブルフルスキャン

ちょっとくらい余分な領域を使ってしまう程度なら許容範囲かもしれない。しかし、テーブルフルスキャンは全ブロック読み込みを行う点に注意が必要である。この点について、自動トレースを取りながら確認してみる。

下記それぞれの手順を行ったあと、テーブルフルスキャンの自動トレースを取る。

  1. テスト用のテーブル作成→10000件データ挿入する→delete→APPENDヒント付きで10000件データ挿入する
  2. テスト用のテーブル作成→10000件データ挿入する→truncate→APPENDヒント付きで10000件データ挿入する

下記は、deleteケースのフルスキャンの自動トレース。

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        706  consistent gets
         37  physical reads
          0  redo size
     148795  bytes sent via SQL*Net to client
       7690  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

下記は、truncateケースのフルスキャンの自動トレース。

統計
---------------------------------------------------------
         0  recursive calls
         0  db block gets
       686  consistent gets
        17  physical reads
         0  redo size
    148795  bytes sent via SQL*Net to client
      7690  bytes received via SQL*Net from client
       668  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
     10000  rows processed

注目点は、どちらも10000 rows processedであるにも関わらず、deleteは37 physical readsで、truncateは17 physical readsになっていること。なお「前提データの確認」では21 physical readsになっている。これは、同じ行数を返すにも関わらず、ディスクアクセスのブロック数が1.7倍〜2.0倍違うことを示している。

テーブルフルスキャンは、アプリケーション側から見れば全行読み込みだが、Oracle的には全ブロック読み込みと言ったほうが実体に近い。よって、delete-insertが激しくデータサイズも激しく変動するテーブルは注意が必要になる。確保したブロックに占める削除領域があまりにも多すぎるとテーブルフルスキャンは、著しく非効率になる可能性がある。

感想とか

そんなわけで、一般によく言われるdeleteとtruncateの違いを、実際にクエリを実行しながら、主に使用ブロック数の面から確認することで理解を深められました。

参考資料・参考文献

システムはなぜダウンするのか

システムはなぜダウンするのか

Oracleの場合、データを削除するとともにパーティション領域を開放するには、「SQL文」で「TRUNCATE」命令もしくは「DROP」命令を発行しなければなりませんでした。(中略)ところが、データだけを削除する「DELETE」命令を使ったのです。このため、「空」のパーティション領域が解放されずに蓄積していきました。それでも稼動後しばらくは、2年分の領域を確保していたために、問題は表面化しませんでした。稼動から2年が経過したタイミングで、ダウンが発生したのでした。

システムはなぜダウンするのか 第6章 その「うっかり」が致命傷 運用・操作ミス 6.1 運用コマンドを間違える 2つの削除コマンドの使い分けを誤る p.233 より抜粋

*1:正確にはエクステントを〜と書くべきなんだろうけど、意味的にはアンマリ変わんないのでブロックにした