テーブル切捨ての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';
- クエリの参考元:OTN Japan - Oracle9i 物理設計:第5部 領域監視 の「セグメントレベルの未使用領域の発生 〜HWM以前の未使用領域〜」より抜粋
「テーブルフルスキャン」と書いた場合、下記クエリを指す、とする。
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直後の使用ブロック数
下記それぞれの手順を行ったあと、テーブルのブロック確保数、使用・未使用ブロック数を確認する。
- テスト用のテーブル作成→10000件データ挿入する→delete
- テスト用のテーブル作成→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は変化しないため、領域をムダに取ってしまう可能性がある。それを確認する。
下記それぞれの手順を行ったあと、テーブルのブロック確保数、使用・未使用ブロック数を確認する。
- テスト用のテーブル作成→10000件データ挿入する→delete→APPENDヒント付きで10000件データ挿入する
- テスト用のテーブル作成→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は切り捨てられるので、使用するブロックは結局同じ数値になるので、確保ブロック数も変化しない。
テーブルフルスキャン
ちょっとくらい余分な領域を使ってしまう程度なら許容範囲かもしれない。しかし、テーブルフルスキャンは全ブロック読み込みを行う点に注意が必要である。この点について、自動トレースを取りながら確認してみる。
下記それぞれの手順を行ったあと、テーブルフルスキャンの自動トレースを取る。
- テスト用のテーブル作成→10000件データ挿入する→delete→APPENDヒント付きで10000件データ挿入する
- テスト用のテーブル作成→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の違いを、実際にクエリを実行しながら、主に使用ブロック数の面から確認することで理解を深められました。
参考資料・参考文献
- 作者: 大和田尚孝,日経コンピュータ
- 出版社/メーカー: 日経BP社
- 発売日: 2009/01/22
- メディア: 単行本
- 購入: 24人 クリック: 345回
- この商品を含むブログ (43件) を見る
Oracleの場合、データを削除するとともにパーティション領域を開放するには、「SQL文」で「TRUNCATE」命令もしくは「DROP」命令を発行しなければなりませんでした。(中略)ところが、データだけを削除する「DELETE」命令を使ったのです。このため、「空」のパーティション領域が解放されずに蓄積していきました。それでも稼動後しばらくは、2年分の領域を確保していたために、問題は表面化しませんでした。稼動から2年が経過したタイミングで、ダウンが発生したのでした。
システムはなぜダウンするのか 第6章 その「うっかり」が致命傷 運用・操作ミス 6.1 運用コマンドを間違える 2つの削除コマンドの使い分けを誤る p.233 より抜粋
*1:正確にはエクステントを〜と書くべきなんだろうけど、意味的にはアンマリ変わんないのでブロックにした