【DB笔试面试620】在Oracle中，举例说明“集合操作关联转变（Set Join Conversion）”查询转换。
- 2019 年 10 月 10 日
- 筆記
题目部分
在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程序员面试笔试宝典》，作者：李华荣。
