-- 1. テストデータ作成
drop table tab2 ;
drop table tab1 ;
create table tab1(
col1 bigint not null
,col2 varchar(100)
);
create table tab2(
col1 bigint not null
,col2 bigint not null
,col3 varchar(100)
);
alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1,col2);
alter table tab2 add constraint tab2fk foreign key(col1) references tab1(col1);
show create table tab1;
show create table tab2;
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i bigint;
declare j bigint;
start transaction;
set i = 1;
while i <= 100 do
insert into tab1 values(i,substring(md5(rand()), 1, 30) );
set j = 1;
while j <= 10000 do
insert into tab2 values(i,j,substring(md5(rand()), 1, 30) );
set j = j + 1;
end while;
set i = i + 1;
end while;
commit;
end
//
delimiter ;
call proc1();
select count(*) from tab1;
select count(*) from tab2;
select * from tab1 order by rand() limit 10;
select * from tab2 order by rand() limit 10;
-- 2. 実行計画比較
explain format=tree
select
t1.col1 col1
, t1.col2 col2
, max(t2.col2) col2max
, min(t2.col2) col2min
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
group by t1.col1,t1.col2
order by t1.col1,t1.col2
;
(3.56 sec)
explain format=tree
select
t1.col1 col1
, t1.col2 col2
, t3.col2max
, t3.col2min
from tab1 t1 ,lateral
(select
max(t2.col2) col2max
, min(t2.col2) col2min
from tab2 t2
where t1.col1 = t2.col1
) t3
order by t1.col1,t1.col2
;
(0.27 sec)
explain format=tree
select
t1.col1 col1
, t1.col2 col2
, t3.col2max
, t3.col2min
from tab1 t1 cross apply
(select
max(t2.col2) col2max
, min(t2.col2) col2min
from tab2 t2
where t1.col1 = t2.col1
) t3
order by t1.col1,t1.col2
;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'apply
explain format=tree
select
t1.col1 col1
, t1.col2 col2
,(select max(t2.col2) from tab2 t2 where t1.col1 = t2.col1) col2max
,(select min(t2.col2) from tab2 t2 where t1.col1 = t2.col1) col2min
from tab1 t1
order by t1.col1,t1.col2
;
(0.48 sec)
(23c)
https://blogs.oracle.com/otnjp/post/tsushima-hakushi-37
-- 1. テストデータ作成
drop table tab1 cascade constraint;
drop table tab2 cascade constraint;
create table tab1(
col1 int not null
,col2 varchar2(100)
);
create table tab2(
col1 int not null
,col2 int not null
,col3 varchar2(100)
);
alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1,col2);
alter table tab2 add constraint tab2fk foreign key(col1) references tab1(col1);
set long 4000000
set longchunksize 4000000
exec dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',true);
select dbms_metadata.get_ddl('TABLE','TAB1',user) from dual;
select dbms_metadata.get_ddl('TABLE','TAB2',user) from dual;
declare
begin
for i in 1..100 loop
insert into tab1 values (i,substr(standard_hash(dbms_random.value(), 'MD5'),1,30) );
for j in 1..10000 loop
insert into tab2 values (i,j,substr(standard_hash(dbms_random.value(), 'MD5'),1,30) );
end loop;
end loop;
end;
/
commit;
select count(*) from tab1;
select count(*) from tab2;
select * from tab1 order by dbms_random.value() fetch first 10 rows only;
select * from tab2 order by dbms_random.value() fetch first 10 rows only;
-- 2. 実行計画比較
set time on
set timing on
explain plan for
select
t1.col1 col1
, t1.col2 col2
, max(t2.col2) col2max
, min(t2.col2) col2min
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
group by t1.col1,t1.col2
order by t1.col1,t1.col2
;
select * from table(dbms_xplan.display(format=>'ALL') );
経過: 00:00:00.21
explain plan for
select
t1.col1 col1
, t1.col2 col2
, t3.col2max
, t3.col2min
from tab1 t1 ,lateral
(select
max(t2.col2) col2max
, min(t2.col2) col2min
from tab2 t2
where t1.col1 = t2.col1
) t3
order by t1.col1,t1.col2
;
select * from table(dbms_xplan.display(format=>'ALL') );
経過: 00:00:00.11
explain plan for
select
t1.col1 col1
, t1.col2 col2
, t3.col2max
, t3.col2min
from tab1 t1 cross apply
(select
max(t2.col2) col2max
, min(t2.col2) col2min
from tab2 t2
where t1.col1 = t2.col1
) t3
order by t1.col1,t1.col2
;
select * from table(dbms_xplan.display(format=>'ALL') );
経過: 00:00:00.11
explain plan for
select
t1.col1 col1
, t1.col2 col2
,(select max(t2.col2) from tab2 t2 where t1.col1 = t2.col1) col2max
,(select min(t2.col2) from tab2 t2 where t1.col1 = t2.col1) col2min
from tab1 t1
order by t1.col1,t1.col2
;
select * from table(dbms_xplan.display(format=>'ALL') );
経過: 00:00:00.20
(16)
https://lets.postgresql.jp/documents/technical/lateral/1
-- 1. テストデータ作成
drop table tab1 CASCADE;
drop table tab2 CASCADE;
create table tab1(
col1 bigint not null
,col2 varchar(100)
);
create table tab2(
col1 bigint not null
,col2 bigint not null
,col3 varchar(100)
);
alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1,col2);
alter table tab2 add constraint tab2fk foreign key(col1) references tab1(col1);
\d tab1
\d tab2
start transaction;
do $$
declare
begin
for i in 1..100 loop
insert into tab1 values(i,substring(md5(random()::text), 1, 30) );
for j in 1..10000 loop
insert into tab2 values(i,j,substring(md5(random()::text), 1, 30) );
end loop;
end loop;
end
$$
;
commit;
select count(*) from tab1;
select count(*) from tab2;
\pset pager 0
select * from tab1 order by random() limit 10;
select * from tab2 order by random() limit 10;
-- 2. 実行計画比較
explain analyze
select
t1.col1 col1
, t1.col2 col2
, max(t2.col2) col2max
, min(t2.col2) col2min
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
group by t1.col1,t1.col2
order by t1.col1,t1.col2
;
Execution Time: 388.292 ms
explain analyze
select
t1.col1 col1
, t1.col2 col2
, t3.col2max
, t3.col2min
from tab1 t1 ,lateral
(select
max(t2.col2) col2max
, min(t2.col2) col2min
from tab2 t2
where t1.col1 = t2.col1
) t3
order by t1.col1,t1.col2
;
Execution Time: 1.346 ms
explain analyze
select
t1.col1 col1
, t1.col2 col2
, t3.col2max
, t3.col2min
from tab1 t1 cross apply
(select
max(t2.col2) col2max
, min(t2.col2) col2min
from tab2 t2
where t1.col1 = t2.col1
) t3
order by t1.col1,t1.col2
;
ERROR: "apply"またはその近辺で構文エラー
explain analyze
select
t1.col1 col1
, t1.col2 col2
,(select max(t2.col2) from tab2 t2 where t1.col1 = t2.col1) col2max
,(select min(t2.col2) from tab2 t2 where t1.col1 = t2.col1) col2min
from tab1 t1
order by t1.col1,t1.col2
;
Execution Time: 1.371 ms
(2022)
https://interprism.hatenablog.com/entry/cross-apply-outer-apply-lateral
-- 1. テストデータ作成
drop table tab2 ;
drop table tab1 ;
create table tab1(
col1 bigint not null
,col2 varchar(100)
);
create table tab2(
col1 bigint not null
,col2 bigint not null
,col3 varchar(100)
);
alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1,col2);
alter table tab2 add constraint tab2fk foreign key(col1) references tab1(col1);
sp_help tab1;
go
sp_help tab2;
go
set nocount on
declare @i int;
declare @j int;
begin transaction;
set @i = 1;
while @i <= 100
begin
insert into tab1 values(@i,substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30) );
set @j = 1;
while @j <= 10000
begin
insert into tab2 values(@i,@j,substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30) );
set @j = @j + 1;
end
set @i = @i + 1;
end
commit;
select count(*) from tab1;
select count(*) from tab2;
select top(10) * from tab1 order by newid();
select top(10) * from tab2 order by newid();
-- 2. 実行計画比較
set showplan_all on
go
set showplan_all off
go
set statistics time on
go
select
t1.col1 col1
, t1.col2 col2
, max(t2.col2) col2max
, min(t2.col2) col2min
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
group by t1.col1,t1.col2
order by t1.col1,t1.col2
;
経過時間 = 223 ミリ秒
select
t1.col1 col1
, t1.col2 col2
, t3.col2max
, t3.col2min
from tab1 t1 ,lateral
(select
max(t2.col2) col2max
, min(t2.col2) col2min
from tab2 t2
where t1.col1 = t2.col1
) t3
order by t1.col1,t1.col2
;
メッセージ 156、レベル 15、状態 1、行 7
キーワード 'select' 付近に不適切な構文があります。
メッセージ 102、レベル 15、状態 1、行 12
')' 付近に不適切な構文があります。
select
t1.col1 col1
, t1.col2 col2
, t3.col2max
, t3.col2min
from tab1 t1 cross apply
(select
max(t2.col2) col2max
, min(t2.col2) col2min
from tab2 t2
where t1.col1 = t2.col1
) t3
order by t1.col1,t1.col2
;
経過時間 = 1 ミリ秒
select
t1.col1 col1
, t1.col2 col2
,(select max(t2.col2) from tab2 t2 where t1.col1 = t2.col1) col2max
,(select min(t2.col2) from tab2 t2 where t1.col1 = t2.col1) col2min
from tab1 t1
order by t1.col1,t1.col2
;
経過時間 = 0 ミリ秒
