"오라클 lateral 조인"의 두 판 사이의 차이

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