题目部分
在Oracle中,和“消除”相关的查询转换有哪些?
♣
答案部分
(一)排序消除
LHR@orclasm > SELECT COUNT(1) FROM ( SELECT T.EMPNO FROM SCOTT.EMP T ORDER BY T.EMPNO);
COUNT(1)
----------
14
Execution Plan
----------------------------------------------------------
Plan hash value: 96606410
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_FULL_EMP_LHR | 14 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
47 recursive calls
5 db block gets
1 consistent gets
0 physical reads
2616 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
(二)去重消除
CREATE TABLE T_QC_20170613_LHR AS SELECT * FROM DBA_USERS;
LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T;
Execution Plan
----------------------------------------------------------
Plan hash value: 1708573004
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58 | 986 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 58 | 986 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T_QC_20170613_LHR | 58 | 986 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
1710 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
58 rows processed
ALTER TABLE T_QC_20170613_LHR ADD PRIMARY KEY (USERNAME);
LHR@orclasm > SELECT DISTINCT T.USERNAME FROM T_QC_20170613_LHR T;
Execution Plan
----------------------------------------------------------
Plan hash value: 884813832
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58 | 986 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | SYS_C0089569 | 58 | 986 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
79 recursive calls
28 db block gets
83 consistent gets
0 physical reads
0 redo size
1710 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
58 rows processed
(三)表消除
SELECT A.* FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."DEPTNO" IS NOT NULL)
(四)公共子表达式消除(Common Sub-expression Elimination,CSE)
LHR@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 );
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1287 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 11 | 1287 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 11 | 957 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
3 - filter("A"."EMPNO"<=7521 OR "A"."EMPNO">=7782)
LHR@orclasm > ALTER SESSION SET "_ELIMINATE_COMMON_SUBEXPR"=FALSE;
Session altered.
LHR@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 );
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 117 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO"<=7521 OR
"A"."DEPTNO"="B"."DEPTNO" AND "A"."EMPNO">=7782)
本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。