2 Replies Latest reply: Jul 29, 2013 11:22 AM by Solomon Yakobson RSS

    12C correlated subqueries

    Solomon Yakobson

      12C docs still state: Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. (Using Subqueries). However it allows referencing parent on any level:

       

      SQL> select  banner
        2    from  v$version
        3  /

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
      PL/SQL Release 12.1.0.1.0 - Production
      CORE    12.1.0.1.0      Production
      TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
      NLSRTL Version 12.1.0.1.0 - Production

      SQL> select  *
        2    from  dual d
        3    where exists (
        4                  select  1
        5                    from  (
        6                           select  dummy
        7                             from  dual
        8                             where dummy = d.dummy
        9                          )
      10                 )
      11  /

      D
      -
      X

      SQL>

       

      And in 11g:

       

      SQL> select  banner
        2    from  v$version
        3  /

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE    11.2.0.3.0      Production
      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production

      SQL> select  *
        2    from  dual d
        3    where exists (
        4                  select  1
        5                    from  (
        6                           select  dummy
        7                             from  dual
        8                             where dummy = d.dummy
        9                          )
      10                 )
      11  /
                                 where dummy = d.dummy
                                               *
      ERROR at line 8:
      ORA-00904: "D"."DUMMY": invalid identifier


      SQL>

       

      I've seen this behavior in one of early 10G releases which then was "fixed" (I'd rather prefer Oracle to leave it and "fix" documentation) in later releases. So I assume same story is going on with 12C. I assume if 12C would allow correlation on any level Oraclewould mention it in New Features, but deep inside I am hoping correlation on any level will stay. Does anyone have any insight? And why am I hoping? New 12C procedure expand_sql_text in dbms_utility package shows how correlation is resolved:

       

      SQL> set long 1000
      SQL> variable c clob
      SQL> begin
        2      dbms_utility.expand_sql_text('
        3  select  *
        4    from  dual d
        5    where exists (
        6                  select  1
        7                    from  (
        8                           select  dummy
        9                             from  dual
      10                             where dummy = d.dummy
      11                          )
      12                 )',:c);
      13  end;
      14  /

      PL/SQL procedure successfully completed.

      SQL> print c

      C
      --------------------------------------------------------------------------------
      SELECT "A1"."DUMMY" "DUMMY" FROM "SYS"."DUAL" "A1" WHERE  EXISTS (SELECT 0 FROM
      (SELECT "A3"."DUMMY" "DUMMY" FROM "SYS"."DUAL" "A3" WHERE "A3"."DUMMY"=:B1) "A2
      ")


      SQL>

       

      And comrare it to "officialyy supported" one level deep correlation:

       

      SQL> set long 1000
      SQL> variable c clob
      SQL> begin
        2      dbms_utility.expand_sql_text('
        3  select  *
        4    from  dual d
        5    where exists (
        6                  select  1
        7                    from  dual
        8                    where dummy = d.dummy
        9                 )',:c);
      10  end;
      11  /

      PL/SQL procedure successfully completed.

      SQL> print c

      C
      --------------------------------------------------------------------------------
      SELECT "A1"."DUMMY" "DUMMY" FROM "SYS"."DUAL" "A1" WHERE  EXISTS (SELECT 0 FROM
      "SYS"."DUAL" "A2" WHERE "A2"."DUMMY"="A1"."DUMMY")


      SQL>

       

      As you can see, in first case optimizer knows that correlation is nested more than one level deep and rewrites query to use bind variable. In second case no rewite is needed.

       

      SY.