(8.0.26)
-- 1. テストデータ作成
drop table tab1;
create table tab1(
col1 bigint
,col2 varchar(1000)
-- ,col3 varchar(1000)
-- ,col4 varchar(1000)
-- ,col5 varchar(1000)
-- ,col6 varchar(1000)
-- ,col7 varchar(1000)
-- ,col8 varchar(1000)
-- ,col9 varchar(1000)
-- ,col10 varchar(1000)
-- ,col11 varchar(1000)
);
drop table tab2;
create table tab2(
col1 bigint
,col2 varchar(1000)
-- ,col3 varchar(1000)
-- ,col4 varchar(1000)
-- ,col5 varchar(1000)
-- ,col6 varchar(1000)
-- ,col7 varchar(1000)
-- ,col8 varchar(1000)
-- ,col9 varchar(1000)
-- ,col10 varchar(1000)
-- ,col11 varchar(1000)
);
drop table tab3;
create table tab3(
col1 bigint
,col2 varchar(1000)
-- ,col3 varchar(1000)
-- ,col4 varchar(1000)
-- ,col5 varchar(1000)
-- ,col6 varchar(1000)
-- ,col7 varchar(1000)
-- ,col8 varchar(1000)
-- ,col9 varchar(1000)
-- ,col10 varchar(1000)
-- ,col11 varchar(1000)
);
drop procedure proc1;
delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 0;
start transaction;
while i < x do
set i = i + 1;
insert into tab1 values(
i
,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
);
insert into tab2 values(
i
,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
);
insert into tab3 values(
i
,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
-- ,concat( md5(rand() ),md5( rand() ),md5( rand() ) )
);
end while;
commit;
end
//
delimiter ;
call proc1(1000000);
select count(*) from tab1;
select * from tab1 order by rand() limit 20;
create index ind1 on tab1(col1);
select count(*) from tab2;
select * from tab2 order by rand() limit 20;
create index ind2 on tab2(col1);
select count(*) from tab3;
select * from tab3 order by rand() limit 20;
create index ind3 on tab3(col1);
analyze table tab1;
analyze table tab2;
analyze table tab3;
--2. 動作確認
※クラスタ化インデックスの場合インデックススキャン=テーブルスキャンとなるため、非クラスタ化インデックスで確認
--2.1 インデックスフルスキャンの場合
explain analyze
select /*+ NO_JOIN_INDEX(t1 ind1) NO_JOIN_INDEX(t2 ind2) NO_JOIN_INDEX(t3 ind3) */
count(*) from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t1.col1 = t3.col1
;
ダミーカラム数=1 --> 2.24 sec
ダミーカラム数=5 --> 2.26 sec
ダミーカラム数=10 --> 2.23 sec
--2.2 テーブルフルスキャンの場合
explain analyze
select /*+ NO_INDEX(t1 ind1) NO_INDEX(t2 ind2) NO_INDEX(t3 ind3) */
count(*) from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t1.col1 = t3.col1
;
ダミーカラム数=1 --> 2.29 sec
ダミーカラム数=5 --> 6.17 sec
ダミーカラム数=10 --> 9.03 sec
--3. 結果
インデックスフルキャンのほうがテーブルフルスキャンより早い
(19c)
-- 1. テストデータ作成
drop table tab1 purge;
create table tab1(
col1 int
,col2 varchar2(1000)
-- ,col3 varchar2(1000)
-- ,col4 varchar2(1000)
-- ,col5 varchar2(1000)
-- ,col6 varchar2(1000)
-- ,col7 varchar2(1000)
-- ,col8 varchar2(1000)
-- ,col9 varchar2(1000)
-- ,col10 varchar2(1000)
-- ,col11 varchar2(1000)
);
drop table tab2 purge;
create table tab2(
col1 int
,col2 varchar2(1000)
-- ,col3 varchar2(1000)
-- ,col4 varchar2(1000)
-- ,col5 varchar2(1000)
-- ,col6 varchar2(1000)
-- ,col7 varchar2(1000)
-- ,col8 varchar2(1000)
-- ,col9 varchar2(1000)
-- ,col10 varchar2(1000)
-- ,col11 varchar2(1000)
);
drop table tab3 purge;
create table tab3(
col1 int
,col2 varchar2(1000)
-- ,col3 varchar2(1000)
-- ,col4 varchar2(1000)
-- ,col5 varchar2(1000)
-- ,col6 varchar2(1000)
-- ,col7 varchar2(1000)
-- ,col8 varchar2(1000)
-- ,col9 varchar2(1000)
-- ,col10 varchar2(1000)
-- ,col11 varchar2(1000)
);
declare
begin
for i in 1..1000000 loop
insert into tab1 values(
i
,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
);
insert into tab2 values(
i
,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
);
insert into tab3 values(
i
,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
-- ,standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')||standard_hash(dbms_random.value(), 'MD5')
);
end loop;
end;
/
commit;
select count(*) from tab1;
select * from tab1 order by dbms_random.value() fetch first 20 rows only;
alter table tab1 add constraint tab1pk primary key (col1);
select count(*) from tab2;
select * from tab2 order by dbms_random.value() fetch first 20 rows only;
alter table tab2 add constraint tab2pk primary key (col1);
select count(*) from tab3;
select * from tab3 order by dbms_random.value() fetch first 20 rows only;
alter table tab3 add constraint tab3pk primary key (col1);
set time on
set timing on
exec dbms_stats.gather_table_stats(user,'TAB1');
exec dbms_stats.gather_table_stats(user,'TAB2');
exec dbms_stats.gather_table_stats(user,'TAB3');
--2. 動作確認
--2.1 インデックスフルスキャンの場合
explain plan for
select /*+ INDEX_FFS(T1 TAB1PK) INDEX_FFS(T2 TAB2PK) INDEX_FFS(T3 TAB3PK) */
count(*) from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t1.col1 = t3.col1
;
select * from table(dbms_xplan.display(format=>'ALL') );
ダミーカラム数=1 --> 経過: 00:00:00.43
ダミーカラム数=5 --> 経過: 00:00:00.43
ダミーカラム数=10 --> 経過: 00:00:00.43
--2.2 テーブルフルスキャンの場合
explain plan for
select /*+ FULL(T1) FULL(T2) FULL(T3) */
count(*) from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t1.col1 = t3.col1
;
select * from table(dbms_xplan.display(format=>'ALL') );
ダミーカラム数=1 --> 経過: 00:00:00.38
ダミーカラム数=5 --> 経過: 00:00:00.99
ダミーカラム数=10 --> 経過: 00:00:08.41
--3. 結果
インデックスフルキャンのほうがテーブルフルスキャンより早い
※ただし、ダミーカラム数が少ない場合はテーブルフルスキャンのほうがわずかに効率が良い場合がある模様
(14)
-- 1. テストデータ作成
drop table tab1;
create table tab1(
col1 bigint
,col2 varchar(1000)
-- ,col3 varchar(1000)
-- ,col4 varchar(1000)
-- ,col5 varchar(1000)
-- ,col6 varchar(1000)
-- ,col7 varchar(1000)
-- ,col8 varchar(1000)
-- ,col9 varchar(1000)
-- ,col10 varchar(1000)
-- ,col11 varchar(1000)
);
drop table tab2;
create table tab2(
col1 bigint
,col2 varchar(1000)
-- ,col3 varchar(1000)
-- ,col4 varchar(1000)
-- ,col5 varchar(1000)
-- ,col6 varchar(1000)
-- ,col7 varchar(1000)
-- ,col8 varchar(1000)
-- ,col9 varchar(1000)
-- ,col10 varchar(1000)
-- ,col11 varchar(1000)
);
drop table tab3;
create table tab3(
col1 bigint
,col2 varchar(1000)
-- ,col3 varchar(1000)
-- ,col4 varchar(1000)
-- ,col5 varchar(1000)
-- ,col6 varchar(1000)
-- ,col7 varchar(1000)
-- ,col8 varchar(1000)
-- ,col9 varchar(1000)
-- ,col10 varchar(1000)
-- ,col11 varchar(1000)
);
start transaction;
insert into tab1 select
g
,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;
commit;
start transaction;
insert into tab2 select
g
,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;
commit;
start transaction;
insert into tab3 select
g
,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
-- ,md5(random()::text)||md5(random()::text)||md5(random()::text)
from generate_series(1,1000000) g;
commit;
select count(*) from tab1;
select * from tab1 order by random() limit 20;
alter table tab1 add constraint tab1pk primary key (col1);
select count(*) from tab2;
select * from tab2 order by random() limit 20;
alter table tab2 add constraint tab2pk primary key (col1);
select count(*) from tab3;
select * from tab3 order by random() limit 20;
alter table tab3 add constraint tab3pk primary key (col1);
\timing 1
set pg_hint_plan.debug_print = on;
analyze tab1;
analyze tab2;
analyze tab3;
--2. 動作確認
--2.1 インデックスオンリースキャンの場合
/*+ HashJoin(t1 t2) HashJoin(t1 t2 t3) IndexOnlyScan(t1 tab1pk) IndexOnlyScan(t2 tab2pk) IndexOnlyScan(t3 tab3pk) */
explain analyze
select
count(*) from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t1.col1 = t3.col1
;
ダミーカラム数=1 --> Execution Time: 1184.005 ms
ダミーカラム数=5 --> Execution Time: 1189.563 ms
ダミーカラム数=10 --> Execution Time: 1189.810 ms
--2.2 テーブルフルスキャンの場合
/*+ HashJoin(t1 t2) HashJoin(t1 t2 t3) SeqScan(t1) SeqScan(t2) SeqScan(t3) */
explain analyze
select
count(*) from tab1 t1
inner join tab2 t2
on t1.col1 = t2.col1
inner join tab3 t3
on t1.col1 = t3.col1
;
ダミーカラム数=1 --> Execution Time: 1385.841 ms
ダミーカラム数=5 --> Execution Time: 6673.240 ms
ダミーカラム数=10 --> Execution Time: 13066.414 ms
--3. 結果
インデックスオンリースキャンのほうがテーブルフルスキャンより早い
(2019)
-- 1. テストデータ作成
drop table tab1;
create table tab1(
col1 bigint not null
,col2 varchar(1000)
-- ,col3 varchar(1000)
-- ,col4 varchar(1000)
-- ,col5 varchar(1000)
-- ,col6 varchar(1000)
-- ,col7 varchar(1000)
-- ,col8 varchar(1000)
-- ,col9 varchar(1000)
-- ,col10 varchar(1000)
-- ,col11 varchar(1000)
);
drop table tab2;
create table tab2(
col1 bigint not null
,col2 varchar(1000)
-- ,col3 varchar(1000)
-- ,col4 varchar(1000)
-- ,col5 varchar(1000)
-- ,col6 varchar(1000)
-- ,col7 varchar(1000)
-- ,col8 varchar(1000)
-- ,col9 varchar(1000)
-- ,col10 varchar(1000)
-- ,col11 varchar(1000)
);
drop table tab3;
create table tab3(
col1 bigint not null
,col2 varchar(1000)
-- ,col3 varchar(1000)
-- ,col4 varchar(1000)
-- ,col5 varchar(1000)
-- ,col6 varchar(1000)
-- ,col7 varchar(1000)
-- ,col8 varchar(1000)
-- ,col9 varchar(1000)
-- ,col10 varchar(1000)
-- ,col11 varchar(1000)
);
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 100000
begin
insert into tab1 values(
@i
,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
);
insert into tab2 values(
@i
,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
);
insert into tab3 values(
@i
,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
-- ,master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) )+HASHBYTES('MD5',cast(rand() as varchar) ) )
);
set @i = @i + 1;
end
commit;
select count(*) from tab1;
select top 20 * from tab1 order by newid();
create nonclustered index ind1 on tab1(col1);
select count(*) from tab2;
select top 20 * from tab2 order by newid();
create nonclustered index ind2 on tab2(col1);
select count(*) from tab3;
select top 20 * from tab3 order by newid();
create nonclustered index ind3 on tab3(col1);
set statistics time on
update statistics tab1;
update statistics tab2;
update statistics tab3;
--2. 動作確認
※クラスタ化インデックスの場合インデックススキャン=テーブルスキャンとなるため、非クラスタ化インデックスで確認
--2.1 インデックスフルスキャンの場合
select
count(*) from tab1 t1 WITH(index(ind1), FORCESCAN )
inner join tab2 t2 WITH(index(ind2), FORCESCAN )
on t1.col1 = t2.col1
inner join tab3 t3 WITH(index(ind3), FORCESCAN )
on t1.col1 = t3.col1
OPTION (HASH JOIN,MAXDOP 1)
;
ダミーカラム数=1 --> 経過時間 = 121 ミリ秒
ダミーカラム数=5 --> 経過時間 = 122 ミリ秒
ダミーカラム数=10 --> 経過時間 = 122 ミリ秒
--2.2 テーブルフルスキャンの場合
select
count(*) from tab1 t1 WITH(index(0), FORCESCAN )
inner join tab2 t2 WITH(index(0), FORCESCAN )
on t1.col1 = t2.col1
inner join tab3 t3 WITH(index(0), FORCESCAN )
on t1.col1 = t3.col1
OPTION (HASH JOIN,MAXDOP 1)
;
ダミーカラム数=1 --> 経過時間 = 136 ミリ秒
ダミーカラム数=5 --> 経過時間 = 158 ミリ秒
ダミーカラム数=10 --> 経過時間 = 175 ミリ秒
--3. 結果
インデックスフルキャンのほうがテーブルフルスキャンより早い