在Oracle中，“OR扩展”可以有查询转换吗？

DB宝 2019-08-14

504

♣题目部分在Oracle中，“OR扩展”可以有查询转换吗？

     
♣答案部分


同一字段：
 1LHR@orclasm > SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1;
 2
 3     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
 4---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
 5      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
 6
 7
 8Execution Plan
 9----------------------------------------------------------
10Plan hash value: 2355049923
11
12---------------------------------------------------------------------------------------
13| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
14---------------------------------------------------------------------------------------
15|   0 | SELECT STATEMENT             |        |     1 |    87 |     2   (0)| 00:00:01 |
16|   1 |  INLIST ITERATOR             |        |       |       |            |          |
17|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     2   (0)| 00:00:01 |
18|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     3   (0)| 00:00:01 |
19---------------------------------------------------------------------------------------
20
21Predicate Information (identified by operation id):
22---------------------------------------------------
23
24   3 - access("A"."EMPNO"=1 OR "A"."EMPNO"=7369)
25
26Note
27-----
28   - dynamic sampling used for this statement (level=2)
29
30
31Statistics
32----------------------------------------------------------
33         23  recursive calls
34          5  db block gets
35          3  consistent gets
36          0  physical reads
37       1628  redo size
38       1025  bytes sent via SQL*Net to client
39        520  bytes received via SQL*Net from client
40          2  SQL*Net roundtrips to/from client
41          0  sorts (memory)
42          0  sorts (disk)
43          1  rows processed
44
45LHR@orclasm > SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1; 
46
47     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
48---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
49      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
50
51
52Execution Plan
53----------------------------------------------------------
54Plan hash value: 2259546459
55
56---------------------------------------------------------------------------------------
57| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
58---------------------------------------------------------------------------------------
59|   0 | SELECT STATEMENT             |        |     2 |   174 |     2   (0)| 00:00:01 |
60|   1 |  CONCATENATION               |        |       |       |            |          |
61|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
62|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
63|   4 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
64|*  5 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
65---------------------------------------------------------------------------------------
66
67Predicate Information (identified by operation id):
68---------------------------------------------------
69
70   3 - access("A"."EMPNO"=1)
71   5 - access("A"."EMPNO"=7369)
72
73Note
74-----
75   - dynamic sampling used for this statement (level=2)
76
77
78Statistics
79----------------------------------------------------------
80         23  recursive calls
81          4  db block gets
82          3  consistent gets
83          0  physical reads
84       1560  redo size
85       1021  bytes sent via SQL*Net to client
86        520  bytes received via SQL*Net from client
87          2  SQL*Net roundtrips to/from client
88          0  sorts (memory)
89          0  sorts (disk)
90          1  rows processed
91
92LHR@orclasm > 

复制

不同字段：
 1LHR@orclasm > SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA';
 2
 3     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
 4---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
 5      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
 6
 7
 8Execution Plan
 9----------------------------------------------------------
10Plan hash value: 3956160932
11
12--------------------------------------------------------------------------
13| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
14--------------------------------------------------------------------------
15|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |
16|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |
17--------------------------------------------------------------------------
18
19Predicate Information (identified by operation id):
20---------------------------------------------------
21
22   1 - filter("A"."EMPNO"=7369 OR "A"."ENAME"='DBA')
23
24Note
25-----
26   - dynamic sampling used for this statement (level=2)
27
28
29Statistics
30----------------------------------------------------------
31         28  recursive calls
32          4  db block gets
33         16  consistent gets
34          0  physical reads
35       1544  redo size
36       1021  bytes sent via SQL*Net to client
37        520  bytes received via SQL*Net from client
38          2  SQL*Net roundtrips to/from client
39          0  sorts (memory)
40          0  sorts (disk)
41          1  rows processed
42
43LHR@orclasm > SELECT  /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA';
44
45     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
46---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
47      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
48
49
50Execution Plan
51----------------------------------------------------------
52Plan hash value: 2453891490
53
54---------------------------------------------------------------------------------------
55| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
56---------------------------------------------------------------------------------------
57|   0 | SELECT STATEMENT             |        |     2 |   174 |     3   (0)| 00:00:01 |
58|   1 |  CONCATENATION               |        |       |       |            |          |
59|*  2 |   TABLE ACCESS FULL          | EMP    |     1 |    87 |     2   (0)| 00:00:01 |
60|*  3 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
61|*  4 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
62---------------------------------------------------------------------------------------
63
64Predicate Information (identified by operation id):
65---------------------------------------------------
66
67   2 - filter("A"."ENAME"='DBA')
68   3 - filter(LNNVL("A"."ENAME"='DBA'))
69   4 - access("A"."EMPNO"=7369)
70
71Note
72-----
73   - dynamic sampling used for this statement (level=2)
74
75
76Statistics
77----------------------------------------------------------
78         31  recursive calls
79          4  db block gets
80         25  consistent gets
81          0  physical reads
82       1560  redo size
83       1021  bytes sent via SQL*Net to client
84        520  bytes received via SQL*Net from client
85          2  SQL*Net roundtrips to/from client
86          0  sorts (memory)
87          0  sorts (disk)
88          1  rows processed

复制

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