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

DB宝 2019-08-18

770

♣题目部分在Oracle中，举例说明“集合操作关联转变（Set Join Conversion）”查询转换。

     
♣答案部分

 1LHR@orclasm > SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO>7469
 2  2  MINUS
 3  3  SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO>7839;
 4
 5Execution Plan
 6----------------------------------------------------------
 7Plan hash value: 3686975449
 8
 9----------------------------------------------------------------------------
10| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
11----------------------------------------------------------------------------
12|   0 | SELECT STATEMENT    |      |    13 |  1566 |     8  (63)| 00:00:01 |
13|   1 |  MINUS              |      |       |       |            |          |
14|   2 |   SORT UNIQUE       |      |    13 |  1131 |     4  (25)| 00:00:01 |
15|*  3 |    TABLE ACCESS FULL| EMP  |    13 |  1131 |     3   (0)| 00:00:01 |
16|   4 |   SORT UNIQUE       |      |     5 |   435 |     4  (25)| 00:00:01 |
17|*  5 |    TABLE ACCESS FULL| EMP  |     5 |   435 |     3   (0)| 00:00:01 |
18----------------------------------------------------------------------------
19
20Predicate Information (identified by operation id):
21---------------------------------------------------
22
23   3 - filter("A"."EMPNO">7469)
24   5 - filter("A"."EMPNO">7839)
25
26Note
27-----
28   - dynamic sampling used for this statement (level=2)
29
30
31Statistics
32----------------------------------------------------------
33         61  recursive calls
34          5  db block gets
35         34  consistent gets
36          0  physical reads
37       2536  redo size
38       1357  bytes sent via SQL*Net to client
39        520  bytes received via SQL*Net from client
40          2  SQL*Net roundtrips to/from client
41          2  sorts (memory)
42          0  sorts (disk)
43          8  rows processed
44
45LHR@orclasm > 
46LHR@orclasm > ALTER SESSION SET "_CONVERT_SET_TO_JOIN"=TRUE; 
47
48Session altered.
49
50LHR@orclasm > 
51LHR@orclasm > SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO>7469
52  2  MINUS
53  3  SELECT  * FROM SCOTT.EMP A WHERE A.EMPNO>7839;
54
55Execution Plan
56----------------------------------------------------------
57Plan hash value: 3353202012
58
59---------------------------------------------------------------------------
60| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
61---------------------------------------------------------------------------
62|   0 | SELECT STATEMENT   |      |    13 |  2262 |     7  (15)| 00:00:01 |
63|*  1 |  HASH JOIN ANTI    |      |    13 |  2262 |     7  (15)| 00:00:01 |
64|*  2 |   TABLE ACCESS FULL| EMP  |    13 |  1131 |     3   (0)| 00:00:01 |
65|*  3 |   TABLE ACCESS FULL| EMP  |     5 |   435 |     3   (0)| 00:00:01 |
66---------------------------------------------------------------------------
67
68Predicate Information (identified by operation id):
69---------------------------------------------------
70
71   1 - access("A"."EMPNO"="A"."EMPNO" AND
72              SYS_OP_MAP_NONNULL("A"."ENAME")=SYS_OP_MAP_NONNULL("A"."ENAME") AND
73              SYS_OP_MAP_NONNULL("A"."JOB")=SYS_OP_MAP_NONNULL("A"."JOB") AND
74              SYS_OP_MAP_NONNULL("A"."MGR")=SYS_OP_MAP_NONNULL("A"."MGR") AND
75              SYS_OP_MAP_NONNULL("A"."HIREDATE")=SYS_OP_MAP_NONNULL("A"."HIREDATE")
76              AND SYS_OP_MAP_NONNULL("A"."SAL")=SYS_OP_MAP_NONNULL("A"."SAL") AND
77              SYS_OP_MAP_NONNULL("A"."COMM")=SYS_OP_MAP_NONNULL("A"."COMM") AND
78              SYS_OP_MAP_NONNULL("A"."DEPTNO")=SYS_OP_MAP_NONNULL("A"."DEPTNO"))
79   2 - filter("A"."EMPNO">7469)
80   3 - filter("A"."EMPNO">7839)
81
82Note
83-----
84   - dynamic sampling used for this statement (level=2)
85
86
87Statistics
88----------------------------------------------------------
89         61  recursive calls
90          5  db block gets
91         34  consistent gets
92          0  physical reads
93       2552  redo size
94       1347  bytes sent via SQL*Net to client
95        520  bytes received via SQL*Net from client
96          2  SQL*Net roundtrips to/from client
97          0  sorts (memory)
98          0  sorts (disk)
99          8  rows processed


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