在Oracle中，和视图相关的查询转换有哪些？
♣答案部分
（一）简单视图合并
1CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369; 2SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA'; 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 3956160932 6 7-------------------------------------------------------------------------- 8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 9--------------------------------------------------------------------------10| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 |11|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |12--------------------------------------------------------------------------1314Predicate Information (identified by operation id):15---------------------------------------------------1617 1 - filter("JOB"='DBA' AND "EMPNO"<>7369)18192021LHR@orclasm > SELECT /*+ NO_MERGE(V)*/ * FROM VW_SVM_LHR V WHERE V.JOB='DBA';2223no rows selected242526Execution Plan27----------------------------------------------------------28Plan hash value: 453529682930---------------------------------------------------------------------------------31| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |32---------------------------------------------------------------------------------33| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 |34| 1 | VIEW | VW_SVM_LHR | 3 | 261 | 3 (0)| 00:00:01 |35|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |36---------------------------------------------------------------------------------3738Predicate Information (identified by operation id):39---------------------------------------------------4041 2 - filter("JOB"='DBA' AND "EMPNO"<>7369)4243LHR@orclasm > ALTER SESSION SET "_SIMPLE_VIEW_MERGING"=FALSE;4445Session altered.4647LHR@orclasm > SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA';4849no rows selected505152Execution Plan53----------------------------------------------------------54Plan hash value: 453529685556---------------------------------------------------------------------------------57| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |58---------------------------------------------------------------------------------59| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 |60| 1 | VIEW | VW_SVM_LHR | 3 | 261 | 3 (0)| 00:00:01 |61|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |62---------------------------------------------------------------------------------6364Predicate Information (identified by operation id):65---------------------------------------------------6667 2 - filter("JOB"='DBA' AND "EMPNO"<>7369)CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369; 2SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA'; 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 3956160932 6 7-------------------------------------------------------------------------- 8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 9-------------------------------------------------------------------------- 10| 0 | SELECT STATEMENT | | 3 | 114 | 3 (0)| 00:00:01 | 11|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 | 12-------------------------------------------------------------------------- 13 14Predicate Information (identified by operation id): 15--------------------------------------------------- 16 17 1 - filter("JOB"='DBA' AND "EMPNO"<>7369) 18 19 20 21LHR@orclasm > SELECT /*+ NO_MERGE(V)*/ * FROM VW_SVM_LHR V WHERE V.JOB='DBA'; 22 23no rows selected 24 25 26Execution Plan 27---------------------------------------------------------- 28Plan hash value: 45352968 29 30--------------------------------------------------------------------------------- 31| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 32--------------------------------------------------------------------------------- 33| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 | 34| 1 | VIEW | VW_SVM_LHR | 3 | 261 | 3 (0)| 00:00:01 | 35|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 | 36--------------------------------------------------------------------------------- 37 38Predicate Information (identified by operation id): 39--------------------------------------------------- 40 41 2 - filter("JOB"='DBA' AND "EMPNO"<>7369) 42 43LHR@orclasm > ALTER SESSION SET "_SIMPLE_VIEW_MERGING"=FALSE; 44 45Session altered. 46 47LHR@orclasm > SELECT * FROM VW_SVM_LHR V WHERE V.JOB='DBA'; 48 49no rows selected 50 51 52Execution Plan 53---------------------------------------------------------- 54Plan hash value: 45352968 55 56--------------------------------------------------------------------------------- 57| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 58--------------------------------------------------------------------------------- 59| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 | 60| 1 | VIEW | VW_SVM_LHR | 3 | 261 | 3 (0)| 00:00:01 | 61|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 | 62--------------------------------------------------------------------------------- 63 64Predicate Information (identified by operation id): 65--------------------------------------------------- 66 67 2 - filter("JOB"='DBA' AND "EMPNO"<>7369)
（二）外连接视图合并（Outer Join View Merging）
1CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369; 2SELECT * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO=T.DEPTNO(+); 3--------------------------------------------------------------------------- 4| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 5--------------------------------------------------------------------------- 6| 0 | SELECT STATEMENT | | 13 | 754 | 7 (15)| 00:00:01 | 7|* 1 | HASH JOIN OUTER | | 13 | 754 | 7 (15)| 00:00:01 | 8|* 2 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 | 9| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |10---------------------------------------------------------------------------1112Predicate Information (identified by operation id):13---------------------------------------------------1415 1 - access("DEPTNO"="T"."DEPTNO"(+))16 2 - filter("EMPNO"<>7369)171819--视图作为被驱动表：20SELECT /*+ FULL(T)*/ * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO;21---------------------------------------------------------------------------22| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |23---------------------------------------------------------------------------24| 0 | SELECT STATEMENT | | 13 | 754 | 7 (15)| 00:00:01 |25|* 1 | HASH JOIN OUTER | | 13 | 754 | 7 (15)| 00:00:01 |26| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |27|* 3 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |28---------------------------------------------------------------------------29Predicate Information (identified by operation id):30---------------------------------------------------3132 1 - access("DEPTNO"(+)="T"."DEPTNO")33 3 - filter("EMPNO"(+)<>7369)343536--视图含有2个表：37CREATE 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;38SELECT /*+ FULL(T)*/ * FROM VW_SVM2_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO;39------------------------------------------------------------------------------------40| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |41------------------------------------------------------------------------------------42| 0 | SELECT STATEMENT | | 13 | 1508 | 10 (10)| 00:00:01 |43|* 1 | HASH JOIN OUTER | | 13 | 1508 | 10 (10)| 00:00:01 |44| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |45| 3 | VIEW | VW_SVM2_LHR | 13 | 1248 | 7 (15)| 00:00:01 |46|* 4 | HASH JOIN | | 13 | 663 | 7 (15)| 00:00:01 |47| 5 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |48|* 6 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 |49------------------------------------------------------------------------------------5051Predicate Information (identified by operation id):52---------------------------------------------------5354 1 - access("V"."DEPTNO"(+)="T"."DEPTNO")55 4 - access("A"."DEPTNO"="B"."DEPTNO")56 6 - filter("EMPNO"<>7369)CREATE OR REPLACE VIEW VW_SVM_LHR AS SELECT * FROM SCOTT.EMP WHERE EMPNO<>7369; 2SELECT * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO=T.DEPTNO(+); 3--------------------------------------------------------------------------- 4| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 5--------------------------------------------------------------------------- 6| 0 | SELECT STATEMENT | | 13 | 754 | 7 (15)| 00:00:01 | 7|* 1 | HASH JOIN OUTER | | 13 | 754 | 7 (15)| 00:00:01 | 8|* 2 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 | 9| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | 10--------------------------------------------------------------------------- 11 12Predicate Information (identified by operation id): 13--------------------------------------------------- 14 15 1 - access("DEPTNO"="T"."DEPTNO"(+)) 16 2 - filter("EMPNO"<>7369) 17 18 19--视图作为被驱动表： 20SELECT /*+ FULL(T)*/ * FROM VW_SVM_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO; 21--------------------------------------------------------------------------- 22| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 23--------------------------------------------------------------------------- 24| 0 | SELECT STATEMENT | | 13 | 754 | 7 (15)| 00:00:01 | 25|* 1 | HASH JOIN OUTER | | 13 | 754 | 7 (15)| 00:00:01 | 26| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | 27|* 3 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 | 28--------------------------------------------------------------------------- 29Predicate Information (identified by operation id): 30--------------------------------------------------- 31 32 1 - access("DEPTNO"(+)="T"."DEPTNO") 33 3 - filter("EMPNO"(+)<>7369) 34 35 36--视图含有2个表： 37CREATE 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; 38SELECT /*+ FULL(T)*/ * FROM VW_SVM2_LHR V,SCOTT.DEPT T WHERE V.DEPTNO(+)=T.DEPTNO; 39------------------------------------------------------------------------------------ 40| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 41------------------------------------------------------------------------------------ 42| 0 | SELECT STATEMENT | | 13 | 1508 | 10 (10)| 00:00:01 | 43|* 1 | HASH JOIN OUTER | | 13 | 1508 | 10 (10)| 00:00:01 | 44| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 | 45| 3 | VIEW | VW_SVM2_LHR | 13 | 1248 | 7 (15)| 00:00:01 | 46|* 4 | HASH JOIN | | 13 | 663 | 7 (15)| 00:00:01 | 47| 5 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 | 48|* 6 | TABLE ACCESS FULL| EMP | 13 | 494 | 3 (0)| 00:00:01 | 49------------------------------------------------------------------------------------ 50 51Predicate Information (identified by operation id): 52--------------------------------------------------- 53 54 1 - access("V"."DEPTNO"(+)="T"."DEPTNO") 55 4 - access("A"."DEPTNO"="B"."DEPTNO") 56 6 - filter("EMPNO"<>7369)
可见，视图被保留了下来，单独执行。
（三）复杂视图合并（Complex View Merging）
1LHR@orclasm > SELECT * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA'; 2 3no rows selected 4 5 6Execution Plan 7---------------------------------------------------------- 8Plan hash value: 2922957592 910-----------------------------------------------------------------------------------11| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |12-----------------------------------------------------------------------------------13| 0 | SELECT STATEMENT | | 1 | 39 | 7 (15)| 00:00:01 |14|* 1 | HASH JOIN | | 1 | 39 | 7 (15)| 00:00:01 |15| 2 | VIEW | VW_CVM_LHR | 1 | 19 | 3 (0)| 00:00:01 |16| 3 | HASH GROUP BY | | 1 | 8 | 3 (0)| 00:00:01 |17|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |18|* 5 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 |19-----------------------------------------------------------------------------------2021Predicate Information (identified by operation id):22---------------------------------------------------2324 1 - access("V"."JOB"="T"."DNAME")25 4 - filter("T"."JOB"='DBA')26 5 - filter("T"."DNAME"='DBA')2728LHR@orclasm > SELECT /*+MERGE(V)*/ * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA';2930no rows selected313233Execution Plan34----------------------------------------------------------35Plan hash value: 20064611243637----------------------------------------------------------------------------38| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |39----------------------------------------------------------------------------40| 0 | SELECT STATEMENT | | 1 | 28 | 8 (25)| 00:00:01 |41| 1 | HASH GROUP BY | | 1 | 28 | 8 (25)| 00:00:01 |42|* 2 | HASH JOIN | | 3 | 84 | 7 (15)| 00:00:01 |43|* 3 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |44|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |45----------------------------------------------------------------------------4647Predicate Information (identified by operation id):48---------------------------------------------------4950 2 - access("T"."JOB"="T"."DNAME")51 3 - filter("T"."DNAME"='DBA')52 4 - filter("T"."JOB"='DBA')SELECT * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA'; 2 3no rows selected 4 5 6Execution Plan 7---------------------------------------------------------- 8Plan hash value: 2922957592 9 10----------------------------------------------------------------------------------- 11| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 12----------------------------------------------------------------------------------- 13| 0 | SELECT STATEMENT | | 1 | 39 | 7 (15)| 00:00:01 | 14|* 1 | HASH JOIN | | 1 | 39 | 7 (15)| 00:00:01 | 15| 2 | VIEW | VW_CVM_LHR | 1 | 19 | 3 (0)| 00:00:01 | 16| 3 | HASH GROUP BY | | 1 | 8 | 3 (0)| 00:00:01 | 17|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 | 18|* 5 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 | 19----------------------------------------------------------------------------------- 20 21Predicate Information (identified by operation id): 22--------------------------------------------------- 23 24 1 - access("V"."JOB"="T"."DNAME") 25 4 - filter("T"."JOB"='DBA') 26 5 - filter("T"."DNAME"='DBA') 27 28LHR@orclasm > SELECT /*+MERGE(V)*/ * FROM VW_CVM_LHR V,SCOTT.DEPT T WHERE V.JOB=T.DNAME AND V.JOB='DBA'; 29 30no rows selected 31 32 33Execution Plan 34---------------------------------------------------------- 35Plan hash value: 2006461124 36 37---------------------------------------------------------------------------- 38| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 39---------------------------------------------------------------------------- 40| 0 | SELECT STATEMENT | | 1 | 28 | 8 (25)| 00:00:01 | 41| 1 | HASH GROUP BY | | 1 | 28 | 8 (25)| 00:00:01 | 42|* 2 | HASH JOIN | | 3 | 84 | 7 (15)| 00:00:01 | 43|* 3 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 | 44|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 | 45---------------------------------------------------------------------------- 46 47Predicate Information (identified by operation id): 48--------------------------------------------------- 49 50 2 - access("T"."JOB"="T"."DNAME") 51 3 - filter("T"."DNAME"='DBA') 52 4 - filter("T"."JOB"='DBA')
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧
