关于view 的执行计划--续
来源:互联网 发布:java在线编程 编辑:程序博客网 时间:2024/05/01 15:31
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.
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'));
0 0
- 关于view 的执行计划--续
- 关于view 的执行计划
- 关于SQL的执行计划
- 关于SQL的执行计划
- 关于SQL的执行计划
- 关于SQL的执行计划
- 关于SQL的执行计划
- 关于sql的执行计划
- 关于sql的执行计划
- 关于今年执行的减肥计划
- Oracle关于执行计划的简要分析
- 关于计划与执行
- 关于oracle执行计划
- 关于sql和执行计划
- 关于项目计划的执行问题(待写)
- 关于sql的执行计划(推荐详细)
- 关于sql的执行计划(推荐详细)
- 关于Linux下计划任务无法执行的问题
- ArcGIS如何创建地形剖面图
- error LNK2019: 无法解析的外部符号 “public: static class cocos2d::extension::CCScrollView
- 范德萨发扣篮大赛那份两块地范德萨
- 按类别列出的编译器选项Visual Studio 2010
- 《领域驱动设计与模式实践》 - 书摘精要
- 关于view 的执行计划--续
- VC 运行时库 /MD、/MDd 和 /MT、/MTd
- failure install_failed_older_sdk android studio 解决方法
- linux中O(1)调度算法与完全公平(CFS)调度算法
- 腾讯2014校园招聘笔试题
- VC包含目录、附加依赖项、库目录及具体设置
- opengl入门系列- openglES 2.0 的 VBO 用法和例子
- 数论类题集
- U盘防毒最强方案(创建删不掉的autorun.inf文件夹)