【DB笔试面试616】在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)

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)