"오라클 lateral 조인"의 두 판 사이의 차이
DB CAFE
(→LATERAL) |
|||
10번째 줄: | 10번째 줄: | ||
); | ); | ||
</SOURCE> | </SOURCE> | ||
+ | |||
+ | Oracle Database has used lateral internally for a while. Specifically this was to transform ANSI outer joins to Oracle outer joins where a direct translation isn't possible. For example, if your outer join included an OR: | ||
+ | |||
+ | select t1.x, t2.x from t1 | ||
+ | left join t2 | ||
+ | on t1.x = t2.x or t1.y = t2.x | ||
+ | |||
+ | |||
+ | There isn't a way to convert this to Oracle syntax. So instead it became something like: | ||
+ | <SOURCE LANG=SQL> | ||
+ | select T1.X X,subq.X X | ||
+ | from T1, lateral ( | ||
+ | (select T2.X X from T2 | ||
+ | where T1.X=T2.X or T1.Y=T2.X | ||
+ | ) | ||
+ | ) (+) subq; | ||
+ | </SOURCE> | ||
+ | |||
+ | |||
+ | You can read more about this at: | ||
+ | |||
+ | http://optimizermagic.blogspot.co.uk/2007/12/outerjoins-in-oracle.html | ||
+ | |||
+ | Lateral is also part of the ANSI standard. So from a compliance point of view it makes sense to expose it. It is also useful for supporting CROSS APPLY which we introduced in 12c. | ||
+ | |||
+ | It's also useful when working with non-mergeable views. For example, in the following query Oracle fully processes the subquery before joining to t1: | ||
+ | <SOURCE LANG=SQL> | ||
+ | select /*+ gather_plan_statistics */* from t1, ( | ||
+ | select * from t2 | ||
+ | minus | ||
+ | select * from t2 | ||
+ | where y > 50 | ||
+ | ) t2 | ||
+ | where t1.x = t2.x; | ||
+ | |||
+ | select * from table( | ||
+ | dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE +ROWSTATS LAST') | ||
+ | ); | ||
+ | </SOURCE> | ||
+ | |||
+ | PLAN_TABLE_OUTPUT | ||
+ | ------------------------------------------------------------------- | ||
+ | |||
+ | EXPLAINED SQL STATEMENT: | ||
+ | ------------------------ | ||
+ | select /*+ gather_plan_statistics */* from t1, ( select * from t2 | ||
+ | minus select * from t2 where y > 50 ) t2 where t1.x = t2.x | ||
+ | |||
+ | Plan hash value: 187374969 | ||
+ | |||
+ | ----------------------------------------------------------------- | ||
+ | | Id | Operation | Name | Starts | E-Rows | A-Rows | | ||
+ | ----------------------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | | 1 | | 10 | | ||
+ | |* 1 | HASH JOIN | | 1 | 100 | 10 | | ||
+ | | 2 | TABLE ACCESS FULL | T1 | 1 | 2 | 2 | | ||
+ | | 3 | VIEW | | 1 | 100 | 50 | | ||
+ | | 4 | MINUS | | 1 | | 50 | | ||
+ | | 5 | SORT UNIQUE | | 1 | 100 | 100 | | ||
+ | | 6 | TABLE ACCESS FULL| T2 | 1 | 100 | 100 | | ||
+ | | 7 | SORT UNIQUE | | 1 | 51 | 50 | | ||
+ | |* 8 | TABLE ACCESS FULL| T2 | 1 | 51 | 50 | | ||
+ | ----------------------------------------------------------------- | ||
+ | |||
+ | Predicate Information (identified by operation id): | ||
+ | --------------------------------------------------- | ||
+ | |||
+ | 1 - access("T1"."X"="T2"."X") | ||
+ | 8 - filter("Y">50) | ||
+ | |||
+ | |||
+ | In some cases there can be a big performance advantage to doing the join inside the subquery. Using the lateral operator enables this: | ||
+ | |||
+ | select /*+ gather_plan_statistics */* from t1, | ||
+ | lateral( | ||
+ | select * from t2 | ||
+ | where t1.x = t2.x | ||
+ | minus | ||
+ | select * from t2 | ||
+ | where t1.x = t2.x | ||
+ | and y > 50 | ||
+ | ) t2; | ||
+ | |||
+ | select * from table( | ||
+ | dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE +ROWSTATS LAST') | ||
+ | ); | ||
+ | |||
+ | PLAN_TABLE_OUTPUT | ||
+ | ---------------------------------------------------------------------------------------------- | ||
+ | |||
+ | EXPLAINED SQL STATEMENT: | ||
+ | ------------------------ | ||
+ | select /*+ gather_plan_statistics */* from t1, lateral( select * from | ||
+ | t2 where t1.x = t2.x minus select * from t2 where t1.x = t2.x | ||
+ | and y > 50 ) t2 | ||
+ | |||
+ | Plan hash value: 1689167383 | ||
+ | |||
+ | ---------------------------------------------------------------------------------------------- | ||
+ | | Id | Operation | Name | Starts | E-Rows | A-Rows | | ||
+ | ---------------------------------------------------------------------------------------------- | ||
+ | | 0 | SELECT STATEMENT | | 1 | | 10 | | ||
+ | | 1 | NESTED LOOPS | | 1 | 20 | 10 | | ||
+ | | 2 | TABLE ACCESS FULL | T1 | 1 | 2 | 2 | | ||
+ | | 3 | VIEW | VW_LAT_A18161FF | 2 | 10 | 10 | | ||
+ | | 4 | MINUS | | 2 | | 10 | | ||
+ | | 5 | SORT UNIQUE | | 2 | 10 | 20 | | ||
+ | | 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 2 | 10 | 20 | | ||
+ | |* 7 | INDEX RANGE SCAN | I | 2 | 10 | 20 | | ||
+ | | 8 | SORT UNIQUE | | 2 | 5 | 10 | | ||
+ | |* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 2 | 5 | 10 | | ||
+ | |* 10 | INDEX RANGE SCAN | I | 2 | 10 | 20 | | ||
+ | ---------------------------------------------------------------------------------------------- | ||
+ | |||
+ | Predicate Information (identified by operation id): | ||
+ | --------------------------------------------------- | ||
+ | |||
+ | 7 - access("T1"."X"="T2"."X") | ||
+ | 9 - filter("Y">50) | ||
+ | 10 - access("T1"."X"="T2"."X") | ||
+ | |||
+ | |||
+ | You can see that Oracle switches from two full scans of T2 to two index lookups of it. Possibly a huge saving! | ||
+ | |||
+ | And of course, like with cross apply, it may just make the query easier to write and/or understand. For example, you can take this rather convoluted way of converting a comma separated string to rows: | ||
+ | |||
+ | with strings as ( | ||
+ | select 'a,b,c' s from dual | ||
+ | ) | ||
+ | select trim(regexp_substr(s.s, '[^,]+', 1, rws.column_value)) as v | ||
+ | from | ||
+ | strings s, | ||
+ | table(cast(multiset( | ||
+ | select level l from dual | ||
+ | connect by level <= length (regexp_replace(s.s, '[^,]+')) + 1 | ||
+ | ) as sys.OdciNumberList ) | ||
+ | ) rws; | ||
+ | |||
+ | V | ||
+ | a | ||
+ | b | ||
+ | c | ||
+ | |||
+ | |||
+ | Lateral removes the need for all the table cast nonsense. So you can rewrite it like: | ||
+ | |||
+ | with strings as ( | ||
+ | select 'a,b,c' s from dual | ||
+ | ) | ||
+ | select trim(regexp_substr(s.s, '[^,]+', 1, rws.l)) as v | ||
+ | from | ||
+ | strings s, | ||
+ | lateral( | ||
+ | select level l from dual | ||
+ | connect by level <= length (regexp_replace(s.s, '[^,]+')) + 1 | ||
+ | ) rws; | ||
+ | |||
+ | V | ||
+ | a | ||
+ | b | ||
+ | c | ||
+ | |||
+ | |||
+ | Though you might want to check out another method for doing this anyway: | ||
+ | |||
+ | https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/ | ||
+ | |||
+ | HT to Wayne Smith in development for helping with this! | ||
+ | |||
+ | Scripts to create the tables for the above example: | ||
+ | |||
+ | create table t1 as | ||
+ | select rownum x from dual connect by level <= 2; | ||
+ | create table t2 as | ||
+ | select mod(rownum, 10) x, rownum y from dual connect by level <= 100; | ||
+ | |||
+ | create index i on t2(x); | ||
+ | exec dbms_stats.gather_table_stats(user, 't1'); | ||
+ | exec dbms_stats.gather_table_stats(user, 't2'); |
2019년 8월 16일 (금) 14:11 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
LATERAL[편집]
SELECT department_name, employee_name
FROM departments d
, LATERAL (SELECT employee_name
FROM employees e
WHERE e.department_id = d.department_id
);
Oracle Database has used lateral internally for a while. Specifically this was to transform ANSI outer joins to Oracle outer joins where a direct translation isn't possible. For example, if your outer join included an OR:
select t1.x, t2.x from t1 left join t2 on t1.x = t2.x or t1.y = t2.x
There isn't a way to convert this to Oracle syntax. So instead it became something like:
select T1.X X,subq.X X
from T1, lateral (
(select T2.X X from T2
where T1.X=T2.X or T1.Y=T2.X
)
) (+) subq;
You can read more about this at:
http://optimizermagic.blogspot.co.uk/2007/12/outerjoins-in-oracle.html
Lateral is also part of the ANSI standard. So from a compliance point of view it makes sense to expose it. It is also useful for supporting CROSS APPLY which we introduced in 12c.
It's also useful when working with non-mergeable views. For example, in the following query Oracle fully processes the subquery before joining to t1:
select /*+ gather_plan_statistics */* from t1, (
select * from t2
minus
select * from t2
where y > 50
) t2
where t1.x = t2.x;
select * from table(
dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE +ROWSTATS LAST')
);
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
select /*+ gather_plan_statistics */* from t1, ( select * from t2 minus select * from t2 where y > 50 ) t2 where t1.x = t2.x
Plan hash value: 187374969
| Id | Operation | Name | Starts | E-Rows | A-Rows |
| 0 | SELECT STATEMENT | | 1 | | 10 | |* 1 | HASH JOIN | | 1 | 100 | 10 | | 2 | TABLE ACCESS FULL | T1 | 1 | 2 | 2 | | 3 | VIEW | | 1 | 100 | 50 | | 4 | MINUS | | 1 | | 50 | | 5 | SORT UNIQUE | | 1 | 100 | 100 | | 6 | TABLE ACCESS FULL| T2 | 1 | 100 | 100 | | 7 | SORT UNIQUE | | 1 | 51 | 50 | |* 8 | TABLE ACCESS FULL| T2 | 1 | 51 | 50 |
Predicate Information (identified by operation id):
1 - access("T1"."X"="T2"."X") 8 - filter("Y">50)
In some cases there can be a big performance advantage to doing the join inside the subquery. Using the lateral operator enables this:
select /*+ gather_plan_statistics */* from t1, lateral(
select * from t2 where t1.x = t2.x minus select * from t2 where t1.x = t2.x and y > 50
) t2;
select * from table(
dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE +ROWSTATS LAST')
);
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
select /*+ gather_plan_statistics */* from t1, lateral( select * from t2 where t1.x = t2.x minus select * from t2 where t1.x = t2.x
and y > 50 ) t2
Plan hash value: 1689167383
| Id | Operation | Name | Starts | E-Rows | A-Rows |
| 0 | SELECT STATEMENT | | 1 | | 10 | | 1 | NESTED LOOPS | | 1 | 20 | 10 | | 2 | TABLE ACCESS FULL | T1 | 1 | 2 | 2 | | 3 | VIEW | VW_LAT_A18161FF | 2 | 10 | 10 | | 4 | MINUS | | 2 | | 10 | | 5 | SORT UNIQUE | | 2 | 10 | 20 | | 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 2 | 10 | 20 | |* 7 | INDEX RANGE SCAN | I | 2 | 10 | 20 | | 8 | SORT UNIQUE | | 2 | 5 | 10 | |* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 2 | 5 | 10 | |* 10 | INDEX RANGE SCAN | I | 2 | 10 | 20 |
Predicate Information (identified by operation id):
7 - access("T1"."X"="T2"."X") 9 - filter("Y">50) 10 - access("T1"."X"="T2"."X")
You can see that Oracle switches from two full scans of T2 to two index lookups of it. Possibly a huge saving!
And of course, like with cross apply, it may just make the query easier to write and/or understand. For example, you can take this rather convoluted way of converting a comma separated string to rows:
with strings as (
select 'a,b,c' s from dual
) select trim(regexp_substr(s.s, '[^,]+', 1, rws.column_value)) as v from
strings s, table(cast(multiset( select level l from dual connect by level <= length (regexp_replace(s.s, '[^,]+')) + 1 ) as sys.OdciNumberList ) ) rws;
V a b c
Lateral removes the need for all the table cast nonsense. So you can rewrite it like:
with strings as (
select 'a,b,c' s from dual
) select trim(regexp_substr(s.s, '[^,]+', 1, rws.l)) as v from
strings s, lateral( select level l from dual connect by level <= length (regexp_replace(s.s, '[^,]+')) + 1 ) rws;
V a b c
Though you might want to check out another method for doing this anyway:
https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/
HT to Wayne Smith in development for helping with this!
Scripts to create the tables for the above example:
create table t1 as
select rownum x from dual connect by level <= 2;
create table t2 as
select mod(rownum, 10) x, rownum y from dual connect by level <= 100;
create index i on t2(x); exec dbms_stats.gather_table_stats(user, 't1'); exec dbms_stats.gather_table_stats(user, 't2');