oracle左连接等价改写,[原]关于left join 和rigt join 的理解实验

explain plan for select emp.empno,emp.ename,dept.deptno,dept.dname

from emp

full join dept on dept.deptno=emp.deptno;

select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

Plan hash value: 51889263

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 15 | 630 | 6 (0)| 00:00:01 |

| 1 | VIEW | VW_FOJ_0 | 15 | 630 | 6 (0)| 00:00:01 |

|* 2 | HASH JOIN FULL OUTER| | 15 | 390 | 6 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

16 rows selected.

explain plan for select emp.empno,emp.ename,dept.deptno,dept.dname

from emp

left join dept on dept.deptno = emp.deptno

union all

select emp.empno,emp.ename,dept.deptno,dept.dname

from emp

right join dept on dept.deptno = emp.deptno

where emp.empno is null;

select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------

Plan hash value: 315464403

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 15 | 390 | 12 (9)| 00:00:01 |

| 1 | UNION-ALL | | | | | |

|* 2 | HASH JOIN OUTER | | 14 | 364 | 6 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |

|* 5 | FILTER | | | | | |

| 6 | MERGE JOIN OUTER | | 1 | 26 | 6 (17)| 00:00:01 |

| 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |

| 8 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 9 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |

| 10 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("DEPT"."DEPTNO"(+)="EMP"."DEPTNO")

5 - filter("EMP"."EMPNO" IS NULL)

9 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))

filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))

25 rows selected.