【DB筆試面試619】在Oracle中，舉例說明「連接因式分解（Join factorization，JF）」查詢轉換。
- 2019 年 10 月 10 日
- 筆記
題目部分
在Oracle中，舉例說明「連接因式分解（Join factorization，JF）」查詢轉換。
♣
答案部分
LHR@orclasm > SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=6 2 UNION ALL 3 SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=7; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1245103347 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 244 | 8 (13)| 00:00:01 | |* 1 | HASH JOIN | | 2 | 244 | 8 (13)| 00:00:01 | | 2 | VIEW | VW_JF_SET$623BBB07 | 2 | 200 | 4 (0)| 00:00:01 | | 3 | UNION-ALL | | | | | | |* 4 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 | | 6 | TABLE ACCESS FULL | DEPT | 4 | 88 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ITEM_1"="B"."DEPTNO") 4 - filter("A"."EMPNO"=6) 5 - filter("A"."EMPNO"=7) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 99 recursive calls 7 db block gets 32 consistent gets 0 physical reads 4536 redo size 866 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed LHR@orclasm > ALTER SESSION SET "_OPTIMIZER_JOIN_FACTORIZATION"=FALSE; Session altered. LHR@orclasm > SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=6 2 UNION ALL 3 SELECT /*+FULL(A) FULL(B)*/ A.*,B.DNAME FROM SCOTT.EMP A,SCOTT.DEPT B WHERE A.DEPTNO=B.DEPTNO AND A.EMPNO=7; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2703228680 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 218 | 8 (50)| 00:00:01 | | 1 | UNION-ALL | | | | | | | 2 | NESTED LOOPS | | 1 | 109 | 4 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| DEPT | 82 | 1804 | 2 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 109 | 4 (0)| 00:00:01 | |* 6 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 | |* 7 | TABLE ACCESS FULL| DEPT | 82 | 1804 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("A"."EMPNO"=6) 4 - filter("A"."DEPTNO"="B"."DEPTNO") 6 - filter("A"."EMPNO"=7) 7 - filter("A"."DEPTNO"="B"."DEPTNO") Statistics ---------------------------------------------------------- 93 recursive calls 7 db block gets 14 consistent gets 0 physical reads 4536 redo size 866 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。
