Explain Plan Output


With the complexities of SQL, its a tough ask nowadays for the EXPLAIN PLAN to handle all the hassles. For example, the WITH clause with a scalar SQL looks fine when directly accessed as below

SQL> explain plan into p1 for
  2  with d as ( select * from dual )
  3  select
  4    empno,
  5    ename,
  6    ( select dname from dept where deptno = e.deptno) dname
  7  from emp e, d;

Explained.

SQL> select * from table(dbms_xplan.display('p1'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   140 |    19   (6)|
|*  1 |  TABLE ACCESS FULL   | DEPT        |     1 |    11 |     3  (34)|
|   2 |  MERGE JOIN CARTESIAN|             |    14 |   140 |    19   (6)|
|   3 |   TABLE ACCESS FULL  | DUAL        |     1 |       |    17   (6)|
|   4 |   BUFFER SORT        |             |    14 |   140 |     2   (0)|
|   5 |    TABLE ACCESS FULL | EMP         |    14 |   140 |     3  (34)|
-------------------------------------------------------------------------

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

   1 - filter("DEPT"."DEPTNO"=:B1)

16 rows selected.

but goes pear-shaped when that is made into a view...Where did my DEPT table go ?

SQL> create or replace view V as
  2  with d as ( select * from dual )
  3  select
  4    empno,
  5    ename,
  6    ( select dname from dept where deptno = e.deptno) dname
  7  from emp e, d
  8  /

View created.

SQL> explain plan into p1 for
  2  select * from v;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display('p1'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |    14 |   406 |    19   (6)|
|   1 |  VIEW                 | V           |    14 |   406 |            |
|   2 |   MERGE JOIN CARTESIAN|             |    14 |   140 |    19   (6)|
|   3 |    TABLE ACCESS FULL  | DUAL        |     1 |       |    17   (6)|
|   4 |    BUFFER SORT        |             |    14 |   140 |     2   (0)|
|   5 |     TABLE ACCESS FULL | EMP         |    14 |   140 |     3  (34)|
--------------------------------------------------------------------------

11 rows selected.