关于view 的执行计划--续

SQL> select EMPNO,ENAME from emp WHERE empno in(select /*+ no_unnest*/empno from emp_view);

     EMPNO ENAME
---------- ----------
      7566 JONES
      7698 BLAKE
      7782 CLARK

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID dwp84d4hwvjv7, child number 0
-------------------------------------
select EMPNO,ENAME from emp WHERE empno in(select /*+ no_unnest*/empno
from emp_view)

Plan hash value: 2908627659

---------------------------------------------------------------------------------
| Id  | Operation     | Name| Rows| Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     | |||    24 (100)||
|*  1 |  FILTER     ||| |     | |
|   2 |   TABLE ACCESS FULL  | EMP |    14 |   140 |     3   (0)| 00:00:01 |
|*  3 |   VIEW     | EMP_VIEW |     3 |    39 |     3   (0)| 00:00:01 |
|*  4 |    COUNT STOPKEY     | |||     ||
|*  5 |     TABLE ACCESS FULL| EMP_TEMP |     3 |    36 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

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

   1 - SEL$1
   2 - SEL$1 / EMP@SEL$1
   3 - SEL$3 / EMP_VIEW@SEL$2
   4 - SEL$3
   5 - SEL$3 / EMP_TEMP@SEL$3

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      NO_ACCESS(@"SEL$2" "EMP_VIEW"@"SEL$2")
      FULL(@"SEL$3" "EMP_TEMP"@"SEL$3")
      END_OUTLINE_DATA
  */

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

   1 - filter( IS NOT NULL)

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   3 - filter("EMPNO"=:B1)
   4 - filter(ROWNUM<10)
   5 - filter("JOB"='MANAGER')

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

   1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
   2 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
   3 - "EMPNO"[NUMBER,22]
   4 - "EMPNO"[NUMBER,22]
   5 - "EMPNO"[NUMBER,22]

64 rows selected.

SQL>  

它的截图如下

SQL> select /*+ gather_PLAN_statistics */EMPNO,ENAME from emp_cp WHERE deptno in(select /*+no_unnest*/deptno from dept_cp);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

已选择14行。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));