关于view 的执行计划

最新推荐文章于 2024-01-06 19:10:53 发布

原创最新推荐文章于 2024-01-06 19:10:53 发布 · 723 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#view执行计划

oracle 专栏收录该内容

30 篇文章

订阅专栏

SQL> DESC emp_temp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NUMBER(4)
ENAME NOT NULL VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> SHOW USER
USER is "SCOTT"
SQL> create view emp_mgr_view as select * from emp_temp where job='MANAGER';

View created.

SQL> select EMPNO,SAL from emp_mgr_view WHERE ENAME='CLARK';

EMPNO SAL
---------- ----------
7782 2450

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 41c0jz9cwfrxp, child number 0
-------------------------------------
select EMPNO,SAL from emp_mgr_view WHERE ENAME='CLARK'

Plan hash value: 2473744504

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP_TEMP | 1 | 23 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

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

1 - SEL$F5BB74E1 / EMP_TEMP@SEL$2

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

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "EMP_TEMP"@"SEL$2")
END_OUTLINE_DATA
*/

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

1 - filter(("ENAME"='CLARK' AND "JOB"='MANAGER'))

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

1 - "EMPNO"[NUMBER,22], "SAL"[NUMBER,22]

44 rows selected.

SQL> create view emp_view as select * from emp_temp where job='MANAGER'AND ROWNUM<10;

View created.

SQL> select EMPNO,SAL from emp_view WHERE ENAME='CLARK';

EMPNO SAL
---------- ----------
7782 2450

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 4k3ku0azd62ms, child number 0
-------------------------------------
select EMPNO,SAL from emp_view WHERE ENAME='CLARK'

Plan hash value: 2208042655

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | VIEW | EMP_VIEW | 3 | 99 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| EMP_TEMP | 3 | 69 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

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

1 - SEL$2 / EMP_VIEW@SEL$1
2 - SEL$2
3 - SEL$2 / EMP_TEMP@SEL$2

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "EMP_VIEW"@"SEL$1")
FULL(@"SEL$2" "EMP_TEMP"@"SEL$2")

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
END_OUTLINE_DATA
*/

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

1 - filter("ENAME"='CLARK')
2 - filter(ROWNUM<10)
3 - filter("JOB"='MANAGER')

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

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

51 rows selected.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> select EMPNO,ENAME from emp WHERE empno in(select empno from emp_view);

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

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 1puw24sw4n5dz, child number 0
-------------------------------------
select EMPNO,ENAME from emp WHERE empno in(select empno from emp_view)

Plan hash value: 3671142903

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS | | 3 | 69 | 5 (20) | 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 | 3 (0) | 00:00:01 |
| 3 | HASH UNIQUE | | 3 | 12 | | |
| 4 | VIEW | EMP_VIEW | 3 | 12 | 3 (0)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
|* 6 | TABLE ACCESS FULL | EMP_TEMP | 3 | 36 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 8 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
-----------------------------------------------------------------------------------------

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

1 - SEL$5DA710D3
2 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
3 - SEL$683B0107
4 - SEL$3 / EMP_VIEW@SEL$2
5 - SEL$3
6 - SEL$3 / EMP_TEMP@SEL$3
7 - SEL$5DA710D3 / EMP@SEL$1
8 - SEL$5DA710D3 / EMP@SEL$1

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

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$683B0107")
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$683B0107")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
INDEX(@"SEL$5DA710D3" "EMP"@"SEL$1" ("EMP"."EMPNO"))
LEADING(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3" "EMP"@"SEL$1")

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
USE_NL(@"SEL$5DA710D3" "EMP"@"SEL$1")
NO_ACCESS(@"SEL$683B0107" "EMP_VIEW"@"SEL$2")
FULL(@"SEL$3" "EMP_TEMP"@"SEL$3")
END_OUTLINE_DATA
*/

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

5 - filter(ROWNUM<10)
6 - filter("JOB"='MANAGER')
8 - access("EMPNO"="$nso_col_1")

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

1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - "$nso_col_1"[NUMBER,22]
3 - "EMPNO"[NUMBER,22]
4 - "EMPNO"[NUMBER,22]
5 - "EMPNO"[NUMBER,22]
6 - "EMPNO"[NUMBER,22]
7 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
8 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]

76 rows selected.

SQL>