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

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

♣题目 部分

在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: 966064101112-----------------------------------------------------------------------------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-----------------------------------------------------------------------------192021Statistics22----------------------------------------------------------23         47  recursive calls24          5  db block gets25          1  consistent gets26          0  physical reads27       2616  redo size28        526  bytes sent via SQL*Net to client29        519  bytes received via SQL*Net from client30          2  SQL*Net roundtrips to/from client31          0  sorts (memory)32          0  sorts (disk)33          1  rows processedSELECT 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----------------------------------------------------------------------------------------1617Note18-----19   - dynamic sampling used for this statement (level=2)202122Statistics23----------------------------------------------------------24          6  recursive calls25          0  db block gets26          9  consistent gets27          1  physical reads28          0  redo size29       1710  bytes sent via SQL*Net to client30        552  bytes received via SQL*Net from client31          5  SQL*Net roundtrips to/from client32          0  sorts (memory)33          0  sorts (disk)34         58  rows processed3536ALTER TABLE  T_QC_20170613_LHR ADD PRIMARY KEY (USERNAME);3738LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T; 3940Execution Plan41----------------------------------------------------------42Plan hash value: 8848138324344---------------------------------------------------------------------------------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---------------------------------------------------------------------------------5051Note52-----53   - dynamic sampling used for this statement (level=2)545556Statistics57----------------------------------------------------------58         79  recursive calls59         28  db block gets60         83  consistent gets61          0  physical reads62          0  redo size63       1710  bytes sent via SQL*Net to client64        552  bytes received via SQL*Net from client65          5  SQL*Net roundtrips to/from client66          6  sorts (memory)67          0  sorts (disk)68         58  rows processedCREATE 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---------------------------------------------------1112   1 - filter("A"."DEPTNO" IS NOT NULL)SELECT  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---------------------------------------------------------------------------1112Predicate Information (identified by operation id):13---------------------------------------------------1415   1 - access("A"."DEPTNO"="B"."DEPTNO")16   3 - filter("A"."EMPNO"<=7521 OR "A"."EMPNO">=7782)1718LHR@orclasm > ALTER SESSION SET "_ELIMINATE_COMMON_SUBEXPR"=FALSE; 1920Session altered.2122LHR@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 );2324---------------------------------------------------------------------------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---------------------------------------------------------------------------3233Predicate Information (identified by operation id):34---------------------------------------------------3536   3 - filter("A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO"<=7521 OR37              "A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO">=7782)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)


上一篇:11gr2全外连接优化执行计划(二)


下一篇:istio安全模块之网格内流量与网关入站流量