Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

TRUNCATE TABLE と DROP TABLE の違い

TRUNCATE TABLE と DROP TABLE (& CREATE TABLE) には

• インデックス、トリガー、整合性制約、データ・ディクショナリ の削除
• 依存するオブジェクトへの影響
• 獲得したエクステント領域を解放するかしないかを選択できるか
• オプティマイザ 〜 旧統計情報の利用と動的サンプリング 〜
• フラッシュバックできるか否か

といった違いがある。

⇒ TRUNCATE TABLE と DELETE の違い

DROP TABLE

インデックス、トリガー、整合性制約 はテーブルに属するために削除される。 また、データ・ディクショナリ から、そのテーブルに関する内容も削除される(ビュー定義 、シノニム は残る)。

依存するオブジェクトへの影響
そのテーブルを参照している、ビュー、ストアドプログラムは INVALID (無効) 状態になる。 INVALID は依存関係のあるものに連鎖する。
もしパッケージが、その依存関係の連鎖に巻き込まれるとエラーを誘発する。

あるセッションの接続中にパッケージがロードされた状態(※)でパッケージが依存関係の連鎖によって INVALID になった場合、直後のパッケージ内のサブプログラムが呼び出されたときに

ORA-04068: パッケージの既存状態は廃棄されました。
ORA-04061: package body "schema.package_name"の既存状態は無効になりました。
ORA-06508: PL/SQL: コールしているプログラム単位が見つかりませんでした。

のエラーが発生する。これはセッション毎にパッケージの内部ステータスを管理するインスタンス(⇔クラス)が存在するためである。
セッションが一度パッケージをロードした後では、たとえ INVALID 直後のサブプログラムを呼び出す前に別セッションから再コンパイルしたとしても、パッケージの状態を保持したインスタンスがパッケージ定義(クラス相当)と矛盾があるのを解決することはできない。

(※) セッション情報にパッケージがインスタンス化されていない場合には、パッケージはインスタンス化する前に自動的に再コンパイルされてエラーもなくロードされる。

データセグメント領域の解放

TRUNCATE TABLE に REUSE STORAGE オプションを指定した場合には、エクステントを解放しない。同量のデータを投入する場合には、エクステントの確保の作業が不要になり効率的である。しかし、断片化 が進んだ状態のテーブルに使用すると未使用領域を大量に発生させる危険性がある。
ダイレクト・パス・インサートやパラレル DML を多用してデータ生成している場合には要注意。

実行計画の差異

適切にチューニングされていない環境では DROP TABLE でも TRUNCATE TABLE であっても最悪で約 1 日間*1非効率、または、不適切な実行計画で動作することがある。（Oracle 10g 時点）
双方とも関連のある実行計画はすべて DDL の実行時にご破算にされ、再度 ハード解析 から行われるが、 このときに データ・ディクショナリ が削除されたか、残っているかで以下のように(※) 実行計画が異なることがある。 ⇒ SQL*Plus で実行計画を取得する

(※) 単純なテストでの検証のため、実際には異なっている可能性があります。 統計情報は、適切なタイミングに正しく取得するようにしてください。

• DROP TABLE の場合
  このパターンにおいて、データの投入後に手動の統計情報の収集をしていない場合、 自動メンテナンスが開始されるまでの間は、ハード解析時に動的サンプリングが行われる。

もし投入したデータが以前の統計情報に近似している場合には DROP TABLE はサンプリング自体のオーバヘッドとサンプリングレートの低さから常に最適な実行計画を導き出せるとは限らなくなる、以前よりレスポンスが低下したと感じるであろう。

• TRUNCATE の場合
  TRUNCATE は収集済の統計情報を削除しない。そのため以前の統計情報でハード解析が行われる。

もし投入したデータが以前の統計情報から、かけ離れている場合には劇的に遅くなる可能性がある。これまた TRUNCATE TABLE によってレスポンスが低下したと感じるであろう。

どちらも統計情報を適切に取得する頻度とタイミングについて配慮していないことに原因がある。
また、統計情報を収集するという作業は非常にコストが高い。時系列によるデータの分布の遷移を思慮せずに 統計情報を安易に定期的に収集するのはサーバー資源を浪費することであり注意が必要である(夜間バッチの処理が朝までに間に合わなくなったりする)。

フラッシュバックできる？できない？

Oracle 10g 以降であれば DROP TABLE の場合には作業を取り消しすることが可能。
10g から (RECYCLEBIN と呼ばれる) ごみ箱のような機能が追加され DROP TABLE を フラッシュバックすることが可能。(※1)
TRUNCATE TABLE の場合には同じエクステントを継続使用することから テーブルのフラッシュバック機能によるデータ復旧(テーブルの復活)は行なうことができない。(※2)

FLASHBACK TABLE lost_table TO BEFORE DROP ;

(※1) SYSTEM 表領域 に作成した場合には即座に削除される。（Oracle 10g 時点）⇒ 参考 フラッシュバックできない表

(※2) FLASHBACK DATABASE を使用してデータベース全体としては修復可能。

---

FLASHBACK DATABASE に必要なライセンス

FLASHBACK DATABASE を使用するには アーカイブログ運用 を行なっていること。
FLASHBACK TABLE / DATABASE を使用するにはエンタープライズ・エディションのライセンスが必要。
(FLASHBACK TABLE 〜 TO BEFORE DROP の特記はないようであるが SE では使用できないとも想像しにくい。気になる方は問い合わせてみてください)

サイト統合にともない代替情報の URL は不明

• Oracle 10g Technical Data 主要機能一覧

TRUNCATE TABLE 関連事項

• TRUNCATE と DELETE の違い
• CREATE TABLE
• DROP TABLE
• ALTER TABLE
• 動的サンプリング
• 断片化
• ハイウォータマーク
• 統計情報の収集 (DBMS_STATS)