質問

とはどう違うのですか？ TRUNCATE と DELETE をSQLで表示できますか？

もしあなたの回答がプラットフォーム固有のものであれば、その旨を示してください。

解決方法は？

以下は、相違点の一覧です。Oracle固有の機能を強調しましたが、コミュニティが他のベンダーの固有の違いも追加してくれることを願っています。ほとんどのベンダーに共通する相違点は、見出しの下に直接記載し、相違点を下にハイライトしています。

---

全体概要

もし、あるテーブルから全ての行を素早く削除したい場合、そして、本当に削除したいのであれば、そして、そのテーブルに対して外部キーを持っていないのであれば、TRUNCATEはおそらくDELETEよりも高速になるでしょう。

以下に詳述するように、システム固有の様々な問題を考慮する必要がある。

---

ステートメントタイプ

DeleteはDML、TruncateはDDL( DDLとDMLとは何ですか？ )

---

コミットとロールバック

ベンダ別変数

SQL*Server

Truncateはロールバックすることができます。

PostgreSQL

Truncateはロールバックすることができます。

オラクル

TRUNCATEはDDLであるため、文の実行前と実行後の2回のコミットを伴います。したがって、Truncateはロールバックすることができず、Truncate処理で失敗すると、いずれにせよコミットが発行されます。

ただし、以下のフラッシュバックを参照。

---

空間再生

削除では容量が回復しない、切り捨てでは容量が回復する

オラクル

REUSE STORAGE句を使用すると、データセグメントは割り当て解除されないので、テーブルにデータを再ロードする場合、わずかながら効率的になります。ハイウォーターマークはリセットされます。

---

行の範囲

Delete は、すべての行または一部の行を削除するために使用されます。Truncateは、すべての行を削除します。

オラクル

テーブルがパーティション化されている場合、各パーティションを個別に切り捨てることができるため、テーブルの全データの部分的な削除が可能です。

---

オブジェクトの種類

削除は、テーブルとクラスタ内のテーブルに適用できます。Truncateは、テーブルまたはクラスタ全体にのみ適用されます。(オラクル固有の場合がある)

---

データオブジェクトのアイデンティティ

オラクル

Deleteはデータ・オブジェクトIDに影響を与えませんが、truncateは新しいデータ・オブジェクトIDを割り当てます。 ただし テーブルが作成されてから一度も挿入されていない場合でも、挿入が一度でもロールバックされると、切り捨て時に新しいデータオブジェクトIDが割り当てられる。

---

フラッシュバック（オラクル）

フラッシュバックは削除の間でも機能しますが、トランケートによって操作前の状態へのフラッシュバックはできなくなります。

ただし、11gR2からはFLASHBACK ARCHIVE機能により、Express Editionを除き、これが可能になります。

オラクルにおけるFLASHBACKの使用 http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638

---

特典

変数

オラクル

Deleteは他のユーザやロールにテーブル上で付与することができますが、truncateはDROP ANY TABLEの付与を使用しない限りはできません。

---

やり直し・元に戻す

Deleteは少量のRedoと大量のUndoを生成します。切り捨ては、それぞれごくわずかな量しか発生させません。

---

インデックス

オラクル

切り捨て操作は、使用不可能なインデックスを再び使用可能にします。削除はそうではありません。

---

外部キー

有効な外部キーがテーブルを参照している場合、切り捨てを適用することはできません。削除の扱いは、外部キーの設定に依存します。

---

テーブルロック

オラクル

切り捨てには排他的テーブルロックが必要で、削除には共有テーブルロックが必要です。したがって、テーブルロックを無効にすることは、テーブルに対する切り捨て操作を防止する方法です。

---

トリガー

DMLトリガーは切り捨てでは起動しません。

オラクル

DDLトリガーが使用可能です。

---

リモート実行

オラクル

データベースリンク上でTruncateを発行することはできません。

---

アイデンティティ・カラム

SQL*Server

Truncate は IDENTITY 列型のシーケンスをリセットし、delete はリセットしません。

---

結果セット

ほとんどの実装では DELETE 文は、削除された行をクライアントに返すことができます。

例えば、Oracle PL/SQL のサブプログラムでは、次のようなことができます。

DELETE FROM employees_temp
WHERE       employee_id = 299 
RETURNING   first_name,
            last_name
INTO        emp_first_name,
            emp_last_name;