题目部分
在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程序员面试笔试宝典》,作者:李华荣。