题目部分
在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程序员面试笔试宝典》,作者:李华荣。