dropとtruncateの速度比較

• MySQL

(8.0.22)

vim /etc/my.cnf
max_allowed_packet = 16MB;

show variables like 'max_allowed_packet';

set sql_log_bin = OFF;

drop table tab1;
create table tab1(
 col1  int
,col2  varchar(500)
,col3  varchar(500)
,col4  varchar(500)
,col5  varchar(500)
,col6  varchar(500)
,col7  varchar(500)
,col8  varchar(500)
,col9  varchar(500)
,col10 varchar(500)
,col11 varchar(500)
,col12 varchar(500)
,col13 varchar(500)
,col14 varchar(500)
,col15 varchar(500)
,col16 varchar(500)
,col17 varchar(500)
,col18 varchar(500)
,col19 varchar(500)
,col20 varchar(500)
,col21 varchar(500)
,col22 varchar(500)
,col23 varchar(500)
,col24 varchar(500)
,col25 varchar(500)
,col26 varchar(500)
,col27 varchar(500)
,col28 varchar(500)
,col29 varchar(500)
,col30 varchar(500)
);

set @i:=0; insert into tab1
  select @i:=@i+1 ,
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X'),
  rpad('X',500,'X')
from information_schema.columns;

drop procedure proc1;

delimiter //
create procedure proc1(in param1 integer)
begin
  declare i int;

  set i = 1;
  while i <= param1 do
    set @i:=0; insert into tab1
      select @i:=@i+1 ,
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X'),
      rpad('X',500,'X')
    from information_schema.columns;
    set i = i + 1;
  end while;
end
//
delimiter ;

call proc1(100);

2 hours 17 min 29.54 sec

analyze table tab1;
select
    table_name,
    table_rows as tbl_rows,
    floor( (data_length)/1024/1024/1024) AS gbyte
from information_schema.tables
where table_schema=database()
and table_name = 'tab1'
;

82G

[1] DROPの場合
drop table tab1;
0.78 sec

[2] TRUNCATEの場合
truncate table tab1;
0.70 sec

• Oracle

(19c)

set time on
set timing on

drop table tab1 purge;
create table tab1(
 col1  int
,col2  varchar2(4000)
,col3  varchar2(4000)
,col4  varchar2(4000)
,col5  varchar2(4000)
,col6  varchar2(4000)
,col7  varchar2(4000)
,col8  varchar2(4000)
,col9  varchar2(4000)
,col10 varchar2(4000)
,col11 varchar2(4000)
,col12 varchar2(4000)
,col13 varchar2(4000)
,col14 varchar2(4000)
,col15 varchar2(4000)
,col16 varchar2(4000)
,col17 varchar2(4000)
,col18 varchar2(4000)
,col19 varchar2(4000)
,col20 varchar2(4000)
,col21 varchar2(4000)
,col22 varchar2(4000)
,col23 varchar2(4000)
,col24 varchar2(4000)
,col25 varchar2(4000)
,col26 varchar2(4000)
,col27 varchar2(4000)
,col28 varchar2(4000)
,col29 varchar2(4000)
,col30 varchar2(4000)
) nologging;

create or replace procedure proc1(param1 in number,param2 in number)
as
  type t_tab1 is table of tab1%rowtype index by pls_integer;
  r_tab1  t_tab1;
  k  int;
begin
  k := 0;
  for i in param1..param2 loop  
    k := k + 1;
    r_tab1(k).col1  := i;
    r_tab1(k).col2  := rpad('X',4000,'X');
    r_tab1(k).col3  := rpad('X',4000,'X');
    r_tab1(k).col4  := rpad('X',4000,'X');
    r_tab1(k).col5  := rpad('X',4000,'X');
    r_tab1(k).col6  := rpad('X',4000,'X');
    r_tab1(k).col7  := rpad('X',4000,'X');
    r_tab1(k).col8  := rpad('X',4000,'X');
    r_tab1(k).col9  := rpad('X',4000,'X');
    r_tab1(k).col10 := rpad('X',4000,'X');
    r_tab1(k).col11 := rpad('X',4000,'X');
    r_tab1(k).col12 := rpad('X',4000,'X');
    r_tab1(k).col13 := rpad('X',4000,'X');
    r_tab1(k).col14 := rpad('X',4000,'X');
    r_tab1(k).col15 := rpad('X',4000,'X');
    r_tab1(k).col16 := rpad('X',4000,'X');
    r_tab1(k).col17 := rpad('X',4000,'X');
    r_tab1(k).col18 := rpad('X',4000,'X');
    r_tab1(k).col19 := rpad('X',4000,'X');
    r_tab1(k).col20 := rpad('X',4000,'X');
    r_tab1(k).col21 := rpad('X',4000,'X');
    r_tab1(k).col22 := rpad('X',4000,'X');
    r_tab1(k).col23 := rpad('X',4000,'X');
    r_tab1(k).col24 := rpad('X',4000,'X');
    r_tab1(k).col25 := rpad('X',4000,'X');
    r_tab1(k).col26 := rpad('X',4000,'X');
    r_tab1(k).col27 := rpad('X',4000,'X');
    r_tab1(k).col28 := rpad('X',4000,'X');
    r_tab1(k).col29 := rpad('X',4000,'X');
    r_tab1(k).col30 := rpad('X',4000,'X');
  end loop;
 
  forall  i in 1..(param2 - param1 + 1) 
    insert /*+ APPEND_VALUES */ into tab1 values r_tab1(i);
  commit; 
end;
/

declare
i_sta int := 0;
i_end int := 0;
begin
for i in 1..100 loop
  i_sta := i_end + 1;
  i_end := i_sta +10000;
  proc1(i_sta, i_end);
end loop;
end;
/

select sum(bytes/1024/1024/1024) gbytes from user_segments where segment_name = 'TAB1';

    GBYTES
----------
122.930664

経過: 00:06:09.11

[1] DROPの場合
drop table tab1 purge;

経過: 00:00:00.94

[2] TRUNCATEの場合
truncate table tab1;

経過: 00:00:00.62

• PostgreSQL

(14)

\timing 1

drop table tab1;
create UNLOGGED table tab1(
 col1  int
,col2  varchar(4000)
,col3  varchar(4000)
,col4  varchar(4000)
,col5  varchar(4000)
,col6  varchar(4000)
,col7  varchar(4000)
,col8  varchar(4000)
,col9  varchar(4000)
,col10 varchar(4000)
,col11 varchar(4000)
,col12 varchar(4000)
,col13 varchar(4000)
,col14 varchar(4000)
,col15 varchar(4000)
,col16 varchar(4000)
,col17 varchar(4000)
,col18 varchar(4000)
,col19 varchar(4000)
,col20 varchar(4000)
,col21 varchar(4000)
,col22 varchar(4000)
,col23 varchar(4000)
,col24 varchar(4000)
,col25 varchar(4000)
,col26 varchar(4000)
,col27 varchar(4000)
,col28 varchar(4000)
,col29 varchar(4000)
,col30 varchar(4000)
);

insert into tab1
  select g,
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X'),
  rpad('X',4000,'X')
  from generate_series(1,100000) g;

do $$
declare
begin
  for i in 1..9 loop
    insert into tab1 select * from tab1;
  end loop;
end
$$
;

SELECT pg_size_pretty(pg_relation_size('tab1'));

 pg_size_pretty
----------------
 98 GB
時間: 1003691.138 ミリ秒(16:43.691)

[1] DROPの場合
drop table tab1;

時間: 1341.362 ミリ秒(00:01.341)

[2] TRUNCATEの場合
truncate table tab1;

時間: 363.536 ミリ秒

• SQL Server

(2019)

新しいディスク(300GB)を追加し、復旧モデル=単純でテスト用のDBを作成する。

メッセージ 1105、レベル 17、状態 2、行 7
データベース 'tempdb' にオブジェクト '<temporary system object: 71635381588656128>' の領域を割り当てられませんでした。
'PRIMARY' ファイル グループがいっぱいです。不要なファイルの削除、ファイル グループ内のオブジェクトの削除、ファイル グループへの新しいファイルの追加、またはファイル グループの既存のファイルの自動拡張の設定のいずれかを行ってディスク領域を作成してください。

→ tempdbのデータファイルも追加

drop table tab1;
create table tab1(
 col1  int
,col2  varchar(4000)
,col3  varchar(4000)
,col4  varchar(4000)
,col5  varchar(4000)
,col6  varchar(4000)
,col7  varchar(4000)
,col8  varchar(4000)
,col9  varchar(4000)
,col10 varchar(4000)
,col11 varchar(4000)
,col12 varchar(4000)
,col13 varchar(4000)
,col14 varchar(4000)
,col15 varchar(4000)
,col16 varchar(4000)
,col17 varchar(4000)
,col18 varchar(4000)
,col19 varchar(4000)
,col20 varchar(4000)
,col21 varchar(4000)
,col22 varchar(4000)
,col23 varchar(4000)
,col24 varchar(4000)
,col25 varchar(4000)
,col26 varchar(4000)
,col27 varchar(4000)
,col28 varchar(4000)
,col29 varchar(4000)
,col30 varchar(4000)
);

insert into tab1
  select row_number() over(order by object_id),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000),
  replicate('X',4000)
  from sys.columns;

begin
set nocount on;
declare @i int;
set @i = 1;
while (@i <= 10)
  begin
  insert into tab1 select * from tab1;
  set @i = @i + 1;
  end
end
go

約2時間

EXEC sp_spaceused 'dbo.tab1';
go

122GB

[1] DROPの場合
drop table tab1;

1秒以下

[2] TRUNCATEの場合
truncate table tab1;

1秒以下