【DB笔试面试621】在Oracle中，举例说明“DISTINCT配置（Distinct Placement，DP）”查询转换。
- 2019 年 10 月 10 日
- 筆記
题目部分
在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
& 说明：
有关查询转换的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140618/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
