"오라클 lateral 조인"의 두 판 사이의 차이
DB CAFE
(→LATERAL) |
(→LATERAL) |
||
10번째 줄: | 10번째 줄: | ||
); | ); | ||
</SOURCE> | </SOURCE> | ||
− | |||
− | |||
− | |||
− | |||
Oracle Database는 한동안 내부적으로 래터럴(lateral)을 사용했습니다. 특히 직접 변환이 불가능한 ANSI OUTER 조인을 Oracle OUTER 조인으로 변환하는 것이 었습니다. | Oracle Database는 한동안 내부적으로 래터럴(lateral)을 사용했습니다. 특히 직접 변환이 불가능한 ANSI OUTER 조인을 Oracle OUTER 조인으로 변환하는 것이 었습니다. | ||
예를 들어 OUTER 조인에 OR 이 포함 된 경우 : | 예를 들어 OUTER 조인에 OR 이 포함 된 경우 : | ||
34번째 줄: | 30번째 줄: | ||
− | + | 이에 대한 자세한 내용은 다음을 참조하십시오. | |
− | |||
http://optimizermagic.blogspot.co.uk/2007/12/outerjoins-in-oracle.html | 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. | 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. | ||
+ | 레티럴은 ANSI 표준의 일부입니다. 따라서 준수 관점에서 노출하는 것이 합리적입니다. | ||
+ | 12c에서 소개 한 CROSS APPLY도 유용합니다. | ||
+ | |||
+ | 병합이 불가능한 VIEW를 작업 할 때도 유용합니다. | ||
+ | 예를 들어, 다음 쿼리에서 Oracle은 t1에 조인하기 전에 서브쿼리를 전부 처리합니다. | ||
− | |||
<SOURCE LANG=SQL> | <SOURCE LANG=SQL> | ||
select /*+ gather_plan_statistics */* from t1, ( | select /*+ gather_plan_statistics */* from t1, ( | ||
54번째 줄: | 53번째 줄: | ||
); | ); | ||
</SOURCE> | </SOURCE> | ||
− | + | <SOURCE LANG=SQL> | |
PLAN_TABLE_OUTPUT | PLAN_TABLE_OUTPUT | ||
------------------------------------------------------------------- | ------------------------------------------------------------------- | ||
84번째 줄: | 83번째 줄: | ||
1 - access("T1"."X"="T2"."X") | 1 - access("T1"."X"="T2"."X") | ||
8 - filter("Y">50) | 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( | select * from table( | ||
dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE +ROWSTATS LAST') | dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE +ROWSTATS LAST') | ||
136번째 줄: | 139번째 줄: | ||
10 - access("T1"."X"="T2"."X") | 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! | 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 ( | with strings as ( | ||
select 'a,b,c' s from dual | select 'a,b,c' s from dual | ||
152번째 줄: | 162번째 줄: | ||
) as sys.OdciNumberList ) | ) as sys.OdciNumberList ) | ||
) rws; | ) rws; | ||
− | + | </SOURCE> | |
+ | <SOURCE LANG=SQL> | ||
V | V | ||
a | a | ||
b | b | ||
c | c | ||
+ | </SOURCE> | ||
− | + | 레터럴은 모든 테이블 캐스트변환오류가 발생되지 않습니다. | |
− | + | 따라서 다음과 같이 다시 작성할 수 있습니다. | |
+ | <SOURCE LANG=SQL> | ||
with strings as ( | with strings as ( | ||
select 'a,b,c' s from dual | select 'a,b,c' s from dual | ||
171번째 줄: | 184번째 줄: | ||
connect by level <= length (regexp_replace(s.s, '[^,]+')) + 1 | connect by level <= length (regexp_replace(s.s, '[^,]+')) + 1 | ||
) rws; | ) rws; | ||
− | + | </SOURCE> | |
+ | <SOURCE LANG=SQL> | ||
V | V | ||
a | a | ||
b | b | ||
c | c | ||
+ | </SOURCE> | ||
− | + | 더많은게 궁금하면 | |
− | |||
https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/ | https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/ | ||
− | |||
− | |||
+ | 위 예제에 대한 테이블을 작성하는 스크립트 : | ||
+ | <SOURCE LANG=SQL> | ||
create table t1 as | create table t1 as | ||
select rownum x from dual connect by level <= 2; | select rownum x from dual connect by level <= 2; | ||
194번째 줄: | 208번째 줄: | ||
exec dbms_stats.gather_table_stats(user, 't1'); | exec dbms_stats.gather_table_stats(user, 't1'); | ||
exec dbms_stats.gather_table_stats(user, 't2'); | exec dbms_stats.gather_table_stats(user, 't2'); | ||
+ | </SOURCE> | ||
+ | |||
[[Category:oracle]] | [[Category:oracle]] |
2020년 3월 13일 (금) 13:12 판
- 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는 한동안 내부적으로 래터럴(lateral)을 사용했습니다. 특히 직접 변환이 불가능한 ANSI OUTER 조인을 Oracle OUTER 조인으로 변환하는 것이 었습니다. 예를 들어 OUTER 조인에 OR 이 포함 된 경우 :
select t1.x, t2.x from t1
left join t2
on t1.x = t2.x or t1.y = t2.x
이것을 Oracle 구문으로 변환하는 방법이 없습니다. 대신에 그것은 다음과 같이되었다 :
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;
이에 대한 자세한 내용은 다음을 참조하십시오.
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. 레티럴은 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');