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