♣题目 部分
在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)