专栏首页小麦苗的DB宝专栏【DB笔试面试618】在Oracle中,“OR扩展”可以有查询转换吗?

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

题目部分

在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

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

本文分享自微信公众号 - DB宝(xiaomaimiaolhr),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-08-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试615】在Oracle中,和谓词相关的查询转换有哪些?

    小麦苗DBA宝典
  • 【DB笔试面试589】在Oracle中,什么是半连接(Semi Join)?

    半连接(Semi Join)是一种特殊的连接类型,当做子查询展开时,Oracle经常会把那些外部WHERE条件为EXISTS、IN或= ANY的子查询转换为对应...

    小麦苗DBA宝典
  • 【DB笔试面试614】在Oracle中,和视图相关的查询转换有哪些?

    小麦苗DBA宝典
  • 【DB笔试面试615】在Oracle中,和谓词相关的查询转换有哪些?

    小麦苗DBA宝典
  • 用NBitcoin进行区块链开发(6):交易签名

    比特币交易的签名过程是所有环节中最复杂的步骤之一,下面两篇文章对这个过程有详细的描述。

    申龙斌
  • Bochspwn漏洞挖掘技术深究(1):Double Fetches 检测

    虽然现在技术文章很少人看,大家都喜欢聊安全八卦,但技术文章输出是一种很好的学习方式。更重要的是,专业的文章是给专业的人看的,并非为了取悦所有人。

    泉哥
  • 数据迁移前的准备和系统检查 (r2笔记70天)

    关于数据迁移,在之前也讨论过一些需要注意的地方,可能林林总总列了不少,都是在数据迁移迁移前和迁移时需要注意的。 http://blog.itpub.net/23...

    jeanron100
  • Windows Server 2003 组策略报错:无法读取模版信息

    今日帮客户修改管理员账号,进本地策略--->安全策略时提示这个错误信息,现总结了一下,希望可以帮助大家。问题出现原因:组策略出现“windows无法读取模板信...

    魏晓蕾
  • Linux内存管理 (22)内存检测技术(slub_debug/kmemleak/kasan)【转】

    转自:https://www.cnblogs.com/arnoldlu/p/8568090.html

    用户3033338
  • AkShare-宏观经济-中国LPR数据

    LPR数据对大家都有非常重大的影响,尤其是有房贷的各位小伙伴,特提供 LPR 数据接口,欢迎大家使用。

    AkShare

扫码关注云+社区

领取腾讯云代金券