在Oracle中，和“表达式和条件评估”相关的查询转换有哪些？
♣答案部分
（一）逻辑转换
1LHR@orclasm > SELECT /*+FULL(A) FULL(B)*/ * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND B.DEPTNO=20; 2 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 4192419542 6 7--------------------------------------------------------------------------- 8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 9---------------------------------------------------------------------------10| 0 | SELECT STATEMENT | | 4 | 468 | 5 (0)| 00:00:01 |11| 1 | NESTED LOOPS | | 4 | 468 | 5 (0)| 00:00:01 |12|* 2 | TABLE ACCESS FULL| DEPT | 1 | 30 | 2 (0)| 00:00:01 |13|* 3 | TABLE ACCESS FULL| EMP | 5 | 435 | 3 (0)| 00:00:01 |14---------------------------------------------------------------------------1516Predicate Information (identified by operation id):17---------------------------------------------------1819 2 - filter("B"."DEPTNO"=20)20 3 - filter("A"."DEPTNO"=20)SELECT /*+FULL(A) FULL(B)*/ * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND B.DEPTNO=20; 2 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 4192419542 6 7--------------------------------------------------------------------------- 8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 9--------------------------------------------------------------------------- 10| 0 | SELECT STATEMENT | | 4 | 468 | 5 (0)| 00:00:01 | 11| 1 | NESTED LOOPS | | 4 | 468 | 5 (0)| 00:00:01 | 12|* 2 | TABLE ACCESS FULL| DEPT | 1 | 30 | 2 (0)| 00:00:01 | 13|* 3 | TABLE ACCESS FULL| EMP | 5 | 435 | 3 (0)| 00:00:01 | 14--------------------------------------------------------------------------- 15 16Predicate Information (identified by operation id): 17--------------------------------------------------- 18 19 2 - filter("B"."DEPTNO"=20) 20 3 - filter("A"."DEPTNO"=20)
（二）常量转换
1LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE a.sal>=100+50; 2Execution Plan 3---------------------------------------------------------- 4Plan hash value: 3956160932 5 6-------------------------------------------------------------------------- 7| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 8-------------------------------------------------------------------------- 9| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |10|* 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |11--------------------------------------------------------------------------1213Predicate Information (identified by operation id):14---------------------------------------------------1516 1 - filter("A"."SAL">=150)SELECT * FROM SCOTT.EMP A WHERE a.sal>=100+50; 2Execution Plan 3---------------------------------------------------------- 4Plan hash value: 3956160932 5 6-------------------------------------------------------------------------- 7| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 8-------------------------------------------------------------------------- 9| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 | 10|* 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 | 11-------------------------------------------------------------------------- 12 13Predicate Information (identified by operation id): 14--------------------------------------------------- 15 16 1 - filter("A"."SAL">=150)
（三）LIKE转换
1LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.ENAME LIKE 'lhr' ; 2-------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4-------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | 6|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 | 7-------------------------------------------------------------------------- 8 9Predicate Information (identified by operation id):10---------------------------------------------------1112 1 - filter("A"."ENAME"='lhr')SELECT * FROM SCOTT.EMP A WHERE A.ENAME LIKE 'lhr' ; 2-------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4-------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | 6|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 | 7-------------------------------------------------------------------------- 8 9Predicate Information (identified by operation id): 10--------------------------------------------------- 11 12 1 - filter("A"."ENAME"='lhr')
（四）IN转换
1LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.ENAME IN ('lhr','DBA') ; 2-------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4-------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | 6|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 | 7-------------------------------------------------------------------------- 8 9Predicate Information (identified by operation id):10---------------------------------------------------1112 1 - filter("A"."ENAME"='DBA' OR "A"."ENAME"='lhr')SELECT * FROM SCOTT.EMP A WHERE A.ENAME IN ('lhr','DBA') ; 2-------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4-------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | 6|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 | 7-------------------------------------------------------------------------- 8 9Predicate Information (identified by operation id): 10--------------------------------------------------- 11 12 1 - filter("A"."ENAME"='DBA' OR "A"."ENAME"='lhr')
（五）BETWEEN AND转换
1LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO BETWEEN 1 AND 2; 2-------------------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4-------------------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 | 6| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 | 7|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | 8-------------------------------------------------------------------------------------- 910Predicate Information (identified by operation id):11---------------------------------------------------1213 2 - access("A"."EMPNO">=1 AND "A"."EMPNO"<=2)SELECT * FROM SCOTT.EMP A WHERE A.EMPNO BETWEEN 1 AND 2; 2-------------------------------------------------------------------------------------- 3| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 4-------------------------------------------------------------------------------------- 5| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 | 6| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 | 7|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | 8-------------------------------------------------------------------------------------- 9 10Predicate Information (identified by operation id): 11--------------------------------------------------- 12 13 2 - access("A"."EMPNO">=1 AND "A"."EMPNO"<=2)
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧
