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.