专栏首页小麦苗的DB宝专栏【DB笔试面试616】在Oracle中,和“消除”相关的查询转换有哪些?

【DB笔试面试616】在Oracle中,和“消除”相关的查询转换有哪些?

题目部分

在Oracle中,和“消除”相关的查询转换有哪些?

答案部分

(一)排序消除

LHR@orclasm > SELECT COUNT(1) FROM ( SELECT T.EMPNO FROM SCOTT.EMP T ORDER BY T.EMPNO);

  COUNT(1)
----------
        14


Execution Plan
----------------------------------------------------------
Plan hash value: 96606410

-----------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |                  |     1 |            |          |
|   2 |   INDEX FULL SCAN| IDX_FULL_EMP_LHR |    14 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         47  recursive calls
          5  db block gets
          1  consistent gets
          0  physical reads
       2616  redo size
        526  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

(二)去重消除

CREATE TABLE T_QC_20170613_LHR AS SELECT * FROM DBA_USERS;

LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T;

Execution Plan
----------------------------------------------------------
Plan hash value: 1708573004

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    58 |   986 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE       |                   |    58 |   986 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T_QC_20170613_LHR |    58 |   986 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
          9  consistent gets
          1  physical reads
          0  redo size
       1710  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         58  rows processed

ALTER TABLE  T_QC_20170613_LHR ADD PRIMARY KEY (USERNAME);

LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T; 

Execution Plan
----------------------------------------------------------
Plan hash value: 884813832

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |    58 |   986 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | SYS_C0089569 |    58 |   986 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
         79  recursive calls
         28  db block gets
         83  consistent gets
          0  physical reads
          0  redo size
       1710  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         58  rows processed

(三)表消除

SELECT  A.* FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |  1218 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("A"."DEPTNO" IS NOT NULL)

(四)公共子表达式消除(Common Sub-expression Elimination,CSE)

LHR@orclasm > SELECT  * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE ( A.DEPTNO=B.DEPTNO AND A.EMPNO<=7521) OR ( A.DEPTNO=B.DEPTNO AND A.EMPNO>=7782 );

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    11 |  1287 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    11 |  1287 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    11 |   957 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."DEPTNO"="B"."DEPTNO")
   3 - filter("A"."EMPNO"<=7521 OR "A"."EMPNO">=7782)

LHR@orclasm > ALTER SESSION SET "_ELIMINATE_COMMON_SUBEXPR"=FALSE; 

Session altered.

LHR@orclasm > SELECT  * FROM SCOTT.EMP A,SCOTT.DEPT B WHERE ( A.DEPTNO=B.DEPTNO AND A.EMPNO<=7521) OR ( A.DEPTNO=B.DEPTNO AND A.EMPNO>=7782 );

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   117 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |     1 |   117 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     1 |    87 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - filter("A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO"<=7521 OR
              "A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO">=7782)

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

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

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

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试589】在Oracle中,什么是半连接(Semi Join)?

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

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

    小麦苗DBA宝典
  • 【DB笔试面试618】在Oracle中,“OR扩展”可以有查询转换吗?

    小麦苗DBA宝典
  • Bochspwn漏洞挖掘技术深究(1):Double Fetches 检测

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

    泉哥
  • MySQL实战二:多种查询方案

    MySQL学习仓库Up-Up-MySQL,这是一个学习MySQL从入门实战到理论完善,再到精通的一个仓库,后面会把MySQL的学习资料上传上去!欢迎大家star...

    公众号guangcity
  • 利用WPAD/PAC与JScript实现Windows 10远程代码执行

    简介 Project Zero团队在google发表了一篇关于利用WPAD/PAC和JScript在本地网络中实现Windows10远程代码执行的博客,笔者根据...

    安恒信息
  • 一步步利用Linux kernel漏洞<1a/4>

    上一篇(第一篇)介绍了很多关于Linux内核漏洞CVE-2017-11176一些背景知识,本篇再介绍一点基础知识,不然后面就完全看不懂了,之后再想办法触发利用它...

    jeff xie
  • 手搓一个分布式大气监测系统(三)使用 Nucleo LoRa 开发套件打造 PM2.5 监测终端

    鹅民大气监测项目前期已搭建了5个种子监测点,接下去将吸引更多的志愿者来搭建终端,接入系统,一起搭建监测网络。从二期开始,大气监测项目组的同事优选了几款开发套件完...

    twowinter
  • 【转】 快捷方式lnk文件格式详解(英文)(中文)

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

    小麦苗DBA宝典

扫码关注云+社区

领取腾讯云代金券