"오라클 lateral 조인"의 두 판 사이의 차이
DB CAFE
(→LATERAL 조인) |
|||
(사용자 2명의 중간 판 17개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | + | = LATERAL 조인 = | |
− | + | # Oracle Database는 한동안 내부적으로 래터럴(lateral)을 사용했습니다. | |
− | + | # 특히 직접 변환이 불가능한 ANSI OUTER 조인을 Oracle OUTER 조인으로 변환하는 것이 었습니다. | |
<SOURCE LANG=SQL> | <SOURCE LANG=SQL> | ||
SELECT department_name, employee_name | SELECT department_name, employee_name | ||
FROM departments d | FROM departments d | ||
, LATERAL (SELECT employee_name | , LATERAL (SELECT employee_name | ||
− | FROM | + | FROM employees e |
− | WHERE | + | WHERE e.department_id = d.department_id |
− | ); | + | )(+); |
</SOURCE> | </SOURCE> | ||
+ | * 예를 들어 OUTER 조인에 OR 이 포함 된 경우 : | ||
+ | <SOURCE LANG=SQL> | ||
+ | select t1.x, t2.x from t1 | ||
+ | left join t2 | ||
+ | on t1.x = t2.x or t1.y = t2.x | ||
+ | </SOURCE> | ||
+ | |||
+ | 이것을 Oracle 구문으로 변환하는 방법이 없습니다. | ||
+ | * lateral을 이용하면 : | ||
+ | |||
+ | <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> | ||
+ | * lateral outer 사용시 lareral(...) (+) 알리아스명 | ||
+ | |||
+ | 이에 대한 자세한 내용은 다음을 참조하십시오. | ||
+ | http://optimizermagic.blogspot.co.uk/2007/12/outerjoins-in-oracle.html | ||
+ | |||
+ | 레티럴은 ANSI 표준의 일부입니다. | ||
+ | 따라서 준수 관점에서 노출하는 것이 합리적입니다. | ||
+ | |||
+ | 12c에서 소개 한 CROSS APPLY도 유용합니다. | ||
+ | |||
+ | 병합이 불가능한 VIEW를 작업 할 때도 유용합니다. | ||
+ | |||
+ | 예를 들어, 다음 쿼리에서 Oracle은 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> | ||
+ | <SOURCE LANG=SQL> | ||
+ | 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) | ||
+ | </SOURCE> | ||
+ | |||
+ | 경우에 따라 서브 쿼리 내에서 조인을 수행하면 성능이 크게 향상 될 수 있습니다. | ||
+ | |||
+ | 레터럴 연산자를 사용하면 다음이 가능합니다. | ||
+ | <SOURCE LANG=SQL> | ||
+ | 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; | ||
+ | </SOURCE> | ||
+ | |||
+ | <SOURCE LANG=SQL> | ||
+ | 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") | ||
+ | |||
+ | </SOURCE> | ||
+ | |||
+ | You can see that Oracle switches from two full scans of T2 to two index lookups of it. Possibly a huge saving! | ||
+ | |||
+ | Oracle은 두 개의 T2 full스캔에서 두개의 인덱스 조회로 전환하는 것을 볼 수 있습니다. | ||
+ | |||
+ | 매우크게 속도가 향상 될것입니다. | ||
+ | |||
+ | 물론 교차 적용과 마찬가지로 쿼리에서 and/or 작성시 이해가 쉬워 질 수 있습니다. | ||
+ | |||
+ | 예를 들어, 쉼표로 구분 된 문자열을 행으로 변환하는 다소 복잡한 방법을 사용할 수 있습니다. | ||
+ | |||
+ | <SOURCE LANG=SQL> | ||
+ | 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; | ||
+ | </SOURCE> | ||
+ | <SOURCE LANG=SQL> | ||
+ | V | ||
+ | a | ||
+ | b | ||
+ | c | ||
+ | </SOURCE> | ||
+ | |||
+ | |||
+ | 레터럴은 모든 테이블 캐스트변환오류가 발생되지 않습니다. | ||
+ | |||
+ | 따라서 다음과 같이 다시 작성할 수 있습니다. | ||
+ | <SOURCE LANG=SQL> | ||
+ | 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; | ||
+ | </SOURCE> | ||
+ | <SOURCE LANG=SQL> | ||
+ | V | ||
+ | a | ||
+ | b | ||
+ | c | ||
+ | </SOURCE> | ||
+ | |||
+ | |||
+ | 더많은게 궁금하면 | ||
+ | https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/ | ||
+ | |||
+ | |||
+ | |||
+ | 위 예제에 대한 테이블을 작성하는 스크립트 : | ||
+ | <SOURCE LANG=SQL> | ||
+ | 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'); | ||
+ | </SOURCE> | ||
+ | |||
+ | == CROSS APPLY == | ||
+ | <source lang=sql> | ||
+ | SELECT department_name, employee_name,SALARY, EMPLOYEE_ID | ||
+ | FROM departments d | ||
+ | CROSS APPLY (SELECT employee_name, E.SALARY, EMPLOYEE_ID | ||
+ | FROM employees e | ||
+ | WHERE e.department_id = d.department_id | ||
+ | -- and e.SALARY >= 3000 | ||
+ | ) ; | ||
+ | </source> | ||
+ | * CROSS APPLY = LATERAL | ||
+ | |||
+ | == OUTER APPLY == | ||
+ | <source lang=sql> | ||
+ | SELECT department_name, employee_name,SALARY, EMPLOYEE_ID | ||
+ | FROM departments d | ||
+ | OUTER APPLY (SELECT employee_name, E.SALARY, EMPLOYEE_ID | ||
+ | FROM employees e | ||
+ | WHERE e.department_id = d.department_id | ||
+ | -- and e.SALARY >= 3000 | ||
+ | ) ; | ||
+ | </source> | ||
+ | * OUTER APPLY = LATERAL (+) | ||
+ | |||
+ | https://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1#lateral-inline-views | ||
+ | [[Category:oracle]] |
2023년 4월 11일 (화) 18:01 기준 최신판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 LATERAL 조인[편집]
- Oracle Database는 한동안 내부적으로 래터럴(lateral)을 사용했습니다.
- 특히 직접 변환이 불가능한 ANSI OUTER 조인을 Oracle OUTER 조인으로 변환하는 것이 었습니다.
SELECT department_name, employee_name
FROM departments d
, LATERAL (SELECT employee_name
FROM employees e
WHERE e.department_id = d.department_id
)(+);
- 예를 들어 OUTER 조인에 OR 이 포함 된 경우 :
select t1.x, t2.x from t1
left join t2
on t1.x = t2.x or t1.y = t2.x
이것을 Oracle 구문으로 변환하는 방법이 없습니다.
- lateral을 이용하면 :
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;
- lateral outer 사용시 lareral(...) (+) 알리아스명
이에 대한 자세한 내용은 다음을 참조하십시오. http://optimizermagic.blogspot.co.uk/2007/12/outerjoins-in-oracle.html
레티럴은 ANSI 표준의 일부입니다. 따라서 준수 관점에서 노출하는 것이 합리적입니다.
12c에서 소개 한 CROSS APPLY도 유용합니다.
병합이 불가능한 VIEW를 작업 할 때도 유용합니다.
예를 들어, 다음 쿼리에서 Oracle은 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)
경우에 따라 서브 쿼리 내에서 조인을 수행하면 성능이 크게 향상 될 수 있습니다.
레터럴 연산자를 사용하면 다음이 가능합니다.
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!
Oracle은 두 개의 T2 full스캔에서 두개의 인덱스 조회로 전환하는 것을 볼 수 있습니다.
매우크게 속도가 향상 될것입니다.
물론 교차 적용과 마찬가지로 쿼리에서 and/or 작성시 이해가 쉬워 질 수 있습니다.
예를 들어, 쉼표로 구분 된 문자열을 행으로 변환하는 다소 복잡한 방법을 사용할 수 있습니다.
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
레터럴은 모든 테이블 캐스트변환오류가 발생되지 않습니다.
따라서 다음과 같이 다시 작성할 수 있습니다.
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
더많은게 궁금하면
https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/
위 예제에 대한 테이블을 작성하는 스크립트 :
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');
1.1 CROSS APPLY[편집]
SELECT department_name, employee_name,SALARY, EMPLOYEE_ID
FROM departments d
CROSS APPLY (SELECT employee_name, E.SALARY, EMPLOYEE_ID
FROM employees e
WHERE e.department_id = d.department_id
-- and e.SALARY >= 3000
) ;
- CROSS APPLY = LATERAL
1.2 OUTER APPLY[편집]
SELECT department_name, employee_name,SALARY, EMPLOYEE_ID
FROM departments d
OUTER APPLY (SELECT employee_name, E.SALARY, EMPLOYEE_ID
FROM employees e
WHERE e.department_id = d.department_id
-- and e.SALARY >= 3000
) ;
- OUTER APPLY = LATERAL (+)