首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【DB笔试面试620】在Oracle中,举例说明“集合操作关联转变(Set Join Conversion)”查询转换。

【DB笔试面试620】在Oracle中,举例说明“集合操作关联转变(Set Join Conversion)”查询转换。

作者头像
小麦苗DBA宝典
发布2019-09-29 16:10:42
3930
发布2019-09-29 16:10:42
举报

题目部分

在Oracle中,举例说明“集合操作关联转变(Set Join Conversion)”查询转换。

答案部分

LHR@orclasm > SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO>7469
  2  MINUS
  3  SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO>7839;

Execution Plan
----------------------------------------------------------
Plan hash value: 3686975449

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    13 |  1566 |     8  (63)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |    13 |  1131 |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |    13 |  1131 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |      |     5 |   435 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP  |     5 |   435 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   3 - filter("A"."EMPNO">7469)
   5 - filter("A"."EMPNO">7839)

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


Statistics
----------------------------------------------------------
         61  recursive calls
          5  db block gets
         34  consistent gets
          0  physical reads
       2536  redo size
       1357  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          8  rows processed

LHR@orclasm > 
LHR@orclasm > ALTER SESSION SET "_CONVERT_SET_TO_JOIN"=TRUE; 

Session altered.

LHR@orclasm > 
LHR@orclasm > SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO>7469
  2  MINUS
  3  SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO>7839;

Execution Plan
----------------------------------------------------------
Plan hash value: 3353202012

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    13 |  2262 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |    13 |  2262 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |    13 |  1131 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     5 |   435 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("A"."EMPNO"="A"."EMPNO" AND
              SYS_OP_MAP_NONNULL("A"."ENAME")=SYS_OP_MAP_NONNULL("A"."ENAME") AND
              SYS_OP_MAP_NONNULL("A"."JOB")=SYS_OP_MAP_NONNULL("A"."JOB") AND
              SYS_OP_MAP_NONNULL("A"."MGR")=SYS_OP_MAP_NONNULL("A"."MGR") AND
              SYS_OP_MAP_NONNULL("A"."HIREDATE")=SYS_OP_MAP_NONNULL("A"."HIREDATE")
              AND SYS_OP_MAP_NONNULL("A"."SAL")=SYS_OP_MAP_NONNULL("A"."SAL") AND
              SYS_OP_MAP_NONNULL("A"."COMM")=SYS_OP_MAP_NONNULL("A"."COMM") AND
              SYS_OP_MAP_NONNULL("A"."DEPTNO")=SYS_OP_MAP_NONNULL("A"."DEPTNO"))
   2 - filter("A"."EMPNO">7469)
   3 - filter("A"."EMPNO">7839)

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


Statistics
----------------------------------------------------------
         61  recursive calls
          5  db block gets
         34  consistent gets
          0  physical reads
       2552  redo size
       1347  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)
          8  rows processed

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

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2019-08-18,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DB宝 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档