インデックス追加時の影響

DB

 

(5.6)

drop table tab1;
create table tab1(col1 int,col2 timestamp(3));
alter table tab1 add constraint tab1p primary key(col1);


drop procedure proc1;

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

set i = 1;

while i <= 100000 do
insert into tab1 values(i,null);
set i = i + 1;
end while;
end
//
delimiter ;

call proc1();

--session1から実行(インデックス追加・削除)

drop procedure proc2;

delimiter //
create procedure proc2()
begin
declare vSQL varchar(1000);


while true do
set @q := 'create index ind1 on tab1(col1,col2)';
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;

set @q := 'drop index ind1 on tab1';
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;

end while;
end
//
delimiter ;

call proc2();

--session2から実行(更新)

drop procedure proc3;

delimiter //
create procedure proc3()
begin
declare i int;

set i = 1;

while i <= 100000 do
update tab1 set col2 = CURRENT_TIMESTAMP(3) where col1 = i;
insert into tab1 values(0,CURRENT_TIMESTAMP(3) );
delete from tab1 where col1 = 0;
set i = i + 1;
end while;
end
//
delimiter ;

call proc3();

--結果


インデックス追加・削除有の場合
→1 min 56.89 sec

インデックス追加・削除なしの場合
→56.58 sec

 

(12cR1)

 

--データ準備
set time on
set timing on

drop table tab1 purge;
create table tab1(col1 int,col2 timestamp);
alter table tab1 add constraint tab1p primary key (col1);


declare
begin
for i in 1..100000 loop
insert into tab1 values(i,NULL);
commit;
end loop;
end;
/


--session1から実行(インデックス追加・削除)

declare
sSQL varchar2(1000);
begin
while true loop
sSQL :='create index ind1 on tab1(col1,col2) online';
execute immediate sSQL;

sSQL :='drop index ind1 online';
execute immediate sSQL;
end loop;
end;
/


--session2から実行(更新)

declare
begin
for i in 1..100000 loop
update tab1 set col2 = systimestamp where col1 = i;
insert into tab1 values(0,sysdate);
delete from tab1 where col1 = 0;
commit;

end loop;
end;
/


--結果

インデックス追加・削除有の場合
→24.59秒

インデックス追加・削除なしの場合
→17.79秒

 

 

(11)
--データ準備
\timing

drop table tab1;
create table tab1(col1 int,col2 timestamp);
create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1p primary key using index ind1;

insert into tab1 select generate_series(1,1000),NULL;


--session1から実行(インデックス追加・削除)

: > a.sql
for i in `seq 1 100000`; do
echo "create index concurrently ind2 on tab1(col1,col2);" >> a.sql
echo "drop index concurrently ind2;" >> a.sql
done

 


psql test -f a.sql

 


--session2から実行(更新)

drop procedure proc2();

create or replace procedure proc2() as
$$
declare
begin
for i in 1..10000 loop
update tab1 set col2 = clock_timestamp() where col1 = i;
insert into tab1 values(0, clock_timestamp());
delete from tab1 where col1 = 0;
commit;
end loop;
return;
end
$$
language 'plpgsql';

call proc2();

 


--結果

インデックス追加・削除有の場合
→01:44.234

インデックス追加・削除なしの場合
→00:03.784

 


(2014)


SET STATISTICS TIME ON

--データ準備
drop table tab1;
create table tab1(col1 int not null,col2 datetime2);


※プライマリキー作成時にクラスタ化インデックスがない場合、クラスタ化インデックスの主キーとなる
※プライマリキー作成時にクラスタ化インデックスがある場合、非クラスタ化インデックスの主キーとなる
※オンラインで削除できるのは、クラスター化インデックスだけ
※クラスタ化インデックスは複数作成できない

create clustered index ind2 on tab1(col1,col2) with ( online = on );

alter table tab1 add constraint tab1p primary key(col1);

drop index ind2 on tab1 with ( online = on );

DECLARE @i integer;
SET @i = 1;
WHILE @i <= 100000
BEGIN
insert into tab1 values(@i,NULL);
SET @i = @i + 1;
END

--session1から実行(インデックス追加・削除)

 

notepad a.bat

@echo off
type nul > a.sql
for /l %%n in (1,1,100000) do (
echo create clustered index ind2 on tab1^(col1,col2^) with ^( online = on ^); >> a.sql
echo drop index ind2 on tab1 with ^( online = on ^); >> a.sql
)
exit /b 0


sqlcmd -d test -b -i a.sql

--session2から実行(更新)

DROP PROCEDURE dbo.proc3;
GO
CREATE PROCEDURE dbo.proc3
AS
BEGIN
DECLARE @i integer;
SET @i = 1;
WHILE @i <= 10000
BEGIN
update tab1 set col2 = GETDATE() where col1 = @i;
insert into tab1 values(0, GETDATE() );
delete from tab1 where col1 = 0;
SET @i = @i + 1;
END
END
GO

EXEC test.dbo.proc3;
GO


--結果

インデックス追加・削除有の場合
→経過時間 = 46022 ミリ秒

インデックス追加・削除なしの場合
→経過時間 = 6362 ミリ秒