SQLのtruncateによる高速テーブル削除と、構文についてまとめています。
SQLのtruncateの構文
truncateは、トランザクションログを作らずにレコードを削除するSQLです。条件指定ができずテーブルの全レコード削除のみ可能ですが、非常に高速。そのため、大量データが保管されたテーブルを短時間で空にしたい場合に便利です。
トランザクションログが作られないため、いったん削除したデータのロールバックはできません。
mysql> select count(*) from employees2; +----------+ | count(*) | +----------+ | 300025 | +----------+ 1 row in set (0.04 sec) mysql> select * from employees2 limit 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec) # deleteで削除した場合、３０万件削除に約2秒 mysql> delete from employees2; Query OK, 300025 rows affected (2.02 sec) #employees2テーブルのデータをもとに戻す # truncateで削除した場合、３０万件削除に約0.09秒 mysql> truncate table employees2; Query OK, 0 rows affected (0.09 sec)
上記は、30万件のデータをdeleteとtruncateで削除した例です。deleteでは2.02秒、truncateでは0.09秒かかっています。データ件数が多いほど、速度差はさらに広がると考えて良いでしょう。
【関連記事】
▶MySQLのtruncateとは？テーブル削除と切り捨ての2つの機能について解説
SQLのtruncateのDBMSごとの違い
Oracleのtruncateの構文
TRUNCATE TABLE [schema.] table
[ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
[ {DROP [ ALL ] | REUSE} STORAGE ] ;
なお、外部キー制約の親になっている表は、truncateで削除することができません。削除したい場合は、事前に制約を無効にしておく必要があります。
オプション指定で、マテリアラズド・ビュー・ログを保存するか削除するかを選択可能です。
SQL Serverのtruncateの構文
-- Syntax for SQL Server and Azure SQL Database
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name | table_name }
[ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ) ]
[ ; ]
<range> ::=
<partition_number_expression> TO <partition_number_expression>
関連）TRUNCATE TABLE (Transact-SQL) – SQL Server | Microsoft Docs
SQL Serverでは、パーティション分割されたテーブルをパーティション指定してtruncateすることが可能です。以下の例では、パーティション番号2,4,6,7,8がtruncateされます。
パーティション指定は、SQL Srever 2016（13.x）以降で使用可能です。
TRUNCATE TABLE PartitionTable1 WITH (PARTITIONS (2, 4, 6 TO 8)); GO
MySQLのtruncateの構文
MySQLのtruncateは、他のDBMSと違って特にオプション指定がありません。
TRUNCATE [TABLE] tbl_name
関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.33 TRUNCATE TABLE 構文
パーティション化されたテーブルに対してtruncateをおこなった場合でも、データやインデックスは削除されますが、パーティション定義は影響を受けません。
PostgreSQLのtruncateの構文
TRUNCATE [ TABLE ] name
関連）PostgreSQL公式ドキュメント：TRUNCATE
PostgreSQLのtruncateにはオプション指定は特にありません。他のテーブルから外部キー制約のあるテーブルにはtruncateを使用できないほか、truncateを実行してもON DELETEトリガは実行されません。
SQLのtruncateのまとめ
- SQLのtruncateは、高速に指定テーブルを削除する
- truncateはdeleteと違い、条件指定やロールバックはできない
- OralceやSQL Serverなど、パーティション領域への処理を指定できるDBMSもあり
