実行計画各種

DB

 

(12cR1)
drop table tab1;
drop table tab2;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

create unique index ind11 on tab1(col1);
create unique index ind12 on tab1(col2);

create index ind21 on tab2(col1);
create index ind22 on tab2(col2);

alter table tab1 add constraint cons1 primary key(col1) using index ind11;
alter table tab2 add constraint cons2 primary key(col1) using index ind21;

declare
begin
for i in 1..1000 loop
insert into tab1 values(i,i*2);
commit;
end loop;
end;
/

declare
begin
for i in 1..100000 loop
insert into tab2 values(i,mod(i,10));
commit;
end loop;
end;
/


execute dbms_stats.gather_table_stats('TEST','TAB1');
execute dbms_stats.gather_table_stats('TEST','TAB2');


--1テーブル(フルスキャン)

explain plan for
select * from tab1;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 8000 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TAB1 | 1000 | 8000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------


--1テーブル(インデックスユニーク)

explain plan for
select * from tab1
where col2 = 222;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IND12 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL2"=222)

--1テーブル(インデックスレンジ)


explain plan for
select /*+ INDEX(tab2 ind22) */ * from tab2
where col2 >= 222
and col2 < 224;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10163 | 81304 | 418 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB2 | 10163 | 81304 | 418 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND22 | 10163 | | 58 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL2">=222 AND "COL2"<224)

 

--1テーブル(高速全索引スキャン)

explain plan for
select /*+ INDEX_FFS(tab2 ind22) */ col2 from tab2
where col2 >= 1
and col2 < 999;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 294K| 76 (2)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IND22 | 100K| 294K| 76 (2)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("COL2">=1 AND "COL2"<999)


--2テーブル内部(NL)

explain plan for
select t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 9000 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1000 | 9000 | 3 (0)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND21 | 1 | 5 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."COL1"="T2"."COL1")

explain plan for
select /*+ LEADING(t2 t1) */ t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 9000 | 70 (19)| 00:00:01 |
| 1 | NESTED LOOPS | | 1000 | 9000 | 70 (19)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IND21 | 101K| 496K| 56 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | IND11 | 1 | 4 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."COL1"="T2"."COL1")

--2テーブル内部(HJ)

explain plan for
select /*+ USE_HASH(t1 t2) */ t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 9000 | 59 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 9000 | 59 (2)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | 2 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IND21 | 101K| 496K| 56 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."COL1"="T2"."COL1")

explain plan for
select /*+ USE_HASH(t1 t2) LEADING(t2 t1) */ t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 9000 | | 142 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 9000 | 1688K| 142 (1)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IND21 | 101K| 496K| | 56 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."COL1"="T2"."COL1")


--2テーブル内部(MJ)

explain plan for
select /*+ USE_MERGE(t1 t2) */ t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 9000 | | 373 (2)| 00:00:01 |
| 1 | MERGE JOIN | | 1000 | 9000 | | 373 (2)| 00:00:01 |
| 2 | SORT JOIN | | 1000 | 4000 | | 3 (34)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | | 2 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 101K| 496K| 2408K| 370 (2)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| IND21 | 101K| 496K| | 56 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."COL1"="T2"."COL1")
filter("T1"."COL1"="T2"."COL1")

explain plan for
select /*+ USE_MERGE(t1 t2) LEADING(t2 t1) */ t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 9000 | | 373 (2)| 00:00:01 |
| 1 | MERGE JOIN | | 1000 | 9000 | | 373 (2)| 00:00:01 |
| 2 | SORT JOIN | | 101K| 496K| 2408K| 370 (2)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IND21 | 101K| 496K| | 56 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1000 | 4000 | | 3 (34)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."COL1"="T2"."COL1")
filter("T1"."COL1"="T2"."COL1")


--2テーブル外部(NL)

explain plan for
select /*+ USE_NL(t1 t2) */ t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 1202K| 66866 (2)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 102K| 1202K| 66866 (2)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TAB2 | 102 | 816 | 67 (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T1"."COL1"="T2"."COL2"(+))


explain plan for
select /*+ USE_NL(t1 t2) */ t1.col1,t2.col1,t2.col2
from tab2 t1 left join tab1 t2
on t1.col1 = t2.col2;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 1290K| 138K (1)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 101K| 1290K| 138K (1)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IND21 | 101K| 496K| 56 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TAB1 | 1 | 8 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T1"."COL1"="T2"."COL2"(+))


--2テーブル外部(HJ)


explain plan for
select t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 1202K| 71 (2)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 102K| 1202K| 71 (2)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TAB2 | 101K| 793K| 69 (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."COL1"="T2"."COL2"(+))

explain plan for
select t1.col1,t2.col1,t2.col2
from tab2 t1 left join tab1 t2
on t1.col1 = t2.col2;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 1290K| 60 (2)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 101K| 1290K| 60 (2)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TAB1 | 1000 | 8000 | 3 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IND21 | 101K| 496K| 56 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."COL1"="T2"."COL2"(+))


--2テーブル外部(MJ)

explain plan for
select /*+ USE_MERGE(t1 t2) */ t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 1202K| | 442 (2)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 102K| 1202K| | 442 (2)| 00:00:01 |
| 2 | SORT JOIN | | 1000 | 4000 | | 3 (34)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | | 2 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 101K| 793K| 3208K| 439 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TAB2 | 101K| 793K| | 69 (2)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."COL1"="T2"."COL2"(+))
filter("T1"."COL1"="T2"."COL2"(+))

explain plan for
select /*+ USE_MERGE(t1 t2) */ t1.col1,t2.col1,t2.col2
from tab2 t1 left join tab1 t2
on t1.col1 = t2.col2;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 1290K| | 374 (2)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 101K| 1290K| | 374 (2)| 00:00:01 |
| 2 | SORT JOIN | | 101K| 496K| 2408K| 370 (2)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IND21 | 101K| 496K| | 56 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1000 | 8000 | | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TAB1 | 1000 | 8000 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T1"."COL1"="T2"."COL2"(+))
filter("T1"."COL1"="T2"."COL2"(+))


--union

explain plan for
select col1
from tab1
union
select col2
from tab2;


SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 301K| | 346 (2)| 00:00:01 |
| 1 | SORT UNIQUE | | 102K| 301K| 1208K| 346 (2)| 00:00:01 |
| 2 | UNION-ALL | | | | | | |
| 3 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TAB2 | 101K| 297K| | 69 (2)| 00:00:01 |
----------------------------------------------------------------------------------------


--union all

explain plan for
select col1
from tab1
union all
select col2
from tab2;


SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102K| 301K| 71 (2)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | INDEX FAST FULL SCAN| IND11 | 1000 | 4000 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TAB2 | 101K| 297K| 69 (2)| 00:00:01 |
-------------------------------------------------------------------------------

--group by

explain plan for
select col2,count(*)
from tab2
group by col2;


SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 72 (6)| 00:00:01 |
| 1 | HASH GROUP BY | | 10 | 30 | 72 (6)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB2 | 101K| 297K| 69 (2)| 00:00:01 |
---------------------------------------------------------------------------

--order by

explain plan for
select *
from tab2
order by col2;


SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 793K| | 439 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 101K| 793K| 1608K| 439 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB2 | 101K| 793K| | 69 (2)| 00:00:01 |
-----------------------------------------------------------------------------------

--サブクエリ(in)

explain plan for
select t1.col2
from tab1 t1
where t1.col1 in (select t2.col1 from tab2 t2);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 13000 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1000 | 13000 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB1 | 1000 | 8000 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND21 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."COL1"="T2"."COL1")

 

--サブクエリ(not in)

explain plan for
select t1.col2
from tab1 t1
where t1.col1 not in (select t2.col1 from tab2 t2);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 130 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 10 | 130 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB1 | 1000 | 8000 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND21 | 101K| 495K| 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."COL1"="T2"."COL1")


--サブクエリ(exists)

explain plan for
select t1.col2
from tab1 t1
where exists (select 1 from tab2 t2 where t2.col1 = t1.col1);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 13000 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1000 | 13000 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB1 | 1000 | 8000 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND21 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T2"."COL1"="T1"."COL1")


--サブクエリ(not exists)

explain plan for
select t1.col2
from tab1 t1
where not exists (select 1 from tab2 t2 where t2.col1 = t1.col1);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 130 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 10 | 130 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB1 | 1000 | 8000 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND21 | 101K| 495K| 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T2"."COL1"="T1"."COL1")

 

(5.6)

drop table tab1;
drop table tab2;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);


create index ind12 on tab1(col2);

create index ind22 on tab2(col2);

alter table tab1 add constraint cons1 primary key(col1);
alter table tab2 add constraint cons2 primary key(col1);


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

call proc1();

drop procedure proc2;
delimiter //
create procedure proc2()
begin
declare i int;
set i = 1;
while i <= 100000 do
insert into tab2 values(i, mod(i,10));
set i = i + 1;
end while;
end
//
delimiter ;

call proc2();

analyze table tab1;
analyze table tab2;

--1テーブル(フルスキャン)

explain
select * from tab1 IGNORE INDEX (ind12);

+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | tab1 | ALL | NULL | NULL | NULL | NULL | 1000 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

--1テーブル(インデックスユニーク)

explain
select * from tab1
where col1 = 222;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | tab1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

--1テーブル(インデックスレンジ)

explain
select * from tab2
where col2 >= 222
and col2 < 224;

+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| 1 | SIMPLE | tab2 | range | ind22 | ind22 | 5 | NULL | 1 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+

--1テーブル(高速全索引スキャン)

explain
select col2 from tab2;

+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+
| 1 | SIMPLE | tab2 | index | NULL | ind22 | 5 | NULL | 100464 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+


--2テーブル内部(NL)

explain
select t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| 1 | SIMPLE | t1 | index | PRIMARY | ind12 | 5 | NULL | 1000 | Using index |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.col1 | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+

explain
select t2.col1,t1.col1
from tab2 t2 straight_join tab1 t1
on t2.col1 = t1.col1;

+----+-------------+-------+--------+---------------+---------+---------+--------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+--------+-------------+
| 1 | SIMPLE | t2 | index | PRIMARY | ind22 | 5 | NULL | 100464 | Using index |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.col1 | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+--------------+--------+-------------+

--2テーブル外部(NL)

explain
select t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2;

+----+-------------+-------+-------+---------------+-------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+--------------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | ind12 | 5 | NULL | 1000 | Using index |
| 1 | SIMPLE | t2 | ref | ind22 | ind22 | 5 | test.t1.col1 | 5581 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+--------------+------+-------------+


--union

explain
select col1
from tab1
union
select col2
from tab2;

+----+--------------+------------+-------+---------------+-------+---------+------+--------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+-------+---------+------+--------+-----------------+
| 1 | PRIMARY | tab1 | index | NULL | ind12 | 5 | NULL | 1000 | Using index |
| 2 | UNION | tab2 | index | NULL | ind22 | 5 | NULL | 100464 | Using index |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+-------+---------+------+--------+-----------------+

 

--union all

explain
select col1
from tab1
union all
select col2
from tab2;

+----+--------------+------------+-------+---------------+-------+---------+------+--------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+-------+---------+------+--------+-----------------+
| 1 | PRIMARY | tab1 | index | NULL | ind12 | 5 | NULL | 1000 | Using index |
| 2 | UNION | tab2 | index | NULL | ind22 | 5 | NULL | 100464 | Using index |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+-------+---------+------+--------+-----------------+


--group by

explain
select col2,count(*)
from tab2 IGNORE INDEX (ind22)
group by col2;

+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| 1 | SIMPLE | tab2 | ALL | ind22 | NULL | NULL | NULL | 100464 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+


--order by

explain
select *
from tab2 IGNORE INDEX (ind22)
order by col2;

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | tab2 | ALL | NULL | NULL | NULL | NULL | 100464 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+


--サブクエリ(in)
explain
select t1.col2
from tab1 t1
where t1.col1 in (select t2.col1 from tab2 t2);

+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| 1 | SIMPLE | t1 | index | PRIMARY | ind12 | 5 | NULL | 1000 | Using index |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.col1 | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+


--サブクエリ(not in)
explain
select t1.col2
from tab1 t1
where t1.col1 not in (select t2.col1 from tab2 t2);

+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | t1 | index | NULL | ind12 | 5 | NULL | 1000 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t2 | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+

--サブクエリ(exists)

explain
select t1.col2
from tab1 t1
where exists (select 1 from tab2 t2 where t2.col1 = t1.col1);

+----+--------------------+-------+--------+---------------+---------+---------+--------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+--------------+------+--------------------------+
| 1 | PRIMARY | t1 | index | NULL | ind12 | 5 | NULL | 1000 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.col1 | 1 | Using index |
+----+--------------------+-------+--------+---------------+---------+---------+--------------+------+--------------------------+

--サブクエリ(not exists)

explain
select t1.col2
from tab1 t1
where not exists (select 1 from tab2 t2 where t2.col1 = t1.col1);

+----+--------------------+-------+--------+---------------+---------+---------+--------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+--------------+------+--------------------------+
| 1 | PRIMARY | t1 | index | NULL | ind12 | 5 | NULL | 1000 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.col1 | 1 | Using index |
+----+--------------------+-------+--------+---------------+---------+---------+--------------+------+--------------------------+

 

 

(9.4)

drop table tab1;
drop table tab2;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

create unique index ind11 on tab1(col1);
create index ind12 on tab1(col2);

create unique index ind21 on tab2(col1);
create index ind22 on tab2(col2);

alter table tab1 add constraint cons1 primary key using index ind11;
alter table tab2 add constraint cons2 primary key using index ind21;


insert into tab1 select generate_series(1,1000),generate_series(2,1000,2);
insert into tab2 select generate_series(1,100000),mod(generate_series(1,100000),10);


analyze tab1;
analyze tab2;


--1テーブル(フルスキャン)

explain analyze
select * from tab1;

-----------------------------------------------------------------------------------------------------
Seq Scan on tab1 (cost=0.00..15.00 rows=1000 width=8) (actual time=0.005..0.066 rows=1000 loops=1)
Planning time: 0.144 ms
Execution time: 0.113 ms


--1テーブル(インデックスユニーク)

explain analyze
select * from tab1
where col1 = 222;

------------------------------------------------------------------------------------------------------------
Index Scan using cons1 on tab1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (col1 = 222)
Planning time: 0.066 ms
Execution time: 0.032 ms

--1テーブル(インデックスレンジ)

explain analyze
select * from tab1
where col1 between 222 and 444;

-----------------------------------------------------------------------------------------------------------------
Index Scan using cons1 on tab1 (cost=0.28..17.71 rows=222 width=8) (actual time=0.012..0.044 rows=223 loops=1)
Index Cond: ((col1 >= 222) AND (col1 <= 444))
Planning time: 0.051 ms
Execution time: 0.061 ms

--1テーブル(高速全索引スキャン)

explain analyze
select col2 from tab2
where col2 >= 1
and col2 < 3;

------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tab2 (cost=494.04..1238.14 rows=20073 width=4) (actual time=1.316..4.585 rows=20000 loops=1)
Recheck Cond: ((col2 >= 1) AND (col2 < 3))
Heap Blocks: exact=443
-> Bitmap Index Scan on ind22 (cost=0.00..489.02 rows=20073 width=0) (actual time=1.261..1.261 rows=20000 loops=1)
Index Cond: ((col2 >= 1) AND (col2 < 3))
Planning time: 0.049 ms
Execution time: 5.222 ms

--1テーブル(インデックスオンリー)


explain analyze
/*+ IndexOnlyScan(tab2) */
select col2 from tab2
where col2 >= 1
and col2 < 3;

----------------------------------------------------------------------------------------------------------------------------
Index Only Scan using ind22 on tab2 (cost=0.29..2443.89 rows=20073 width=4) (actual time=0.017..3.861 rows=20000 loops=1)
Index Cond: ((col2 >= 1) AND (col2 < 3))
Heap Fetches: 20000
Planning time: 0.060 ms
Execution time: 4.460 ms


--2テーブル内部(NL)
explain analyze
/*+ NestLoop(t1 t2) */
select t1.col1,t2.*
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.29..3213.50 rows=1000 width=12) (actual time=0.017..1.271 rows=1000 loops=1)
-> Seq Scan on tab1 t1 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.007..0.068 rows=1000 loops=1)
-> Index Scan using cons2 on tab2 t2 (cost=0.29..3.19 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1000)
Index Cond: (col1 = t1.col1)
Planning time: 0.126 ms
Execution time: 1.322 ms

explain analyze
/*+ NestLoop(t1 t2) Leading((t2 t1)) */
select t1.col1,t2.*
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;


-------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.28..31983.00 rows=1000 width=12) (actual time=0.017..69.133 rows=1000 loops=1)
-> Seq Scan on tab2 t2 (cost=0.00..1443.00 rows=100000 width=8) (actual time=0.008..6.847 rows=100000 loops=1)
-> Index Only Scan using cons1 on tab1 t1 (cost=0.28..0.30 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=100000)
Index Cond: (col1 = t2.col1)
Heap Fetches: 1000
Planning time: 0.132 ms
Execution time: 69.188 ms


--2テーブル内部(HJ)

explain analyze
/*+ HashJoin(t1 t2) */
select t1.col1,t2.*
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

--------------------------------------------------------------------------------------------------------------------
Hash Join (cost=27.50..1855.50 rows=1000 width=12) (actual time=0.242..14.632 rows=1000 loops=1)
Hash Cond: (t2.col1 = t1.col1)
-> Seq Scan on tab2 t2 (cost=0.00..1443.00 rows=100000 width=8) (actual time=0.012..6.403 rows=100000 loops=1)
-> Hash (cost=15.00..15.00 rows=1000 width=4) (actual time=0.191..0.191 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 36kB
-> Seq Scan on tab1 t1 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.005..0.090 rows=1000 loops=1)
Planning time: 0.213 ms
Execution time: 14.679 ms

explain analyze
/*+ HashJoin(t1 t2) Leading((t1 t2)) */
select t1.col1,t2.*
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

--------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2693.00..2728.00 rows=1000 width=12) (actual time=18.965..19.758 rows=1000 loops=1)
Hash Cond: (t1.col1 = t2.col1)
-> Seq Scan on tab1 t1 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.006..0.065 rows=1000 loops=1)
-> Hash (cost=1443.00..1443.00 rows=100000 width=8) (actual time=18.933..18.933 rows=100000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 3907kB
-> Seq Scan on tab2 t2 (cost=0.00..1443.00 rows=100000 width=8) (actual time=0.004..6.651 rows=100000 loops=1)
Planning time: 0.111 ms
Execution time: 20.257 ms

 


--2テーブル内部(MJ)

explain analyze
select t1.col1,t2.*
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

---------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=0.61..88.84 rows=1000 width=12) (actual time=0.021..0.697 rows=1000 loops=1)
Merge Cond: (t1.col1 = t2.col1)
-> Index Only Scan using cons1 on tab1 t1 (cost=0.28..43.27 rows=1000 width=4) (actual time=0.005..0.080 rows=1000 loops=1)
Heap Fetches: 1000
-> Index Scan using cons2 on tab2 t2 (cost=0.29..3050.29 rows=100000 width=8) (actual time=0.004..0.168 rows=1001 loops=1)
Planning time: 0.126 ms
Execution time: 0.784 ms

 

explain analyze
/*+ Leading((t2 t1)) */
select t1.col1,t2.*
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

---------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=0.61..88.84 rows=1000 width=12) (actual time=0.011..0.680 rows=1000 loops=1)
Merge Cond: (t2.col1 = t1.col1)
-> Index Scan using cons2 on tab2 t2 (cost=0.29..3050.29 rows=100000 width=8) (actual time=0.004..0.237 rows=1001 loops=1)
-> Index Only Scan using cons1 on tab1 t1 (cost=0.28..43.27 rows=1000 width=4) (actual time=0.005..0.170 rows=1000 loops=1)
Heap Fetches: 1000
Planning time: 0.185 ms
Execution time: 0.737 ms


--2テーブル外部(NL)

explain analyze
/*+ NestLoop(t1 t2) */
select t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2;

-------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.29..278503.50 rows=100000 width=12) (actual time=0.031..25.765 rows=90991 loops=1)
-> Seq Scan on tab1 t1 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.010..0.085 rows=1000 loops=1)
-> Index Scan using ind22 on tab2 t2 (cost=0.29..178.49 rows=10000 width=8) (actual time=0.001..0.017 rows=90 loops=1000)
Index Cond: (t1.col1 = col2)
Planning time: 0.134 ms
Execution time: 28.490 ms


explain analyze
/*+ NestLoop(t1 t2) Leading((t2 t1)) */
select t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2;

----------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=10000000000.29..10000278503.50 rows=100000 width=12) (actual time=0.037..24.367 rows=90991 loops=1)
-> Seq Scan on tab1 t1 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.006..0.063 rows=1000 loops=1)
-> Index Scan using ind22 on tab2 t2 (cost=0.29..178.49 rows=10000 width=8) (actual time=0.001..0.016 rows=90 loops=1000)
Index Cond: (t1.col1 = col2)
Planning time: 0.106 ms
Execution time: 27.031 ms

 

--2テーブル外部(HJ)

explain analyze
select t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2;

--------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=27.50..2845.50 rows=100000 width=12) (actual time=0.223..23.992 rows=90991 loops=1)
Hash Cond: (t2.col2 = t1.col1)
-> Seq Scan on tab2 t2 (cost=0.00..1443.00 rows=100000 width=8) (actual time=0.005..6.372 rows=100000 loops=1)
-> Hash (cost=15.00..15.00 rows=1000 width=4) (actual time=0.212..0.212 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 36kB
-> Seq Scan on tab1 t1 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.004..0.110 rows=1000 loops=1)
Planning time: 0.136 ms
Execution time: 27.021 ms

explain analyze
select t2.col1,t1.col1,t1.col2
from tab2 t2 left join tab1 t1
on t2.col1 = t1.col2;

--------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=27.50..1980.50 rows=100000 width=12) (actual time=0.190..19.188 rows=100500 loops=1)
Hash Cond: (t2.col1 = t1.col2)
-> Seq Scan on tab2 t2 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.004..6.609 rows=100000 loops=1)
-> Hash (cost=15.00..15.00 rows=1000 width=8) (actual time=0.179..0.179 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 40kB
-> Seq Scan on tab1 t1 (cost=0.00..15.00 rows=1000 width=8) (actual time=0.006..0.084 rows=1000 loops=1)
Planning time: 0.192 ms
Execution time: 22.304 ms

--2テーブル外部(MJ)

explain analyze
/*+ MergeJoin(t1 t2) */
select t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2;

-----------------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=494.99..5977.97 rows=100000 width=12) (actual time=2.841..30.935 rows=90991 loops=1)
Merge Cond: (t1.col1 = t2.col2)
-> Index Only Scan using cons1 on tab1 t1 (cost=0.28..43.27 rows=1000 width=4) (actual time=0.007..0.150 rows=1000 loops=1)
Heap Fetches: 1000
-> Index Scan using ind22 on tab2 t2 (cost=0.29..4682.19 rows=100000 width=8) (actual time=0.029..20.008 rows=100000 loops=1)
Planning time: 0.289 ms
Execution time: 33.782 ms


explain analyze
/*+ MergeJoin(t1 t2) Leading(( t2 t1 ))*/
select t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2;

---------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=494.99..6475.47 rows=100000 width=12) (actual time=2.372..36.888 rows=90991 loops=1)
Merge Cond: (t2.col2 = t1.col1)
-> Index Scan using ind22 on tab2 t2 (cost=0.29..4682.19 rows=100000 width=8) (actual time=0.007..16.265 rows=100000 loops=1)
-> Materialize (cost=0.28..45.77 rows=1000 width=4) (actual time=0.006..3.940 rows=90991 loops=1)
-> Index Only Scan using cons1 on tab1 t1 (cost=0.28..43.27 rows=1000 width=4) (actual time=0.005..0.147 rows=1000 loops=1)
Heap Fetches: 1000
Planning time: 0.135 ms
Execution time: 39.841 ms


--union
explain analyze
select col1
from tab1
union
select col2
from tab2;

-----------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=2720.50..3730.50 rows=101000 width=4) (actual time=25.674..25.871 rows=1001 loops=1)
Group Key: tab1.col1
-> Append (cost=0.00..2468.00 rows=101000 width=4) (actual time=0.010..14.518 rows=101000 loops=1)
-> Seq Scan on tab1 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.009..0.090 rows=1000 loops=1)
-> Seq Scan on tab2 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.004..9.237 rows=100000 loops=1)
Planning time: 0.071 ms
Execution time: 26.356 ms

 

--union all
explain analyze
select col1
from tab1
union all
select col2
from tab2;

-----------------------------------------------------------------------------------------------------------------
Append (cost=0.00..1458.00 rows=101000 width=4) (actual time=0.007..13.966 rows=101000 loops=1)
-> Seq Scan on tab1 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.007..0.053 rows=1000 loops=1)
-> Seq Scan on tab2 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.003..8.318 rows=100000 loops=1)
Planning time: 0.053 ms
Execution time: 16.986 ms


--group by
explain analyze
select col2,count(*)
from tab2
group by col2;

-----------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1943.00..1943.10 rows=10 width=4) (actual time=20.016..20.016 rows=10 loops=1)
Group Key: col2
-> Seq Scan on tab2 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.005..6.191 rows=100000 loops=1)
Planning time: 0.253 ms
Execution time: 20.047 ms


--order by

explain analyze
select *
from tab2
order by col2 desc;

-----------------------------------------------------------------------------------------------------------------------------------
Index Scan Backward using ind22 on tab2 (cost=0.29..4682.19 rows=100000 width=8) (actual time=0.006..15.700 rows=100000 loops=1)
Planning time: 0.084 ms
Execution time: 18.697 ms


--サブクエリ(in)
explain analyze
select t1.col2
from tab1 t1
where t1.col1 in (select t2.col1 from tab2 t2);

-------------------------------------------------------------------------------------------------------------------------------------
Merge Semi Join (cost=0.61..88.84 rows=1000 width=4) (actual time=0.014..0.668 rows=1000 loops=1)
Merge Cond: (t1.col1 = t2.col1)
-> Index Scan using cons1 on tab1 t1 (cost=0.28..43.27 rows=1000 width=8) (actual time=0.004..0.148 rows=1000 loops=1)
-> Index Only Scan using cons2 on tab2 t2 (cost=0.29..3050.29 rows=100000 width=4) (actual time=0.006..0.189 rows=1000 loops=1)
Heap Fetches: 1000
Planning time: 0.164 ms
Execution time: 0.727 ms


--サブクエリ(not in)

explain analyze
select t1.col2
from tab1 t1
where t1.col1 not in (select t2.col1 from tab2 t2);

----------------------------------------------------------------------------------------------------------------------
Seq Scan on tab1 t1 (cost=1693.00..1710.50 rows=500 width=4) (actual time=30.869..30.869 rows=0 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 1000
SubPlan 1
-> Seq Scan on tab2 t2 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.005..8.747 rows=100000 loops=1)
Planning time: 0.050 ms
Execution time: 30.911 ms

--サブクエリ(exists)

explain analyze
select t1.col2
from tab1 t1
where exists (select 1 from tab2 t2 where t2.col1 = t1.col1);


-------------------------------------------------------------------------------------------------------------------------------------
Merge Semi Join (cost=0.61..88.84 rows=1000 width=4) (actual time=0.119..0.664 rows=1000 loops=1)
Merge Cond: (t1.col1 = t2.col1)
-> Index Scan using cons1 on tab1 t1 (cost=0.28..43.27 rows=1000 width=8) (actual time=0.111..0.233 rows=1000 loops=1)
-> Index Only Scan using cons2 on tab2 t2 (cost=0.29..3050.29 rows=100000 width=4) (actual time=0.006..0.166 rows=1000 loops=1)
Heap Fetches: 1000
Planning time: 0.175 ms
Execution time: 0.704 ms


--サブクエリ(not exists)

explain analyze
select t1.col2
from tab1 t1
where not exists (select 1 from tab2 t2 where t2.col1 = t1.col1);

-------------------------------------------------------------------------------------------------------------------------------------
Merge Anti Join (cost=0.57..88.84 rows=1 width=4) (actual time=0.483..0.483 rows=0 loops=1)
Merge Cond: (t1.col1 = t2.col1)
-> Index Scan using cons1 on tab1 t1 (cost=0.28..43.27 rows=1000 width=8) (actual time=0.004..0.129 rows=1000 loops=1)
-> Index Only Scan using cons2 on tab2 t2 (cost=0.29..3050.29 rows=100000 width=4) (actual time=0.007..0.170 rows=1000 loops=1)
Heap Fetches: 1000
Planning time: 0.191 ms
Execution time: 0.516 ms

 

(2014)


drop table tab1;
drop table tab2;
drop table tab3;
go

create table tab1(col1 int not null,col2 int);
create table tab2(col1 int not null,col2 int);
create table tab3(col1 int not null,col2 int);

create unique index ind11 on tab1(col1);
create index ind12 on tab1(col2);

create unique index ind21 on tab2(col1);
create index ind22 on tab2(col2);

alter table tab1 add constraint cons1 primary key(col1);
alter table tab2 add constraint cons2 primary key(col1);

DECLARE @counter int;
SET @counter = 1;

WHILE (@counter <= 1000)
BEGIN
INSERT INTO tab1 VALUES (@counter,@counter * 2);
SET @counter = @counter + 1;
END
go

DECLARE @counter int;
SET @counter = 1;

WHILE (@counter <= 100000)
BEGIN
INSERT INTO tab2 VALUES (@counter,@counter%10);
SET @counter = @counter + 1;
END
go

DECLARE @counter int;
SET @counter = 1;

WHILE (@counter <= 100000)
BEGIN
INSERT INTO tab3 VALUES (@counter,@counter);
SET @counter = @counter + 1;
END
go

UPDATE STATISTICS tab1;
UPDATE STATISTICS tab2;
UPDATE STATISTICS tab3;

--1テーブル(フルスキャン)
set showplan_text on
go
select * from tab3;

----------------------------------------------
|--Table Scan(OBJECT:([test].[dbo].[tab3]))

 

--1テーブル(インデックスユニーク)
set showplan_text on
go
select * from tab1
where col2 = 20;

--------------------------------------------------------------------------------------------------------------------------------------
|--Index Seek(OBJECT:([test].[dbo].[tab1].[ind12]), SEEK:([test].[dbo].[tab1].[col2]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

--1テーブル(インデックスレンジ)

set showplan_text on
go
select * from tab1
where col2 between 222 and 444;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Index Seek(OBJECT:([test].[dbo].[tab1].[ind12]), SEEK:([test].[dbo].[tab1].[col2] >= CONVERT_IMPLICIT(int,[@1],0) AND [test].[dbo].[tab1].[col2] <= CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)


--1テーブル(高速全索引スキャン)

set showplan_text on
go
select * from tab1;

------------------------------------------------------
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12]))


--2テーブル内部(NL)

set showplan_text on
go
select t1.col1,t2.*
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1;

-----------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[col1], [Expr1002]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind11] AS [t1]))
|--Clustered Index Seek(OBJECT:([test].[dbo].[tab2].[cons2] AS [t2]), SEEK:([t2].[col1]=[test].[dbo].[tab1].[col1] as [t1].[col1]) ORDERED FORWARD)


set showplan_text on
go
select t1.col1,t2.*
from tab2 t2 inner join tab1 t1
on t1.col1 = t2.col1
OPTION (LOOP JOIN,FORCE ORDER);

-------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[col1]))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]))
|--Index Seek(OBJECT:([test].[dbo].[tab1].[ind11] AS [t1]), SEEK:([t1].[col1]=[test].[dbo].[tab2].[col1] as [t2].[col1]) ORDERED FORWARD)


--2テーブル内部(HJ)

set showplan_text on
go
select t1.col1,t2.*
from tab2 t2 inner join tab1 t1
on t1.col1 = t2.col1
OPTION (HASH JOIN);

-------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([t1].[col1])=([t2].[col1]))
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]))


set showplan_text on
go
select t1.col1,t2.*
from tab2 t2 inner join tab1 t1
on t1.col1 = t2.col1
OPTION (HASH JOIN,FORCE ORDER);

-------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([t2].[col1])=([t1].[col1]))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]))
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]))

 

--2テーブル内部(MJ)

set showplan_text on
go
select t1.col1,t2.*
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
OPTION (MERGE JOIN,FORCE ORDER);

---------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Merge Join(Inner Join, MERGE:([t1].[col1])=([t2].[col1]), RESIDUAL:([test].[dbo].[tab2].[col1] as [t2].[col1]=[test].[dbo].[tab1].[col1] as [t1].[col1]))
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind11] AS [t1]), ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].[tab2].[cons2] AS [t2]), ORDERED FORWARD)

 


set showplan_text on
go
select t1.col1,t2.*
from tab2 t2 inner join tab1 t1
on t1.col1 = t2.col1
OPTION (FORCE ORDER);

---------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Merge Join(Inner Join, MERGE:([t2].[col1])=([t1].[col1]), RESIDUAL:([test].[dbo].[tab2].[col1] as [t2].[col1]=[test].[dbo].[tab1].[col1] as [t1].[col1]))
|--Clustered Index Scan(OBJECT:([test].[dbo].[tab2].[cons2] AS [t2]), ORDERED FORWARD)
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind11] AS [t1]), ORDERED FORWARD)

 

--2テーブル外部(NL)

set showplan_text on
go
select t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2
OPTION (LOOP JOIN);

-------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[col1], [Expr1002]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]))
|--Index Seek(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]), SEEK:([t2].[col2]=[test].[dbo].[tab1].[col1] as [t1].[col1]) ORDERED FORWARD)

set showplan_text on
go
select t1.col1,t2.col1,t2.col2
from tab2 t2 left join tab1 t1
on t1.col1 = t2.col2
OPTION (LOOP JOIN);

-------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([t2].[col2]))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]))
|--Index Seek(OBJECT:([test].[dbo].[tab1].[ind11] AS [t1]), SEEK:([t1].[col1]=[test].[dbo].[tab2].[col2] as [t2].[col2]) ORDERED FORWARD)

 


--2テーブル外部(HJ)
set showplan_text on
go
select t1.col1,t2.col1,t2.col2
from tab1 t1 left join tab2 t2
on t1.col1 = t2.col2
OPTION (HASH JOIN);

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Hash Match(Left Outer Join, HASH:([t1].[col1])=([t2].[col2]), RESIDUAL:([test].[dbo].[tab1].[col1] as [t1].[col1]=[test].[dbo].[tab2].[col2] as [t2].[col2]))
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]))

set showplan_text on
go
select t1.col1,t2.col1,t2.col2
from tab2 t2 left join tab1 t1
on t1.col1 = t2.col2
OPTION (HASH JOIN,FORCE ORDER);

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Hash Match(Left Outer Join, HASH:([t2].[col2])=([t1].[col1]), RESIDUAL:([test].[dbo].[tab1].[col1] as [t1].[col1]=[test].[dbo].[tab2].[col2] as [t2].[col2]))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]))
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]))


--2テーブル外部(MJ)

set showplan_text on
go
select t1.col1,t2.col1,t2.col2
from tab2 t2 left join tab1 t1
on t1.col1 = t2.col2;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Merge Join(Right Outer Join, MERGE:([t1].[col1])=([t2].[col2]), RESIDUAL:([test].[dbo].[tab1].[col1] as [t1].[col1]=[test].[dbo].[tab2].[col2] as [t2].[col2]))
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind11] AS [t1]), ORDERED FORWARD)
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]), ORDERED FORWARD)


set showplan_text on
go
select t1.col1,t2.col1,t2.col2
from tab2 t2 left join tab1 t1
on t1.col1 = t2.col2
OPTION (FORCE ORDER);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([t2].[col2])=([t1].[col1]), RESIDUAL:([test].[dbo].[tab1].[col1] as [t1].[col1]=[test].[dbo].[tab2].[col2] as [t2].[col2]))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]), ORDERED FORWARD)
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind11] AS [t1]), ORDERED FORWARD)


--union
set showplan_text on
go
select col1
from tab1
union
select col2
from tab2;

---------------------------------------------------------------------------------
|--Merge Join(Union)
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind11]), ORDERED FORWARD)
|--Stream Aggregate(GROUP BY:([test].[dbo].[tab2].[col2]))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22]), ORDERED FORWARD)
--union all
set showplan_text on
go
select col1
from tab1
union all
select col2
from tab2;

-----------------------------------------------------------
|--Concatenation
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12]))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22]))

--group by
set showplan_text on
go
select col2,count(*)
from tab2
group by col2;

-----------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(GROUP BY:([test].[dbo].[tab2].[col2]) DEFINE:([Expr1005]=Count(*)))
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22]), ORDERED FORWARD)


--order by

set showplan_text on
go
select *
from tab2
order by col2 desc;

------------------------------------------------------------------------
|--Index Scan(OBJECT:([test].[dbo].[tab2].[ind22]), ORDERED BACKWARD)


--サブクエリ(in)
set showplan_text on
go
select t1.col2
from tab1 t1
where t1.col1 in (select t2.col1 from tab2 t2);

-------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[col1], [Expr1002]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]))
|--Index Seek(OBJECT:([test].[dbo].[tab2].[ind21] AS [t2]), SEEK:([t2].[col1]=[test].[dbo].[tab1].[col1] as [t1].[col1]) ORDERED FORWARD)


--サブクエリ(not in)
set showplan_text on
go
select t1.col2
from tab1 t1
where t1.col1 not in (select t2.col1 from tab2 t2);

-------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([t1].[col1], [Expr1002]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]))
|--Index Seek(OBJECT:([test].[dbo].[tab2].[ind21] AS [t2]), SEEK:([t2].[col1]=[test].[dbo].[tab1].[col1] as [t1].[col1]) ORDERED FORWARD)


--サブクエリ(exists)

set showplan_text on
go
select t1.col2
from tab1 t1
where exists (select 1 from tab2 t2 where t2.col1 = t1.col1);

-------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[col1], [Expr1003]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]))
|--Index Seek(OBJECT:([test].[dbo].[tab2].[ind21] AS [t2]), SEEK:([t2].[col1]=[test].[dbo].[tab1].[col1] as [t1].[col1]) ORDERED FORWARD)


--サブクエリ(not exists)


set showplan_text on
go
select t1.col2
from tab1 t1
where not exists (select 1 from tab2 t2 where t2.col1 = t1.col1);


-------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([t1].[col1], [Expr1003]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]))
|--Index Seek(OBJECT:([test].[dbo].[tab2].[ind21] AS [t2]), SEEK:([t2].[col1]=[test].[dbo].[tab1].[col1] as [t1].[col1]) ORDERED FORWARD)