(5.6)
--確認事項
(絞り込み後)件数が少ないテーブルが外部表となること
--データ準備
drop table tab1;
drop table tab2;
create table tab1(col11 int,col12 int,col13 int);
create table tab2(col21 int,col22 int,col23 int);
alter table tab1 add constraint cons1 primary key(col11);
alter table tab2 add constraint cons2 primary key(col21,col22);
create index ind12 on tab1(col13);
create index ind22 on tab2(col23);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 100000 do
insert into tab1 values(i, i, i);
insert into tab2 values(i, i, i);
set i = i + 1;
end while;
end
//
delimiter ;
call proc1();
analyze table tab1;
analyze table tab2;
--実行計画確認
explain
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
where t1.col13 between 100 and 120
and t2.col23 = 100
;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| 1 | SIMPLE | t2 | ref | PRIMARY,ind22 | ind22 | 5 | const | 1 | Using index |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY,ind12 | PRIMARY | 4 | test.t2.col21 | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
explain
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
where t1.col13 = 100
and t2.col23 between 100 and 120
;
+----+-------------+-------+------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+---------------+------+-------------+
| 1 | SIMPLE | t1 | ref | PRIMARY,ind12 | ind12 | 5 | const | 1 | NULL |
| 1 | SIMPLE | t2 | ref | PRIMARY,ind22 | PRIMARY | 4 | test.t1.col11 | 1 | Using where |
+----+-------------+-------+------+---------------+---------+---------+---------------+------+-------------+
(12cR1)
--確認事項
(絞り込み後)件数が少ないテーブルが外部表となること
--データ準備
drop table tab1;
drop table tab2;
create table tab1(col11 int,col12 int,col13 int);
create table tab2(col21 int,col22 int,col23 int);
create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21,col22);
alter table tab1 add constraint cons1 primary key(col11) using index ind11;
alter table tab2 add constraint cons2 primary key(col21,col22) using index ind21;
create index ind12 on tab1(col13);
create index ind22 on tab2(col23);
declare
begin
for i in 1..100000 loop
insert into tab1 values(i,i,i);
insert into tab2 values(i,i,i);
commit;
end loop;
end;
/
execute dbms_stats.gather_table_stats('TEST','TAB1');
execute dbms_stats.gather_table_stats('TEST','TAB2');
--実行計画確認
explain plan for
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
where t1.col13 between 100 and 120
and t2.col23 = 100
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 30 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 30 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB2 | 1 | 15 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND22 | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | IND11 | 1 | | 0 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | TAB1 | 1 | 15 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
explain plan for
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
where t1.col13 = 100
and t2.col23 between 100 and 120
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 30 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 30 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 15 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND12 | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND21 | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 15 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
(9.4)
--確認事項
(絞り込み後)件数が少ないテーブルが外部表となること
--データ準備
drop table tab1;
drop table tab2;
create table tab1(col11 int,col12 int,col13 int);
create table tab2(col21 int,col22 int,col23 int);
create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21,col22);
alter table tab1 add constraint cons1 primary key using index ind11;
alter table tab2 add constraint cons2 primary key using index ind21;
create index ind12 on tab1(col13);
create index ind22 on tab2(col23);
insert into tab1 select generate_series(1,100000),generate_series(1,100000),generate_series(1,100000);
insert into tab2 select generate_series(1,100000),generate_series(1,100000),generate_series(1,100000);
analyze tab1;
analyze tab2;
--実行計画確認
explain
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
where t1.col13 between 100 and 120
and t2.col23 = 100
;
----------------------------------------------------------------------------
Nested Loop (cost=0.58..16.64 rows=1 width=24)
-> Index Scan using ind22 on tab2 t2 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (col23 = 100)
-> Index Scan using cons1 on tab1 t1 (cost=0.29..8.32 rows=1 width=12)
Index Cond: (col11 = t2.col21)
Filter: *1
explain
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
where t1.col13 = 100
and t2.col23 between 100 and 120
;
----------------------------------------------------------------------------
Nested Loop (cost=0.58..16.64 rows=1 width=24)
-> Index Scan using ind12 on tab1 t1 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (col13 = 100)
-> Index Scan using cons2 on tab2 t2 (cost=0.29..8.32 rows=1 width=12)
Index Cond: (col21 = t1.col11)
Filter: *2
(2014)
--確認事項
(絞り込み後)件数が少ないテーブルが外部表となること
--データ準備
drop table tab1;
drop table tab2;
go
create table tab1(col11 int not null,col12 int,col13 int);
create table tab2(col21 int not null,col22 int not null, col23 int);
create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21,col22);
alter table tab1 add constraint cons1 primary key(col11);
alter table tab2 add constraint cons2 primary key(col21,col22);
create index ind12 on tab1(col13);
create index ind22 on tab2(col23);
DECLARE @counter int;
SET @counter = 1;
WHILE (@counter <= 100000)
BEGIN
INSERT INTO tab1 VALUES (@counter,@counter,@counter);
INSERT INTO tab2 VALUES (@counter,@counter,@counter);
SET @counter = @counter + 1;
END
UPDATE STATISTICS tab1;
UPDATE STATISTICS tab2;
--実行計画確認
set showplan_text on
go
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
where t1.col13 between 100 and 120
and t2.col23 = 100
;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[col21]))
|--Index Seek(OBJECT:([test].[dbo].[tab2].[ind22] AS [t2]), SEEK:([t2].[col23]=(100)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([test].[dbo].[tab1].[cons1] AS [t1]), SEEK:([t1].[col11]=[test].[dbo].[tab2].[col21] as [t2].[col21]), WHERE:([test].[dbo].[tab1].[col13] as [t1].[col13]>=(100) AND [test].[dbo].[tab1].[col13] as [t1].[col13]<=(120))
set showplan_text on
go
select t1.*,t2.*
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
where t1.col13 = 100
and t2.col23 between 100 and 120
;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[col11]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[col11]))
| |--Index Seek(OBJECT:([test].[dbo].[tab1].[ind12] AS [t1]), SEEK:([t1].[col13]=(100)) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([test].[dbo].[tab1].[cons1] AS [t1]), SEEK:([t1].[col11]=[test].[dbo].[tab1].[col11] as [t1].[col11]) LOOKUP ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([test].[dbo].[tab2].[cons2] AS [t2]), SEEK:([t2].[col21]=[test].[dbo].[tab1].[col11] as [t1].[col11]), WHERE:([test].[dbo].[tab2].[col23] as [t2].[col23]>=(100) AND [test].[dbo].[tab2].[col23] as [t2].[col23]<=(120))
