在Oracle中，和“表达式和条件评估”相关的查询转换有哪些？

DB宝 2019-08-12

588

♣题目部分在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---------------------------------------------------------------------------
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--------------------------------------------------------------------------
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---------------------------------------------------
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---------------------------------------------------
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--------------------------------------------------------------------------------------
 9
10Predicate Information (identified by operation id):
11---------------------------------------------------
12
13   2 - access("A"."EMPNO">=1 AND "A"."EMPNO"<=2)


本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。