1. TRUNCATE文とは
TRUNCATE文は、テーブル内のすべてのデータを高速に削除するためのSQL文です。DELETE文とは異なり、TRUNCATE文はデータを物理的に削除し、UNDOログを生成しないため、削除処理が非常に高速です。
TRUNCATE TABLE テーブル名;💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
主な特徴
- 高速性: UNDOログを生成しないため、大量のデータを削除する際にDELETE文よりも高速です。
- WHERE句の非対応: DELETE文とは異なり、特定の条件を指定して削除することはできません。
- テーブル構造の保持: データのみを削除し、テーブルの構造や定義（カラム、インデックスなど）は保持されます。
- コミット不可: TRUNCATE文の操作は自動的にコミットされ、ロールバックできません。
- HWM（High Water Mark）のリセット: テーブルのHWMをリセットすることで、未使用領域が解放され、新たなデータ挿入時に効率が向上します。
2. TRUNCATE文の使用方法
以下に基本的なTRUNCATE文の使用例を示します。
基本的な例
-- EMPテーブルの全データを削除
TRUNCATE TABLE EMP;このコマンドを実行すると、EMPテーブル内のすべての行が削除されます。ただし、テーブルの構造はそのまま残ります。また、HWMもリセットされます。
業務シナリオでの利用例
- ログテーブルのクリア: 日次処理後に、ログテーブルをリセットする際に使用。
TRUNCATE TABLE LOG_TABLE; - 一時データの削除: バッチ処理で利用する中間テーブルをクリア。
TRUNCATE TABLE TEMP_TABLE;
3. TRUNCATE文とDELETE文の違い
| 特徴 | TRUNCATE文 | DELETE文 |
|---|---|---|
| 条件指定可能か | 不可 | WHERE句を使用して可能 |
| ロールバック可能か | 不可 | コミットする前であれば可能 |
| 処理速度 | 高速 | データ量が多い場合は遅い |
| トリガーの実行 | 実行されない | 実行される |
| テーブル構造の保持 | 保持される | 保持される |
| HWMのリセット | リセットされる | リセットされない |
パフォーマンス比較
以下は、100万件のデータを削除する際のパフォーマンス比較の例です。
| 操作 | 実行時間（秒） |
| DELETE文（WHEREなし） | 約50秒 |
| TRUNCATE文 | 約1秒 |
TRUNCATE文は、処理速度が圧倒的に速いことがわかります。
4. HWM（High Water Mark）とは
HWM（High Water Mark）は、データがどこまで書き込まれたかを示すマーカーです。HWMの位置は、テーブルのデータが削除されてもリセットされないため、DELETE文を使用した後でも、新しいデータ挿入時に以前のデータ領域を利用することはできません。しかし、TRUNCATE文を使用するとHWMがリセットされ、未使用領域が解放されます。
図解: HWMのリセット
初期状態:
[********** ] HWM
DELETE文実行後:
[ **********] HWM（変更なし）
TRUNCATE文実行後:
[ ] HWM（リセット）HWMのリセットによるメリット
- ストレージ効率の向上: 不要な領域が解放されるため、新規データ挿入時のパフォーマンスが向上します。
- フラグメンテーションの防止: HWMをリセットすることで、テーブル内のデータが効率的に配置されます。
5. 注意点
a. ロールバックできない
TRUNCATE文は実行時に自動的にコミットが行われるため、元の状態に戻すことができません。データを削除する前に慎重に検討してください。
b. 外部キー制約
TRUNCATE文は、外部キー制約が有効な場合には使用できません。外部キー制約を解除するか、DELETE文を使用する必要があります。
回避策: 外部キー制約を無効化
ALTER TABLE 子テーブル DISABLE CONSTRAINT 制約名;
TRUNCATE TABLE 親テーブル;
ALTER TABLE 子テーブル ENABLE CONSTRAINT 制約名;c. セキュリティ
TRUNCATE文の実行には、テーブルの所有者や十分な権限（DROP権限など）が必要です。
6. TRUNCATE文の実例
以下は、SCOTTスキーマを使用した例です。
例: EMPテーブルのデータ削除
-- EMPテーブルの現在のデータを確認
SELECT * FROM EMP;
-- TRUNCATE文を使用して全データを削除
TRUNCATE TABLE EMP;
-- 削除後のデータを確認
SELECT * FROM EMP;実行結果
TRUNCATE文実行後、EMPテーブルは空の状態になります。また、HWMがリセットされるため、新しいデータ挿入時の効率が向上します。
7. ベストプラクティス
- バックアップの確保: TRUNCATE文はロールバックできないため、実行前にデータをバックアップしましょう。
- 使用シナリオの限定: 一時データや中間テーブルのクリアなど、条件指定が不要な場面で使用することが推奨されます。
- 外部キー制約の確認: 外部キー制約がないことを確認した上で使用しましょう。
8. まとめ
TRUNCATE文は、大量のデータを高速に削除する際に非常に便利なSQL文です。特にHWMをリセットできる点は、パフォーマンス改善に大きく寄与します。ただし、条件指定ができず、ロールバックができない点には注意が必要です。適切なシナリオで使用することで、データ操作の効率を大幅に向上させることができます。
ぜひ、DELETE文と使い分けながら効果的に利用してください。
[参考]
Oracle Database SQL言語リファレンス 19c
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント