インデックスアクセス3種

DB

(8.0.22)

通常のインデックススキャン
→type=const or ref or range

インデックスオンリースキャン
→extra=Using index

インデックスフルスキャン
→type=index


drop table tab1;

create table tab1(col1 int, col2 int, col3 int, col4 int);

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


drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 1000000 do
insert into tab1 values(i,floor(rand() * 10000)+1,floor(rand() * 1000)+1,floor(rand() * 100)+1 );
set i = i + 1;
end while;
end
//
delimiter ;

start transaction;
call proc1();
commit;

select count(*) from tab1;

drop index ind1 on tab1;
create index ind1 on tab1(col2);


-- 主キーアクセス
explain
select t1.col2,t1.col3
from tab1 t1
where t1.col1 = 123
;

-- ユニークでないインデックス(等号)
explain
select t1.col2,t1.col3
from tab1 t1
where t1.col2 = 123
;

-- ユニークでないインデックス(不等号)
explain
select t1.col2,t1.col3
from tab1 t1
where t1.col2 between 100 and 500
;

-- インデックスのみのスキャン
explain
select t1.col2
from tab1 t1
where t1.col2 between 100 and 500
;

-- 表のようにインデックスフルスキャン
explain
select t1.col2
from tab1 t1
;

 

 

(19c)


通常のインデックススキャン
→INDEX UNIQUE SCAN or INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID

インデックスオンリースキャン
→INDEX UNIQUE SCAN or INDEX RANGE SCAN

インデックスフルスキャン
→INDEX FAST FULL SCAN


drop table tab1 purge;

create table tab1(col1 int, col2 int, col3 int, col4 int);

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

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,floor(dbms_random.value(1, 10001)),floor(dbms_random.value(1, 1001)),floor(dbms_random.value(1, 101)) );
commit;
end loop;
end;
/

select count(*) from tab1;

drop index ind1;
create index ind1 on tab1(col2);

-- 主キーアクセス
explain plan for
select t1.col2,t1.col3
from tab1 t1
where t1.col1 = 123
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-- ユニークでないインデックス(等号)
explain plan for
select t1.col2,t1.col3
from tab1 t1
where t1.col2 = 123
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-- ユニークでないインデックス(不等号)
explain plan for
select /*+ INDEX(t1 ind1) */ t1.col2,t1.col3
from tab1 t1
where t1.col2 between 100 and 500
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-- インデックスのみのスキャン
explain plan for
select t1.col2
from tab1 t1
where t1.col2 between 100 and 500
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-- 表のようにインデックスフルスキャン
explain plan for
select /*+ INDEX_FFS(t1 ind1) */ t1.col2
from tab1 t1
where t1.col2 between 100 and 500
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

 

(13)
https://www.postgresql.org/docs/13/indexes-index-only-scans.html

通常のインデックススキャン
→Index Scan

インデックスオンリースキャン
→Index Only Scan

※PostgreSQLの場合、追記方式のため、インデックス以外に可視性マップを確認する必要がある。

インデックスフルスキャン
→PostgreSQLの場合、なし。代わりというわけではないが、Bitmap Scanがある。

 

drop table tab1;

create table tab1(col1 int, col2 int, col3 int, col4 int);

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


do $$
declare
i int;
begin
i = 1;
while i <= 1000000 loop
insert into tab1 values(i,floor(random() * 10000)+1,floor(random() * 1000)+1,floor(random() * 100)+1 );
i = i + 1;
end loop;
end
$$
;


select count(*) from tab1;

drop index ind1;
create index ind1 on tab1(col2);

-- 主キーアクセス
explain analyze
select t1.col2,t1.col3
from tab1 t1
where t1.col1 = 123
;

-- ユニークでないインデックス(等号)
/*+ IndexScan(t1 ind1) */
explain analyze
select t1.col2,t1.col3
from tab1 t1
where t1.col2 = 123
;

-- ユニークでないインデックス(不等号)
/*+ IndexScan(t1 ind1) */
explain analyze
select t1.col2,t1.col3
from tab1 t1
where t1.col2 between 100 and 500
;

-- インデックスのみのスキャン
explain analyze
select t1.col2
from tab1 t1
where t1.col2 between 100 and 500
;

 

(2019)

通常のインデックススキャン
→Index Seek + キー参照

インデックスオンリースキャン
→Index Seek

インデックスフルスキャン
→Index Scan


drop table tab1;

create table tab1(col1 int not null, col2 int, col3 int, col4 int);

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

 

set nocount on
declare @i int;
set @i = 1;

while (@i <= 1000000)
begin
insert into tab1 values(@i,floor(rand() * 10000)+1,floor(rand() * 1000)+1,floor(rand() * 100)+1 );
set @i = @i + 1;
end


select count(*) from tab1;

drop index ind1 on tab1;
create index ind1 on tab1(col2);

-- 主キーアクセス
select t1.col2,t1.col3
from tab1 t1
where t1.col1 = 123
;

-- ユニークでないインデックス(等号)
select t1.col2,t1.col3
from tab1 t1
where t1.col2 = 123
;

-- ユニークでないインデックス(不等号)
select t1.col2,t1.col3
from tab1 t1 with (index( ind1 ))
where t1.col2 between 100 and 500
;

-- インデックスのみのスキャン
select t1.col2
from tab1 t1
where t1.col2 between 100 and 500
;

-- 表のようにインデックスフルスキャン
select t1.col2
from tab1 t1
;