1 LATERAL 조인[편집]

1. Oracle Database는 한동안 내부적으로 래터럴(lateral)을 사용했습니다.
2. 특히 직접 변환이 불가능한 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');

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
             ) ;

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
) ;