【DB笔试面试614】在Oracle中，和视图相关的查询转换有哪些？
【DB笔试面试614】在Oracle中，和视图相关的查询转换有哪些？
AiDBA宝典
发布于 2019-09-29 16:08:05
发布于 2019-09-29 16:08:05
文章被收录于专栏：小麦苗的DB宝专栏
♣
题目部分
在Oracle中，和视图相关的查询转换有哪些？
♣
答案部分
（一）简单视图合并
CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369;
SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='DBA' AND "EMPNO"<>7369)
LHR@orclasm > SELECT /*+ NO_MERGE(V)*/ * FROM VW_SVM_LHR V WHERE V.JOB='DBA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 45352968
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 |
| 1 | VIEW | VW_SVM_LHR | 3 | 261 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='DBA' AND "EMPNO"<>7369)
LHR@orclasm > ALTER SESSION SET "_SIMPLE_VIEW_MERGING"=FALSE;
Session altered.
LHR@orclasm > SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 45352968
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 |
| 1 | VIEW | VW_SVM_LHR | 3 | 261 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='DBA' AND "EMPNO"<>7369)
（二）外连接视图合并（Outer Join View Merging）
CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369;
SELECT * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO=T.DEPTNO(+);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 754 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 13 | 754 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="T"."DEPTNO"(+))
2 - filter("EMPNO"<>7369)
--视图作为被驱动表：
SELECT /*+ FULL(T)*/ * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 754 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 13 | 754 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"(+)="T"."DEPTNO")
3 - filter("EMPNO"(+)<>7369)
--视图含有2个表：
CREATE OR REPLACE VIEW VW_SVM2_LHR AS SELECT /*+ FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND EMPNO<>7369;
SELECT /*+ FULL(T)*/ * FROM VW_SVM2_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1508 | 10 (10)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 13 | 1508 | 10 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | VIEW | VW_SVM2_LHR | 13 | 1248 | 7 (15)| 00:00:01 |
|* 4 | HASH JOIN | | 13 | 663 | 7 (15)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V"."DEPTNO"(+)="T"."DEPTNO")
4 - access("A"."DEPTNO"="B"."DEPTNO")
6 - filter("EMPNO"<>7369)
可见，视图被保留了下来，单独执行。
（三）复杂视图合并（Complex View Merging）
LHR@orclasm > SELECT * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2922957592
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 39 | 7 (15)| 00:00:01 |
| 2 | VIEW | VW_CVM_LHR | 1 | 19 | 3 (0)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 8 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V"."JOB"="T"."DNAME")
4 - filter("T"."JOB"='DBA')
5 - filter("T"."DNAME"='DBA')
LHR@orclasm > SELECT /*+MERGE(V)*/ * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2006461124
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 8 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 28 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 3 | 84 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."JOB"="T"."DNAME")
3 - filter("T"."DNAME"='DBA')
4 - filter("T"."JOB"='DBA')
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2019-08-09，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
