【DB笔试面试617】在Oracle中，和“表达式和条件评估”相关的查询转换有哪些？
- 2019 年 10 月 10 日
- 筆記
题目部分
在Oracle中，和“表达式和条件评估”相关的查询转换有哪些？
♣
答案部分
（一）逻辑转换
LHR@orclasm > SELECT /*+FULL(A) FULL(B)*/ * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND B.DEPTNO=20; Execution Plan ---------------------------------------------------------- Plan hash value: 4192419542 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 468 | 5 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 4 | 468 | 5 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| DEPT | 1 | 30 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 5 | 435 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("B"."DEPTNO"=20) 3 - filter("A"."DEPTNO"=20)
（二）常量转换
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE a.sal>=100+50; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"."SAL">=150)
（三）LIKE转换
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.ENAME LIKE 'lhr' ; -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"."ENAME"='lhr')
（四）IN转换
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.ENAME IN ('lhr','DBA') ; -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"."ENAME"='DBA' OR "A"."ENAME"='lhr')
（五）BETWEEN AND转换
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO BETWEEN 1 AND 2; -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."EMPNO">=1 AND "A"."EMPNO"<=2)
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
