Query is extremely simple and the result is expected:

select ename,job,mgr,deptno,sal from emp
where deptno in (select deptno from dept)
/
ENAME JOB MGR DEPTNO SAL
---------- --------- ---------- ---------- ----------
SMITH CLERK 7902 20 800
ALLEN SALESMAN 7698 30 1600
WARD SALESMAN 7698 30 1250
JONES MANAGER 7839 20 2975
MARTIN SALESMAN 7698 30 1250
BLAKE MANAGER 7839 30 2850
CLARK MANAGER 7839 10 2450
SCOTT ANALYST 7566 20 3000
KING PRESIDENT 10 5000
TURNER SALESMAN 7698 30 1500
ADAMS CLERK 7788 20 1100
JAMES CLERK 7698 30 950
FORD ANALYST 7566 20 3000
MILLER CLERK 7782 10 1300

14 rows selected.

Elapsed: 00:00:00.01


That is nothing unusual. What is unusual is the execution plan:
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fbqjucf3xdmjb, child number 0
-------------------------------------
select ename,job,mgr,deptno,sal from emp where deptno in (select deptno
from dept)

Plan hash value: 3956160932

--------------------------------------------------------------------------

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

1 - filter("DEPTNO" IS NOT NULL)


19 rows selected.

Elapsed: 00:00:00.04
SQL>

Question: where the @#$%! is the table DEPT? The original query contains
2 tables, the execution plan is only showing me only one. I can get both
tables if I modify the query like this:

select /*+ gather_plan_statistics no_unnest(@sub1) */
ename,job,mgr,deptno,sal from emp
where deptno in (select /*+ qb_name(sub1) */
deptno from dept)
/
.....
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCE D'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID d6vrypfrfb3w9, child number 0
-------------------------------------
select /*+ gather_plan_statistics no_unnest(@sub1) */
ename,job,mgr,deptno,sal from emp where deptno in (select /*+
qb_name(sub1) */ deptno from dept)

Plan hash value: 1783302997

------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SUB1 / DEPT@SUB1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SUB1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SUB1")
FULL(@"SEL$1" "EMP"@"SEL$1")
INDEX(@"SUB1" "DEPT"@"SUB1" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/

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

1 - filter( IS NOT NULL)
3 - access("DEPTNO"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], "MGR"[NUMBER,22],
"SAL"[NUMBER,22], "DEPTNO"[NUMBER,22]
2 - "ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], "MGR"[NUMBER,22],
"SAL"[NUMBER,22], "DEPTNO"[NUMBER,22]


56 rows selected.



It seems that Oracle 11.2.0.3 is doing push_subq and unnest without being
told to. EMP and DEPT are highly unlikely to cause a performance problem,
but a more complex sub-query might. Even more important, it would be
possible to miss it entirely. There is also another anomaly. I tried,
just for fun. to get the classic nested loop plan from that statement. I
got the strangest thing. Here is the query:

select
/*+ gather_plan_statistics
no_unnest(@sub1)
push_subq(@sub1)
index(@SUB1 DEPT@SUB1(DEPTNO)
*/
ename,job,mgr,deptno,sal from emp e
where deptno in (select /*+ qb_name(sub1) */
deptno from dept)
/

Here is the plan:
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCE D'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g3z43q5patbjg, child number 1
-------------------------------------
select /*+ gather_plan_statistics no_unnest(@sub1)
push_subq(@sub1) index(@SUB1 DEPT@SUB1(DEPTNO) */
ename,job,mgr,deptno,sal from emp e where deptno in (select /*+
qb_name(sub1) */ deptno from dept)

Plan hash value: 1130626194

------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / E@SEL$1
2 - SUB1 / DEPT@SUB1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SUB1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SUB1")
FULL(@"SEL$1" "E"@"SEL$1")
PUSH_SUBQ(@"SUB1")
INDEX(@"SUB1" "DEPT"@"SUB1" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/

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

1 - filter( IS NOT NULL)
2 - access("DEPTNO"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "ENAME"[VARCHAR2,10], "JOB"[VARCHAR2,9], "MGR"[NUMBER,22],
"SAL"[NUMBER,22], "DEPTNO"[NUMBER,22]

Note
-----
- cardinality feedback used for this statement


58 rows selected.

Elapsed: 00:00:00.04
SQL>

Question: it does look like a nested loop, but it doesn't say so
anywhere? Which operation is that? Furthermore, the classic nested loop
is now mighty strange:

SQL> alter session set optimizer_features_enable='10.2.0.4';

Session altered.

Elapsed: 00:00:00.02
SQL> select /*+ leading(e) full(e) use_nl(d) */
e.ename,e.job,e.mgr,d.deptno,e.sal
2 from emp e,dept d
3 where e.deptno=d.deptno;

ENAME JOB MGR DEPTNO SAL
---------- --------- ---------- ---------- ----------
SMITH CLERK 7902 20 800
ALLEN SALESMAN 7698 30 1600
WARD SALESMAN 7698 30 1250
JONES MANAGER 7839 20 2975
MARTIN SALESMAN 7698 30 1250
BLAKE MANAGER 7839 30 2850
CLARK MANAGER 7839 10 2450
SCOTT ANALYST 7566 20 3000
KING PRESIDENT 10 5000
TURNER SALESMAN 7698 30 1500
ADAMS CLERK 7788 20 1100
JAMES CLERK 7698 30 950
FORD ANALYST 7566 20 3000
MILLER CLERK 7782 10 1300

14 rows selected.

Elapsed: 00:00:00.00
SQL> save /tmp/3
Created file /tmp/3.sql
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCE D'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID d2aaqmcwy7q1k, child number 0
-------------------------------------
select /*+ leading(e) full(e) use_nl(d) */
e.ename,e.job,e.mgr,d.deptno,e.sal from emp e,dept d where
e.deptno=d.deptno

Plan hash value: 3074306753

------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------

Look what happens when I turn the optimizer features back to the original
value:
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCE D'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID d2aaqmcwy7q1k, child number 1
-------------------------------------
select /*+ leading(e) full(e) use_nl(d) */
e.ename,e.job,e.mgr,d.deptno,e.sal from emp e,dept d where
e.deptno=d.deptno

Plan hash value: 3956160932

--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$F7859CDE / E@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$F7859CDE")
ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")
OUTLINE(@"SEL$1")
FULL(@"SEL$F7859CDE" "E"@"SEL$1")
END_OUTLINE_DATA
*/

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

1 - filter("E"."DEPTNO" IS NOT NULL)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]


48 rows selected.

Join is eliminated, despite the fact that I have not only explicitly
written the query as a join, I've also hinted it to do the nested loops.
The only way I was able to force the nested loop join was to use an
undocumented hint "no_eliminate_join". I had no problems with forcing the
nested loop join if I added "LOC" column to the select list. LOC is not
indexed columns, so the join could not be eliminated. Interestingly
enough, without any hints, that resulted in merge join:

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCE D'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID a30hhd2x2b3vw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */
e.ename,e.job,e.mgr,d.deptno,d.loc,e.sal from emp e,dept d where
e.deptno=d.deptno

Plan hash value: 844388907

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

Using first_rows(1) hint resulted in the new NL join, what Jonathan
called "double NL join" on his blog. I am completely confused now.

--
http://mgogala.byethost5.com

So many questions in such a short space.

Table Elimination -
The IN subquery is transformed into EXISTS
The EXISTS subquery is single table based on a declared primary key,
therefore is subject "uncosted unnesting" (I think, that's the rule applied
here)
The resulting JOIN is from a declared foreign key to a declared primary
key, and no non-key columns appear in the query so the join will always
succeed and return a row from the EMP table if the deptno is not null, and
will fail to return a row only if the deptno is null - so the join can be
replaced by a simple "is not null" predicate.

Add the /*+ no_unnest */ hint and the subquery doesn't turn into a join
therefore join elimination cannot occur. (The fact that no_unnest was
obeyed makes me wonder whether the "uncosted unnesting" above applied in
this case - I have a vague memory that the hint can't override an
unconditional unnest, so I'll have to check that.) Notice that you have an
explicit FILTER operation, and the subquery is behaving as an (EXISTS)
filter subquery.

There is no "push_subq" in this plan, by the way. Push_subq (in principle)
means that Oracle will run the subquery at the earliest possible moment
rather than leaving it to the end of the plan. Since there is only one
table in the driving query the earliest moment and the end of the plan are
the same point in the plan.

In fact, the push_subq path does (at least sometimes) operate differently,
by concealing the filter OPERATOR and leaving the subquery as a filter
PREDICATE to an object access operator. This is exactly what you've got in
your next example where you've explicitly supplied the hint. The filter
OPERATOR from the previous plan has been removed, and the TABLE ACCESS FULL
has slipped one place to the left - nevertheless the INDEX UNIQUE SCAN is
indented at the position it would have been if the FILTER operator had been
in place. You can still see the filter predicate in the predicates section
in line 1 however, since you pulled the plan from memory, and since Oracle
makes a mess of writing subquery predicates into memory, the subquery text
has gone missing from the predicate


I think that answers all the significant questions - the remainder of your
post shows some side effects relating to the behaviour I've desrcibed
above. Randolf Geist has some nice information about the progression of
the nested loop join on his blog:
http://oracle-randolf.blogspot.com/2...-baseline.html
http://oracle-randolf.blogspot.com/2...-2-9i-10g.html

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: <b><em><a href="http://www.apress.com/9781430239543">Oracle
Core</a></em></b>







--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


"Mladen Gogala" <gogala.mladen (AT) gmail (DOT) com> wrote

I failed to supply the link to part 3 of Randolf's series
http://oracle-randolf.blogspot.com/2...art-3-11g.html


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


"Mladen Gogala" <gogala.mladen (AT) gmail (DOT) com> wrote

And another one worth reading (on Join Elimination) - Christian Antognini

http://antognini.ch/2010/01/join-elimination/

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


"Mladen Gogala" <gogala.mladen (AT) gmail (DOT) com> wrote

On Thu, 02 Feb 2012 09:47:48 +0000, Jonathan Lewis wrote:

Ah, I forgot about that. I only looked whether the column was indexed or
not.


That is what I thought. I thought that the subquery was executed first,
as a part of the parse, and turned into an explicit in-list. That's why I
mentioned push_subq.





--
http://mgogala.byethost5.com

You're thinking of the undocumented /*+ precompute_subquery */ hint.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


"Mladen Gogala" <gogala.mladen (AT) gmail (DOT) com> wrote

On Thu, 02 Feb 2012 14:00:22 +0000, Jonathan Lewis wrote:

To tell the truth, that does look very similar to push_subq. With non-
correlated subqueries, the result is probably very similar, in both
cases. I don't think that it is possible to pre-compute a correlated
subquery.



--
http://mgogala.byethost5.com

I'm not sure you're interpreting push_subq correctly.

Consider
select
from parent, child
where child.id_parent = parent_id
and parent.type = 'RARE'
and exists (
select null
from ref_p
where ref_p.id = parent.id
)

Assume that parent.type is indexed and 'RARE' is a value that makes the
optimizer drive from the parent table.

We can see that it would make sense to get a parent row, run the subquery
to check for existence, then acquire the child rows.
The default (historical) Oracle position is to get a parent, join to child,
and then run the subquery for each row in that result set.
By pushing the subquery (down the parse tree) we make Oracle run the
subquery as soon as it has got a row from the parent table.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


"Mladen Gogala" <gogala.mladen (AT) gmail (DOT) com> wrote

Am 02.02.2012 05:08, schrieb Mladen Gogala:

Hi Mladen,

the IN subquery implies that only non-NULL values for emp.deptno are selected.
Now when there is an enabled validated FK constraint emp.deptno->dept.deptno
in place, every non-NULL emp.deptno value is guaranteed to exist in dept.

So with FK_DEPTNO enabled we have

--------------------------------------------------------------------------

and with

SQL> alter table emp disable constraint fk_deptno;

Tabelle wurde geändert.

we now have the expected

------------------------------------------------------------------------------

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

3 - access("DEPTNO"="DEPTNO")

SQL>

So still no table access to dept necessary, as the existance can be validated
by index scan on dept PK index. But now you have the nested loop you wanted to
see.

This is 11.2.0.1 on my laptop.

Regards
Peter