社区首页 >专栏 >【DB笔试面试618】在Oracle中，“OR扩展”可以有查询转换吗？

【DB笔试面试618】在Oracle中，“OR扩展”可以有查询转换吗？

AiDBA宝典

发布于 2019-09-29 08:09:42

6.7K00

代码可运行

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

运行总次数：0

代码可运行

♣

题目部分

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

♣

答案部分

同一字段：

LHR@orclasm > SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 2355049923

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."EMPNO"=1 OR "A"."EMPNO"=7369)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         23  recursive calls
          5  db block gets
          3  consistent gets
          0  physical reads
       1628  redo size
       1025  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LHR@orclasm > SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1; 

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 2259546459

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     2 |   174 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION               |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."EMPNO"=1)
   5 - access("A"."EMPNO"=7369)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         23  recursive calls
          4  db block gets
          3  consistent gets
          0  physical reads
       1560  redo size
       1021  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LHR@orclasm >

不同字段：

LHR@orclasm > SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA';

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| 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"."EMPNO"=7369 OR "A"."ENAME"='DBA')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         28  recursive calls
          4  db block gets
         16  consistent gets
          0  physical reads
       1544  redo size
       1021  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LHR@orclasm > SELECT  /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA';

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 2453891490

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     2 |   174 |     3   (0)| 00:00:01 |
|   1 |  CONCATENATION               |        |       |       |            |          |
|*  2 |   TABLE ACCESS FULL          | EMP    |     1 |    87 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."ENAME"='DBA')
   3 - filter(LNNVL("A"."ENAME"='DBA'))
   4 - access("A"."EMPNO"=7369)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         31  recursive calls
          4  db block gets
         25  consistent gets
          0  physical reads
       1560  redo size
       1021  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed