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
--------------------------------------------------------------------------
| 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)
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,'ADVANCED'));
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
------------------------------------------------------------------------------
| 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):
-------------------------------------------------------------
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,'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 |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4
(100)| |
| 1 | NESTED LOOPS | | 14 | 392 | 4 (0)|
00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 350 | 4 (0)|
00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0
(0)| |
------------------------------------------------------------------------------
Look what happens when I turn the optimizer features back to the original
value:
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED'));
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
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------
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,'ADVANCED'));
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
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7
(100)| |
| 1 | MERGE JOIN | | 14 | 504 | 7
(15)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2
(0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1
(0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 350 | 5
(20)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 350 | 4
(0)| 00:00:01 |
----------------------------------------------------------------------------------------
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