Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Lateral and JPPD transformation

Mohamed HouriFeb 11 2024 — edited Feb 11 2024

Hello

The Oracle Join Push Predicate Down (aka JPPD) exists since Oracle 11g.

SQL Lateral clause exists since Oracle 12.1

But it looks like the JPPD uses internally the Lateral clause

Here’s a reproducible example

create table t1 as
  select
      rownum n1
     ,mod(rownum,5) n2
     ,trunc( (rownum - 1 / 3) ) n3
  from
     dual
connect by level <= 1e2;
 
create table t2 as
  select
     rownum n1
    ,mod(rownum,3) n2
    ,trunc( (rownum - 1 / 5) ) n3
from
    dual
 connect by level <= 1e3;

create table t3 as
   select
     rownum n1
    ,lpad('x',4000,'x') vc
from dual
connect by level <= 100;
 
alter table t2 add constraint t2_pk primary key (n1);
alter table t3 add constraint t3_pk primary key (n1);
 
begin
  dbms_stats.gather_table_stats(user, 't1' );
  dbms_stats.gather_table_stats(user, 't2' );
  dbms_stats.gather_table_stats(user, 't3' );
end;
/

Here’s the query wich uses the JPPD

select
   t1.n1, t1.n3
from
   t1
  left join
   (select 
       t2.n1 
    from 
        t2, t3
    where  t2.n1 = t3.n1
    ) vw_1
on t1.n1 = vw_1.n1; 

Plan hash value: 3070139659

---------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |      |       |     3 (100)|          |
|   1 |  NESTED LOOPS OUTER     |      |   100 |   800 |    3   (0)| 00:00:01  |
|   2 |   TABLE ACCESS FULL     | T1   |   100 |   600 |    3   (0)| 00:00:01  |
|   3 |   VIEW PUSHED PREDICATE |       |    1 |     2 |     0  (0)|           |
|   4 |    NESTED LOOPS         |      |     1 |     7 |    0   (0)|           |
|*  5 |     INDEX UNIQUE SCAN   | T3_PK |    1 |     3 |     0  (0)|           |
|*  6 |    INDEX UNIQUE SCAN   | T2_PK |     1 |    4 |     0   (0)|           |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T3"."N1"="T1"."N1")
   6 - access("T2"."N1"="T1"."N1")

If you activate the CBO (10053) trace file you will find that Oracle, has rewritten the original query like shown below

SELECT
    "T1"."N1" "N1",
    "T1"."N3" "N3"
FROM
    "C##MHOURI"."T1" "T1",
    (
      SELECT
          "T2"."N1" "N1"
      FROM
          "C##MHOURI"."T2" "T2",
          "C##MHOURI"."T3" "T3"
      WHERE
          "T3"."N1" = "T1"."N1"
      AND "T2"."N1" = "T1"."N1" 
    )"VW";

However if you run the above query you will get the following error

ERROR at line 14:
ORA-00904: "T1"."N1": invalid identifier

The above query will work correctly only when we use the LATERAL clause

SELECT
    "T1"."N1" "N1",
    "T1"."N3" "N3"
FROM
    "C##MHOURI"."T1" "T1",
  Lateral  (
     SELECT
         "T2"."N1" "N1"
      FROM
          "C##MHOURI"."T2" "T2",
          "C##MHOURI"."T3" "T3"
      WHERE
          "T3"."N1" = "T1"."N1"
      AND "T2"."N1" = "T1"."N1" 
   )"VW";

100 rows selected.

So, If I got everything correct, this means that Oracle was using internally the Lateral clause in Oracle 11g before it has been made available for everyone starting from Oracle 12c

Am I Right?

Mohamed Houri

Comments
Post Details
Added on Feb 11 2024
8 comments
106 views