(5.6)
カラム数1000のテーブルを作り、
インデックス数:0,10,64とした場合の更新時間を測定
--1.テーブル作成
drop table tab1;
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set @q := 'create table tab1(col1 int)';
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set i = 2;
while i <= 1000 do
select concat('alter table tab1 add (col', i , ' int)') into @q ;
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set i = i + 1;
end while;
end
//
delimiter ;
call proc1();
select column_name
from information_schema.columns
where table_schema='test'
and table_name = 'tab1'
order by ordinal_position
;
--2.インデックス追加
drop procedure proc2;
delimiter //
create procedure proc2(in param1 integer,in param2 integer)
begin
declare i int;
set i = param1;
while i <= param2 do
select concat('create index ind', i ,' on tab1(col', i ,')') into @q ;
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set i = i + 1;
end while;
end
//
delimiter ;
call proc2(1,10);
call proc2(11,64);
※MySQLのインデックスは64個が上限
select index_name
from information_schema.statistics
where table_schema='test'
and table_name = 'tab1'
order by cast(substr(index_name,4) as unsigned)
;
--3.実行計画取得
analyze table tab1;
--4.測定
drop procedure proc3;
delimiter //
create procedure proc3()
begin
declare i int;
declare k int;
set @q := 'truncate table tab1';
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set i = 1;
while i <= 1000 do
insert into tab1(col1) values(0);
set @q := 'update tab1 set col1 = 1 ';
set k = 1;
while k <= 64 do
select concat(@q,' ,col', k ,' =1 ') into @q ;
set k := k + 1;
end while;
select concat(@q,' where col1 = 0 ') into @q ;
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
delete from tab1 where col1 = 1;
set i = i + 1;
end while;
end
//
delimiter ;
call proc3();
--5.結果
インデックス0個→3.80 sec
インデックス10個→4.61 sec
インデックス64個→6.53 sec
(12cR1)
カラム数1000のテーブルを作り、
インデックス数:0,10,100,1000とした場合の更新時間を測定
--1.テーブル作成
drop table tab1 purge;
declare
sSQL varchar2(1000);
begin
sSQL :='create table tab1(col1 int)';
execute immediate sSQL;
for i in 2..1000 loop
sSQL :='alter table tab1 add (col' || to_char(i) || ' int)';
execute immediate sSQL;
end loop;
end;
/
select column_name from user_tab_cols where table_name = 'TAB1' order by column_id;
--2.インデックス追加
create or replace procedure proc1(param1 in number, param2 in number) as
sSQL varchar2(1000);
begin
for i in param1..param2 loop
sSQL :='create index ind' || to_char(i) || ' on tab1(col' || to_char(i) ||')';
execute immediate sSQL;
end loop;
end;
/
exec proc1(1,10);
exec proc1(11,100);
exec proc1(101,1000);
select index_name from user_indexes where table_name = 'TAB1' order by to_number(substr(index_name,4));
--3.実行計画取得
exec dbms_stats.gather_table_stats('TEST','TAB1');
--4.測定
set time on
set timing on
declare
sSQL varchar2(32767);
begin
sSQL :='truncate table tab1';
execute immediate sSQL;
for i in 1..1000 loop
insert into tab1(col1) values(0);
sSQL :='update tab1 set col1 = 1 ';
for k in 2..1000 loop
sSQL := sSQL || ' ,col' || to_char(k) || ' = 1 ';
end loop;
sSQL := sSQL || ' where col1 = 0 ';
execute immediate sSQL;
delete from tab1 where col1 = 1;
commit;
end loop;
end;
/
--5.結果
インデックス0個→経過: 00:00:00.78
インデックス10個→経過: 00:00:01.06
インデックス100個→経過: 00:00:03.51
インデックス1000個→経過: 00:00:50.40
(9.4)
カラム数1000のテーブルを作り、
インデックス数:0,10,100,1000とした場合の更新時間を測定
--1.テーブル作成
drop table tab1;
DO
$$
DECLARE
sSQL varchar(1000);
BEGIN
execute 'create table tab1(col1 int)';
for i in 2..1000 loop
sSQL :='alter table tab1 add col' || i::char(4) || ' int';
execute sSQL;
end loop;
END
$$
language 'plpgsql';
select column_name
from information_schema.columns
where table_catalog='test'
and table_name = 'tab1'
order by ordinal_position
;
--2.インデックス追加
drop function fun2(int,int);
create or replace function fun2(int,int)
returns void as
$$
DECLARE
sSQL varchar(1000);
BEGIN
for i in $1..$2 loop
sSQL :='create index ind' || i::char(4) || ' on tab1(col' || i::char(4) ||')';
execute sSQL;
end loop;
END
$$
language 'plpgsql';
select fun2(1,10);
select fun2(11,100);
select fun2(101,1000);
select t3.relname
from pg_class t1
inner join pg_index t2
on t1.relname = 'tab1'
and t1.oid = t2.indrelid
inner join pg_class t3
on t2.indexrelid = t3.oid
order by substr(t3.relname,4)::int
;
--3.実行計画取得
analyze tab1;
--4.測定
\timing 1
DO
$$
DECLARE
sSQL varchar;
BEGIN
sSQL :='truncate table tab1';
execute sSQL;
for i in 1..1000 loop
insert into tab1(col1) values(0);
sSQL :='update tab1 set col1 = 1 ';
for k in 2..1000 loop
sSQL := sSQL || ' ,col' || k::char(4) || ' = 1 ';
end loop;
sSQL := sSQL || ' where col1 = 0 ';
execute sSQL;
delete from tab1 where col1 = 1;
end loop;
END
$$
language 'plpgsql';
--5.結果
インデックス0個→時間: 14s
インデックス10個→時間: 16s
インデックス100個→時間: 16s
インデックス1000個→時間: 32s
(2014)
カラム数1000のテーブルを作り、
インデックス数:0,10,100,1000とした場合の更新時間を測定
--1.テーブル作成
drop table tab1;
declare @vsql varchar(1000);
declare @i integer;
begin
set @vsql = 'create table tab1(c1 int)';
execute(@vsql);
set @i = 2;
while @i <= 1000
begin
set @vsql = 'alter table tab1 add c' + cast( @i as varchar) + ' int';
execute(@vsql);
set @i = @i + 1;
end
end
select name
from sys.columns
where object_name(object_id)= 'tab1'
order by column_id
;
--2.インデックス追加
drop procedure proc2;
create procedure proc2(@param1 int, @param2 int)
as
begin
declare @vsql varchar(1000);
declare @i integer;
set @i = @param1;
while @i <= @param2
begin
if @i = 1
begin
set @vsql = 'create clustered index ind1 on tab1(c1)';
execute(@vsql);
end
else
begin
set @vsql = 'create index ind' + cast( @i as varchar) + ' on tab1(c' + cast( @i as varchar) + ')';
execute(@vsql);
end
set @i = @i + 1;
end
end
;
exec proc2 1,10;
exec proc2 11,100;
exec proc2 101,1000;
※SQL Serverの非クラスター化インデックスは999個が上限のため、1個目をクラスター化インデックスで作成
select name
from sys.indexes
where object_name(object_id)= 'tab1'
order by cast(substring(name,4,100) as integer)
;
--3.実行計画取得
update statistics tab1;
--4.測定
set statistics time on
drop procedure proc3;
create procedure proc3
as
begin
declare @vsql varchar(8000);
declare @i integer;
declare @k integer;
set @i = 1;
while @i <= 1000
begin
insert into tab1(c1) values(0);
set @vsql = 'update tab1 set c1=1';
set @k = 2;
while @k <= 1000
begin
set @vsql = @vsql + ',c' + cast(@k as varchar) + '=1';
set @k = @k + 1;
end
execute(@vsql);
delete from tab1 where c1 = 1;
set @i = @i + 1;
end
end
;
exec proc3
--5.結果
インデックス0個→経過時間 = 131秒
インデックス10個→経過時間 = 146秒
インデックス100個→経過時間 = 158秒
インデックス1000個→経過時間 = 272秒
