(5.6)
テーブルの件数:100万
取得件数:1万,10万,100万
フルスキャンとインデックスアクセスの処理時間を比較
drop table tab1;
drop table tab2;
create table tab1(col1 int);
create table tab2(col1 int);
alter table tab2 add constraint tab2pk primary key(col1);
drop procedure proc1;
delimiter //
create procedure proc1(in param1 integer)
begin
declare i int;
set @q := 'truncate table tab1';
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set i = 1;
while i <= param1 do
insert into tab1 values(i);
set i = i + 1;
end while;
end
//
delimiter ;
call proc1(1000000);
select count(*) from tab1;
insert into tab2 select * from tab1 order by rand();
select * from tab2 limit 100;
→MySQLの主キーはクラスタ化インデックスのため、物理的にランダム格納はできない
analyze table tab2;
deallocate prepare plan;
prepare plan from '
explain
select
count(*)
from tab2 t1 use index (primary)
where col1 <= ?
';
deallocate prepare plan;
prepare plan from '
explain
select
count(*)
from tab2 t1 ignore index (primary)
where col1 <= ?
';
deallocate prepare plan;
prepare plan from '
select
count(*)
from tab2 t1 ignore index (primary)
where col1 <= ?
';
deallocate prepare plan;
prepare plan from '
select
count(*)
from tab2 t1 use index (primary)
where col1 <= ?
';
set @a = 10000;
set @a = 100000;
set @a = 1000000;
execute plan using @a;
--確認結果
フルスキャンの場合
1万件→0.18 sec
10万件→0.18 sec
100万件→0.20 sec
インデックスアクセスの場合
1万件→0.00 sec
10万件→0.02 sec
100万件→0.23 sec
(12cR1)
テーブルの件数:100万
取得件数:1万,10万,100万
フルスキャンとインデックスアクセスの処理時間を比較
drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 int);
create table tab2(col1 int);
create index ind2 on tab2(col1);
alter table tab2 add constraint tab2pk primary key (col1) using index ind2;
create or replace procedure proc1(param1 in number) as
begin
execute immediate 'truncate table tab1';
for i in 1..param1 loop
insert into tab1 values(i);
commit;
end loop;
end;
/
exec proc1(1000000);
select count(*) from tab1;
insert into tab2 select * from tab1 order by dbms_random.value();
commit;
select * from tab2 fetch first 100 rows only;
exec dbms_stats.gather_table_stats('TEST','TAB2');
set time on
set timing on
variable val1 number;
execute :val1 := 10000
execute :val1 := 100000
execute :val1 := 1000000
explain plan for
select /*+ FULL(T1) */
count(*)
from tab2 T1
where col1 <= :val1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
explain plan for
select /*+ INDEX(T1 IND1) */
count(*)
from tab2 T1
where col1 <= :val1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
--確認結果
フルスキャンの場合
1万件→経過: 00:00:00.01
10万件→経過: 00:00:00.01
100万件→経過: 00:00:00.02
インデックスアクセスの場合
1万件→経過: 00:00:00.00
10万件→経過: 00:00:00.01
100万件→経過: 00:00:00.05
(9.4)
テーブルの件数:100万
取得件数:1万,10万,100万
フルスキャンとインデックスアクセスの処理時間を比較
drop table tab1;
drop table tab2;
create table tab1(col1 int);
create table tab2(col1 int);
create unique index ind2 on tab2(col1);
alter table tab2 add constraint tab2pk primary key using index ind2;
create or replace function fun1(integer)
returns void as
$$
begin
execute 'truncate table tab1';
for i in 1..$1 loop
insert into tab1 values(i);
end loop;
end;
$$ language plpgsql;
select fun1(1000000);
select count(*) from tab1;
insert into tab2 select * from tab1 order by random();
select * from tab2 limit 100;
analyze tab2;
\timing 1
deallocate plan;
prepare plan (int) as
select /*+ SeqScan(t1) */
count(*)
from tab2 t1
where col1 <= $1
;
deallocate plan;
prepare plan (int) as
select /*+ IndexScan(t1 tab2pk) */
count(*)
from tab2 t1
where col1 <= $1
;
explain analyze execute plan(10000);
explain analyze execute plan(100000);
explain analyze execute plan(1000000);
--確認結果
フルスキャンの場合
1万件→67.663 ms
10万件→76.950 ms
100万件→153.814 ms
インデックスアクセスの場合
1万件→7.422 ms
10万件→73.810 ms
100万件→688.718 ms
(2014)
テーブルの件数:100万
取得件数:1万,10万,100万
フルスキャンとインデックスアクセスの処理時間を比較
drop table tab1;
drop table tab2;
create table tab1(col1 int not null);
create table tab2(col1 int not null);
alter table tab2 add constraint tab2pk primary key nonclustered (col1);
drop procedure proc1;
create procedure proc1(@param1 int)
as
begin
declare @i integer;
execute('truncate table tab1');
set @i = 1;
while @i <= @param1
begin
insert into tab1 values(@i);
set @i = @i + 1;
end
end
;
exec proc1 1000000;
select count(*) from tab1;
insert into tab2 select * from tab1 order by newid() ;
select top 100 * from tab2;
→非クラスタ化インデックスにしてもランダムソートされない
update statistics tab2;
set statistics time on
set showplan_text on
declare @a int;
set @a = 10000;
select
count(*)
from tab2 t1 with(forcescan)
where col1 <= @a
;
declare @a int;
set @a = 100000;
select
count(*)
from tab2 t1 with(forcescan)
where col1 <= @a
;
declare @a int;
set @a = 1000000;
select
count(*)
from tab2 t1 with(forcescan)
where col1 <= @a
;
declare @a int;
set @a = 10000;
select
count(*)
from tab2 t1 with(index(tab2pk))
where col1 <= @a
;
declare @a int;
set @a = 100000;
select
count(*)
from tab2 t1 with(index(tab2pk))
where col1 <= @a
;
declare @a int;
set @a = 1000000;
select
count(*)
from tab2 t1 with(index(tab2pk))
where col1 <= @a
;
--確認結果
フルスキャンの場合
1万件→45 ミリ秒
10万件→42 ミリ秒
100万件→62 ミリ秒
インデックスアクセスの場合
1万件→0 ミリ秒
10万件→7 ミリ秒
100万件→68 ミリ秒
