【DB笔试面试621】在Oracle中，举例说明“DISTINCT配置（Distinct Placement，DP）”查询转换。

LHR@orclasm > SELECT  /*+FULL(A) FULL(B)*/ DISTINCT  A.ENAME,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO ;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2962452962

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   588 |     8  (25)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |    14 |   588 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   588 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |   280 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPTNO"="B"."DEPTNO")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         46  recursive calls
          6  db block gets
         14  consistent gets
          0  physical reads
       2620  redo size
        870  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

LHR@orclasm > SELECT  /*+FULL(A) FULL(B) PLACE_DISTINCT*/ DISTINCT  A.ENAME,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO ;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3633957927

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |    14 |   588 |     9  (34)| 00:00:01 |
|   1 |  HASH UNIQUE          |                 |    14 |   588 |     9  (34)| 00:00:01 |
|*  2 |   HASH JOIN           |                 |    14 |   588 |     8  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPT            |     4 |    88 |     3   (0)| 00:00:01 |
|   4 |    VIEW               | VW_DTP_377C5901 |    14 |   280 |     4  (25)| 00:00:01 |
|   5 |     HASH UNIQUE       |                 |    14 |   280 |     4  (25)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| EMP             |    14 |   280 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="B"."DEPTNO")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         59  recursive calls
          5  db block gets
         48  consistent gets
          0  physical reads
       2552  redo size
        873  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed