행위

오라클 lateral 조인

DB CAFE

DBCAFE (토론 | 기여)님의 2020년 3월 13일 (금) 13:12 판 (→‎LATERAL)
thumb_up 추천메뉴 바로가기

https://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1#lateral-inline-views

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');
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_lateral_조인&oldid=3777"