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

DB宝 2019-08-11

569

♣题目部分在Oracle中，和“消除”相关的查询转换有哪些？

     
♣答案部分


（一）排序消除
 1LHR@orclasm > SELECT COUNT(1) FROM ( SELECT T.EMPNO FROM SCOTT.EMP T ORDER BY T.EMPNO);
 2
 3  COUNT(1)
 4----------
 5        14
 6
 7
 8Execution Plan
 9----------------------------------------------------------
10Plan hash value: 96606410
11
12-----------------------------------------------------------------------------
13| Id  | Operation        | Name             | Rows  | Cost (%CPU)| Time     |
14-----------------------------------------------------------------------------
15|   0 | SELECT STATEMENT |                  |     1 |     1   (0)| 00:00:01 |
16|   1 |  SORT AGGREGATE  |                  |     1 |            |          |
17|   2 |   INDEX FULL SCAN| IDX_FULL_EMP_LHR |    14 |     1   (0)| 00:00:01 |
18-----------------------------------------------------------------------------
19
20
21Statistics
22----------------------------------------------------------
23         47  recursive calls
24          5  db block gets
25          1  consistent gets
26          0  physical reads
27       2616  redo size
28        526  bytes sent via SQL*Net to client
29        519  bytes received via SQL*Net from client
30          2  SQL*Net roundtrips to/from client
31          0  sorts (memory)
32          0  sorts (disk)
33          1  rows processed


（二）去重消除
 1CREATE TABLE T_QC_20170613_LHR AS SELECT * FROM DBA_USERS;
 2
 3LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T;
 4
 5Execution Plan
 6----------------------------------------------------------
 7Plan hash value: 1708573004
 8
 9----------------------------------------------------------------------------------------
10| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
11----------------------------------------------------------------------------------------
12|   0 | SELECT STATEMENT   |                   |    58 |   986 |     4  (25)| 00:00:01 |
13|   1 |  HASH UNIQUE       |                   |    58 |   986 |     4  (25)| 00:00:01 |
14|   2 |   TABLE ACCESS FULL| T_QC_20170613_LHR |    58 |   986 |     3   (0)| 00:00:01 |
15----------------------------------------------------------------------------------------
16
17Note
18-----
19   - dynamic sampling used for this statement (level=2)
20
21
22Statistics
23----------------------------------------------------------
24          6  recursive calls
25          0  db block gets
26          9  consistent gets
27          1  physical reads
28          0  redo size
29       1710  bytes sent via SQL*Net to client
30        552  bytes received via SQL*Net from client
31          5  SQL*Net roundtrips to/from client
32          0  sorts (memory)
33          0  sorts (disk)
34         58  rows processed
35
36ALTER TABLE  T_QC_20170613_LHR ADD PRIMARY KEY (USERNAME);
37
38LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T; 
39
40Execution Plan
41----------------------------------------------------------
42Plan hash value: 884813832
43
44---------------------------------------------------------------------------------
45| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
46---------------------------------------------------------------------------------
47|   0 | SELECT STATEMENT |              |    58 |   986 |     1   (0)| 00:00:01 |
48|   1 |  INDEX FULL SCAN | SYS_C0089569 |    58 |   986 |     1   (0)| 00:00:01 |
49---------------------------------------------------------------------------------
50
51Note
52-----
53   - dynamic sampling used for this statement (level=2)
54
55
56Statistics
57----------------------------------------------------------
58         79  recursive calls
59         28  db block gets
60         83  consistent gets
61          0  physical reads
62          0  redo size
63       1710  bytes sent via SQL*Net to client
64        552  bytes received via SQL*Net from client
65          5  SQL*Net roundtrips to/from client
66          6  sorts (memory)
67          0  sorts (disk)
68         58  rows processed



（三）表消除

 1SELECT  A.* FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO;
 2--------------------------------------------------------------------------
 3| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 4--------------------------------------------------------------------------
 5|   0 | SELECT STATEMENT  |      |    14 |  1218 |     3   (0)| 00:00:01 |
 6|*  1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
 7--------------------------------------------------------------------------
 8
 9Predicate Information (identified by operation id):
10---------------------------------------------------
11
12   1 - filter("A"."DEPTNO" IS NOT NULL)



（四）公共子表达式消除（Common Sub-expression Elimination，CSE）
 1LHR@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 );
 2
 3---------------------------------------------------------------------------
 4| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 5---------------------------------------------------------------------------
 6|   0 | SELECT STATEMENT   |      |    11 |  1287 |     7  (15)| 00:00:01 |
 7|*  1 |  HASH JOIN         |      |    11 |  1287 |     7  (15)| 00:00:01 |
 8|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
 9|*  3 |   TABLE ACCESS FULL| EMP  |    11 |   957 |     3   (0)| 00:00:01 |
10---------------------------------------------------------------------------
11
12Predicate Information (identified by operation id):
13---------------------------------------------------
14
15   1 - access("A"."DEPTNO"="B"."DEPTNO")
16   3 - filter("A"."EMPNO"<=7521 OR "A"."EMPNO">=7782)
17
18LHR@orclasm > ALTER SESSION SET "_ELIMINATE_COMMON_SUBEXPR"=FALSE; 
19
20Session altered.
21
22LHR@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 );
23
24---------------------------------------------------------------------------
25| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
26---------------------------------------------------------------------------
27|   0 | SELECT STATEMENT   |      |     1 |   117 |    10   (0)| 00:00:01 |
28|   1 |  NESTED LOOPS      |      |     1 |   117 |    10   (0)| 00:00:01 |
29|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
30|*  3 |   TABLE ACCESS FULL| EMP  |     1 |    87 |     2   (0)| 00:00:01 |
31---------------------------------------------------------------------------
32
33Predicate Information (identified by operation id):
34---------------------------------------------------
35
36   3 - filter("A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO"<=7521 OR
37              "A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO">=7782)


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