【DB笔试面试617】在Oracle中，和“表达式和条件评估”相关的查询转换有哪些？
【DB笔试面试617】在Oracle中，和“表达式和条件评估”相关的查询转换有哪些？
小麦苗DBA宝典
发布于 2019-09-29 16:09:03
发布于 2019-09-29 16:09:03
题目部分
在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程序员面试笔试宝典》，作者：李华荣。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-08-12，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读