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/2011/07/logical-io-evolution-part-...
http://oracle-randolf.blogspot.com/2011/07/logical-io-evolution-part-...
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.mla
...@gmail.com> wrote in message
...
| Query is extremely simple and the result is expected:
|
| select ename,job,mgr,deptno,sal from emp
| where deptno in (select deptno from dept)
| /
|
| 14 rows selected.
|
| That is nothing unusual. What is unusual is the execution plan:
| SQL> select * from table(dbms_xplan.display_cursor);
|
| --------------------------------------------------------------------------
|| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| --------------------------------------------------------------------------
|| 0 | SELECT STATEMENT | | | | 4 (100)| |
||* 1 | TABLE ACCESS FULL| EMP | 14 | 350 | 4 (0)| 00:00:01 |
| --------------------------------------------------------------------------
|
| Predicate Information (identified by operation id):
| ---------------------------------------------------
| 1 - filter("DEPTNO" IS NOT NULL)
|
|
|
| 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)
| /
| ....
| --------------------------------------------------------------------------- ---
|| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| Time |
| --------------------------------------------------------------------------- ---
|| 0 | SELECT STATEMENT | | | | 4
| (100)| |
||* 1 | FILTER | | | |
|| |
|| 2 | TABLE ACCESS FULL| EMP | 14 | 350 | 4 (0)|
| 00:00:01 |
||* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0
| (0)| |
| --------------------------------------------------------------------------- ---
|
| Query Block Name / Object Alias (identified by operation id):
| -------------------------------------------------------------
|
| Predicate Information (identified by operation id):
| ---------------------------------------------------
| 1 - filter( IS NOT NULL)
| 3 - access("DEPTNO"=:B1)
|
| 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,'ADVANCED'));
|
| 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
|
| --------------------------------------------------------------------------- ---
|| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| Time |
| --------------------------------------------------------------------------- ---
|| 0 | SELECT STATEMENT | | | | 4
| (100)| |
||* 1 | TABLE ACCESS FULL | EMP | 14 | 350 | 4 (0)|
| 00:00:01 |
||* 2 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0
| (0)| |
| --------------------------------------------------------------------------- ---
|
| 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,'ADVANCED'));
|
| 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
|
| --------------------------------------------------------------------------- ---
|| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| Time
...
read more »
