SQL CROSS APPLY, OUTER APPLY and LATERAL Oracle Database 12C release 1 (12.1)

New extensions are added for SQL in 12C

CROSS APPLY is another variation to make simple join ANSI CROSS JOIN
OUTER APPLY is another variation to make left outer join ANSI LEFT OUTER APPLY

LATERAL is another variation to make simple join

Let’s prepare some test data

create table test_a
(
  a_id number,
  a_id1 number
);

create table test_b
(
  b_id number,
  b_id1 number
);

begin
  for i in 1..10
  loop
    insert into test_a values(i, i);
  end loop;

  for i in 1..5
  loop
    insert into test_b values(i, i);
  end loop;

  for i in 6..10
  loop
    insert into test_b values(i, null);
  end loop;

  commit;
end;
/

select * from test_a;

      A_ID      A_ID1
---------- ----------
         1          1 
         2          2 
         3          3 
         4          4 
         5          5 
         6          6 
         7          7 
         8          8 
         9          9 
        10         10 

select * from test_b;

      B_ID      B_ID1
---------- ----------
         1          1 
         2          2 
         3          3 
         4          4 
         5          5 
         6            
         7            
         8            
         9            
        10

If you would like to write query in following way it’s not possible in 12C and previous releases

select * from test_a a, (select *
                             from test_b b 
                            where b.b_id1=a.a_id1);

ORA-00904: "A"."A_ID1": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 37 Column: 43

new extensions enables it

CROSS APPLY

It works like normal join between two tables

set autotrace on explain

select * from test_a a cross apply
                           (select *
                             from test_b b 
                            where b.b_id1=a.a_id1);

     A_ID      A_ID1       B_ID      B_ID1
---------- ---------- ---------- ----------
         1          1          1          1 
         2          2          2          2 
         3          3          3          3 
         4          4          4          4 
         5          5          5          5

Plan hash value: 2103443040

------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     5 |   260 |     6   (0)|
|*  1 |  HASH JOIN         |        |     5 |   260 |     6   (0)|
|   2 |   TABLE ACCESS FULL| TEST_A |    10 |   260 |     3   (0)|
|   3 |   TABLE ACCESS FULL| TEST_B |    10 |   260 |     3   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."B_ID1"="A"."A_ID1")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

is equivalent to

set autotrace on explain

select * from test_a a, test_b b
where b.b_id1=a.a_id1;

      A_ID      A_ID1       B_ID      B_ID1
---------- ---------- ---------- ----------
         1          1          1          1 
         2          2          2          2 
         3          3          3          3 
         4          4          4          4 
         5          5          5          5

Plan hash value: 2103443040

------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     5 |   260 |     6   (0)|
|*  1 |  HASH JOIN         |        |     5 |   260 |     6   (0)|
|   2 |   TABLE ACCESS FULL| TEST_A |    10 |   260 |     3   (0)|
|   3 |   TABLE ACCESS FULL| TEST_B |    10 |   260 |     3   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."B_ID1"="A"."A_ID1")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

OUTER APPLY

It works like left outer join between tables

set autotrace on explain

select * from test_a a outer apply
                           (select *
                             from test_b b 
                            where b.b_id1=a.a_id1); 

      A_ID      A_ID1       B_ID      B_ID1
---------- ---------- ---------- ----------
         1          1          1          1 
         2          2          2          2 
         3          3          3          3 
         4          4          4          4 
         5          5          5          5 
         8          8                       
        10         10                       
         6          6                       
         7          7                       
         9          9

lan hash value: 1135461404

------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    10 |   520 |     6   (0)|
|*  1 |  HASH JOIN OUTER   |        |    10 |   520 |     6   (0)|
|   2 |   TABLE ACCESS FULL| TEST_A |    10 |   260 |     3   (0)|
|   3 |   TABLE ACCESS FULL| TEST_B |    10 |   260 |     3   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."B_ID1"(+)="A"."A_ID1")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

is equivalent to LEFT OUTER JOIN

select * from test_a a left outer join test_b b
on a.a_id1=b.b_id1;

      A_ID      A_ID1       B_ID      B_ID1
---------- ---------- ---------- ----------
         1          1          1          1 
         2          2          2          2 
         3          3          3          3 
         4          4          4          4 
         5          5          5          5 
         8          8                       
        10         10                       
         6          6                       
         7          7                       
         9          9

Plan hash value: 1135461404

------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    10 |   520 |     6   (0)|
|*  1 |  HASH JOIN OUTER   |        |    10 |   520 |     6   (0)|
|   2 |   TABLE ACCESS FULL| TEST_A |    10 |   260 |     3   (0)|
|   3 |   TABLE ACCESS FULL| TEST_B |    10 |   260 |     3   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."A_ID1"="B"."B_ID1"(+))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2

LATERAL

it works in the same way as simple join in Oracle

select * from test_a a, lateral
                           (select b.b_id, b.b_id1
                             from test_b b 
                            where b.b_id1=a.a_id1);
     A_ID      A_ID1       B_ID      B_ID1
---------- ---------- ---------- ----------
         1          1          1          1 
         2          2          2          2 
         3          3          3          3 
         4          4          4          4 
         5          5          5          5

Plan hash value: 2103443040

------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     5 |   260 |     6   (0)|
|*  1 |  HASH JOIN         |        |     5 |   260 |     6   (0)|
|   2 |   TABLE ACCESS FULL| TEST_A |    10 |   260 |     3   (0)|
|   3 |   TABLE ACCESS FULL| TEST_B |    10 |   260 |     3   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."B_ID1"="A"."A_ID1")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

For all the extensions there are no difference in explain plan so there is no performance advantage in using it. I think Oracle has added them to make migration from Microsoft much simpler.

Joins with functions

Another option is to make join between a table and a function.

Let’s prepare some test data.

create or replace type t_rec is object
(id number);
/

create or replace type t_tbl is table of t_rec;
/

create or replace function make_join_fnc
( p_id number )
return t_tbl pipelined
is
begin 
  if p_id is null then
    pipe row(t_rec(0));
    pipe row(t_rec(0));

    return;
  elsif p_id > 2 then
    pipe row(t_rec(1));

    return;
  else
    pipe row(t_rec(2));

    return;
  end if;  
end;
/

Now it shows real power. Simple syntax to make join with a function. As you can notice for NULL value function make_join_fnc duplicates records.

select * from test_b a outer apply make_join_fnc(a.b_id1);

      B_ID      B_ID1         ID
---------- ---------- ----------
         1          1          2 
         2          2          2 
         3          3          1 
         4          4          1 
         5          5          1 
         6                     0 
         6                     0 
         7                     0 
         7                     0 
         8                     0 
         8                     0 
         9                     0 
         9                     0 
        10                     0 
        10                     0

Have a fun 🙂

Tomasz

8 thoughts on “SQL CROSS APPLY, OUTER APPLY and LATERAL Oracle Database 12C release 1 (12.1)”

mr on September 15, 2013 at 2:35 am said:

select * from test_a a,lateral (select *
from test_b b
where b.b_id1=a.a_id1); 2 3
where b.b_id1=a.a_id1)
*
ERROR at line 3:
ORA-00904: “A”.”A_ID1″: invalid identifier
select * from test_a a cross apply
(select *
from test_b b
where b.b_id1=a.a_id1); 2 3 4
where b.b_id1=a.a_id1)
*
ERROR at line 4:
ORA-00904: “A”.”A_ID1″: invalid identifier

select * from test_a a outer apply
(select *
from test_b b
where b.b_id1=a.a_id1); 2 3 4
where b.b_id1=a.a_id1)
*
ERROR at line 4:
ORA-00904: “A”.”A_ID1″: invalid identifier
compatible string 12.1.0.0.0

Reply ↓
- admin on September 15, 2013 at 7:30 am said:
  
  I made mistake it’s fixed now. Thank You for notice it.
  
  Cheers
  Tomasz
  
  Reply ↓
mr on September 15, 2013 at 2:47 am said:

continuation …..
i used same tables given in the url and all the columns are exists in the tables ..
do we have to enable to any feature to see the results

Reply ↓
- admin on September 15, 2013 at 7:31 am said:
  
  as above
  Thanks
  Tomasz
  
  Reply ↓
mirek on February 17, 2015 at 12:03 pm said:

example with lateral doesnt return b_id1 column as presented. the select after lateral contains only b_id.

thanks for the examples, I’m still looking for some serious application for new syntax, if any. looks like just another syntax to me so far.

Reply ↓
- admin on February 19, 2015 at 10:23 am said:
  
  Fixed Thanks 🙂
  
  Reply ↓
rbechara on July 9, 2015 at 7:06 pm said:

You presented the execution plans of different queries. Some using JOIN, others using APPLY. The number are practically identicals. I should ask you about the number of total records in your tables and the number of recordings you are searching for. How much are they? I ask this because we see TABLE ACCESS FULL in all cases. So, the tests could be more accurated if seached register numbers are less than 10% of total registers count. For we to see another strategy DBMS uses in those cases. Can you provide us with it please?

Reply ↓
- admin on July 10, 2015 at 9:48 am said:
  
  Explain plans are the same so why do you expect any difference in performance ?
  
  Regards
  Tomasz
  
  Reply ↓

DBAORA

oracle expert presents

SQL CROSS APPLY, OUTER APPLY and LATERAL Oracle Database 12C release 1 (12.1)

8 thoughts on “SQL CROSS APPLY, OUTER APPLY and LATERAL Oracle Database 12C release 1 (12.1)”

Leave a Reply Cancel reply