DELETE と TRUNCATE TABLE の違い
TRUNCATE TABLE(TRUNCATE CLUSTER) 操作は、データの件数の関係なく非常に高速にデータを削除することができる。SQL DELETE 操作と、どこが違い何か障害となるのか? TRUNCATE TABLE 操作には何かトレードオフがあるのか?
参考: TRUNCATE と DROP の違い
DELETE (DML)
ユーザーからの DELETE コマンドを発行したときに Oracle が行う主なタスク ※ 順番は精査してない
- ロールバック に備え UNDO を生成する。
- 文トリガー、行トリガー を実行する。
- REDO 情報を生成する。
- PCTUSED の設定値次第で削除されたブロックを空きリストに登録する。
- インデックスがある場合、インデックスの内容を削除する。
ユーザーからの COMMIT コマンドを発行したときに Oracle が行うタスク
- UNDO 領域の解放(読み取り一貫性によって参照される)
- DELETE ではエクステントの解放は行わない(※)
(※) HWM(ハイウォータマーク)の低下は発生しない。
全てのデータが消されようとも、一度確保した領域は保持し続け HWM も位置も変動しない。
テーブルフルスキャンでは、どこにデータが散在するか Oracle は判断できない。
データが 0 件でも先頭から HWM まで順番に読み込み操作を行う。
膨張と縮退が繰り返されるテーブルのテーブルフルスキャンは要注意である。
⇒ ハイウォータマークの操作方法
TRUNCATE TABLE (DDL)
TRUNCATE TABLE tablename [{PRESERVE|PURGE} MATERIALIZED VIEW LOG] [{DROP [ALL]|REUSE} STORAGE]
TRUNCATE TABLE はDELETE に比べて性能が良いというよりも、非常に良く「手を抜いている」
ユーザーからの TRUNCATE コマンドを発行したときに Oracle が行う主なタスク ※ 順番は精査してない
- [テーブルのロック]
- 拡張したエクステントを解放する。(INITIAL/MINEXTENTS 分は保持)
└ 領域解放の再帰SQLを発行
- HWM をリセットする。
- インデックスも同様
- 共有プール にキャッシュされている切り捨てを行ったオブジェクト情報をフラッシュ
- [テーブルのロック解除]
TRUNCATEに REUSE STORAGE オプションを指定した場合にはエクステントは解放しない
注意すべき点は
- DDL であり DROP TABLE できる権限が必要 (DELETE 権限では実行できない)。
- 自スキーマでない表の切り捨てには、DELETE 権限ではなく、DROP (ANY) 権限が必要となる。
(DROP ANY 権限では付与権限としては強力な分類なので要注意。)
- シノニム に対して TRUNCATE TABLE は使用できない。
- 削除レコードの UNDO が生成されない(＝ロールバックできない)
- FLASHBACK TABLE 〜 TO BEFORE DROP のような個別の復旧コマンドがない。
⇒ データベース単位のフラッシュバック FLASHBACK DATABASE (要アーカイブログ運用)
- 削除されたデータの REDO ログが生成されない。
⇒ 実行直後でも個別のやり直しができない。(DDL として TRUNCATE を行なったという REDOログは残る)
- DDL なので作業の前後で コミット される。(＝ トランザクション の要素として利用できない)
- ピークタイムに TRUNCATE を行うと一時的に大きな負荷を与える。
(依存関係のある SQL は破棄される…SQL文の ハード解析 が連続して発生する＝瞬間的な高い負荷)
高い負荷を伴うシステムではピークタイムに TRUNCATE TABLE(および DDL文全般)の実行は控える。
- ロックの影響を受けない SELECT と TRUNCATE は排他的な関係ではない。
単純な SELECT は共有ロックを取得しないため、SELECT と TRUNCATE は並行して実行される可能性があるかもしれない。(未検証)
参考: 表ロックの種類と相互関係
SELECT はキャッシュからせっせと情報を取り出す中、TRUNCATE はキャッシュと SQL をフラッシュする。仕事中の SELECT は八方ふさがり、途方にくれてしまうことになる。(※)
このことから TRUCATE 操作は抽出操作から隔離されたデータセグメントに使用しないと思わぬエラーに遭遇する可能性がある。
LOCK TABLE ROW EXCLUSIVE MODE を SELECT の前に行なうことで TRUNCATE 処理との衝突を避けることができるが、SELECT 時にロックするのは Oracle らしさが無くなるので控えたい。
DELETE の高速版と思って用いるとリリース後のピーク時になって問題が初めて見つかる可能性もある。
(致命的なバグほど起こって欲しくない時間帯を見定めて発生する。まさに、マーフィーの法則 
)
(※) 同じような事が ALTER SYSTEM FLUSH BUFFER_CACHE 
で発生するかもしれない。*1
PL/SQL で TRUNCATE を使用するためには、システム固有の動的SQL を使用するか
DBMS_SQL パッケージ経由で使用する必要がある。
⇒ 使用例 Code Library の TRUNCATE TABLE ラッパープロシージャ
FLASHBACK DATABASE / TABLE に必要なライセンス
FLASHBACK DATABASE を使用するには アーカイブログ運用 を行なっていること。
FLASHBACK TABLE / DATABASE を使用するにはエンタープライズ・エディションのライセンスが必要。
FLASHBACK TABLE 〜 TO BEFORE DROP に関するの特記事項はない。SE で許可されていないとも想像しにくいですが気になる方は問い合わせてみてください。
サイト統合にともない代替情報の URL は不明
TRUNCATE TABLE 関連事項
