Outer 조인문을 작성하면서 일부 조건절에 Outer 기호(+)를 빠뜨리면 Inner 조인할 때와 같은 결과가 나옴
이럴 때, 옵티마이저는 Outer조인을 Inner조인으로 바꾸는 쿼리변환을 시행함.
아래 Predicate 정보를 확인하기 바람
SQL> set autot on
SQL> select *
2 from emp e, dept d
3 where d.deptno(+) = e.deptno
4 and d.loc = 'DALLAS'
5 and e.sal >= 1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 319292506
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 285 | 3 (0)| 00:0
|* 1 | TABLE ACCESS BY INDEX ROWID | EMP | 5 | 185 | 1 (0)| 00:0
| 2 | NESTED LOOPS | | 5 | 285 | 3 (0)| 00:0
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:0
|* 4 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | | 1 (0)| 00:0
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 0 (0)| 00:0
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."SAL">=1000)
4 - access("D"."LOC"='DALLAS')
5 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1175 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
옵티마이저가 굳이 이런 쿼리 변화을 시행하는 이유는 조인 순서를 자유롭게 결정하기 위해서다.
Outer NL조인, Outer 소트 머지 조인 시 드라이빙 테이블은 항상 Outer 기호가 붙지 않은쪽으로 고정된다
Outer 해시 조인의 경우, 10g부터 자유롭게 조인순서가 바뀌도록 개선되었지만 9i까지는 해시 조인도 순서가 고정적이었다.
이처럼 조인 순서를 자유롭게 결정하지 못하는 것이 쿼리 최적화에 큰 걸림돌일 수 있다.
만약 위 쿼리에서 sal >= 1000 조건에부합하는 사원 레코드가 매우 많고, loc = 'DALLAS'조건에 부합하는 부서에 속한 사원이 매우 적다면
dept 테이블을 먼저 드라이빙하는 것이 유리하다
그럼에도 Outer조인 때문에 항상 emp테이블을 드라이빙해야한다면 불리한 조건에서 최적화하는 것이 된다.
SQL을 작성할때 불필요한 Outer조인을 삼가야 하는 이유가 여기에 있다
Outer 조인을 써야 하는 상황이라면 Outer기호를 정확히 구사해야 올바른 결과집합을 얻을수 있음에 유념하자
ANSI Outer 조인문일 때는 Outer기호 대신 조건걸 위치에 신경을 써야 한다.
Outer 조인에서 Inner쪽 테이블에 대한 필터조건을 아래처럼 where절에 기술한다면 Inner 조인할때와 같은 결과 집합을 얻게 된다.
따라서 옵티마이저가 Outer조인을 아예 Inner조인으로 변환해 버린다
SQL> select e.empno, e.deptno, e.sal, d.loc, d.dname, d.deptno
2 from dept d left outer join emp e on d.deptno = e.deptno
3 where e.sal > 1000;
EMPNO DEPTNO SAL LOC DNAME DEPTNO
---------- ---------- ---------- ------------- -------------- ----------
7876 20 1100 DALLAS RESEARCH 20
7521 30 1250 CHICAGO SALES 30
7654 30 1250 CHICAGO SALES 30
7934 10 1300 NEW YORK ACCOUNTING 10
7844 30 1500 CHICAGO SALES 30
7499 30 1600 CHICAGO SALES 30
7782 10 2450 NEW YORK ACCOUNTING 10
7698 30 2850 CHICAGO SALES 30
7566 20 2975 DALLAS RESEARCH 20
7788 20 3000 DALLAS RESEARCH 20
7902 20 3000 DALLAS RESEARCH 20
7839 10 5000 NEW YORK ACCOUNTING 10
12 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3582342135
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 403 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 13 | 403 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 143 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_SAL_IDX | 13 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."SAL">1000)
5 - access("D"."DEPTNO"="E"."DEPTNO")
제대로 된 Outer조인 결과집합을 얻으려면 sal > 1000조건을 아래와 같이 on절에 기술해 주어야 한다
SQL> select e.empno, e.deptno, e.sal, d.loc, d.dname, d.deptno
1 from dept d left outer join emp e on d.deptno = e.deptno and e.sal > 1000
EMPNO DEPTNO SAL LOC DNAME DEPTNO
---------- ---------- ---------- ------------- -------------- ----------
7782 10 2450 NEW YORK ACCOUNTING 10
7839 10 5000 NEW YORK ACCOUNTING 10
7934 10 1300 NEW YORK ACCOUNTING 10
7566 20 2975 DALLAS RESEARCH 20
7788 20 3000 DALLAS RESEARCH 20
7876 20 1100 DALLAS RESEARCH 20
7902 20 3000 DALLAS RESEARCH 20
7499 30 1600 CHICAGO SALES 30
7521 30 1250 CHICAGO SALES 30
7654 30 1250 CHICAGO SALES 30
7698 30 2850 CHICAGO SALES 30
7844 30 1500 CHICAGO SALES 30
BOSTON OPERATIONS 40
13 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1350698460
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 403 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 13 | 403 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 33 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."SAL"(+)>1000)
4 - access("D"."DEPTNO"="E"."DEPTNO"(+))
ANSI Outer 조인문에서 where절에 기술한 Inner쪽 필터 조건이 의미 있게 사용되는 경우는 아래처럼 is null 조건을 체크하는 경우뿐이며,
조인에 실패하는 레코드를 찾고자 할때 흔히 사용되는 SQL이다
SQL> select e.empno, e.deptno, e.sal, d.loc, d.dname, d.deptno
2 from dept d left outer join emp e on d.deptno = e.deptno
3 where e.empno is null
EMPNO DEPTNO SAL LOC DNAME DEPTNO
---------- ---------- ---------- ------------- -------------- ----------
BOSTON OPERATIONS 40
Execution Plan
----------------------------------------------------------
Plan hash value: 4106494745
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 434 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS OUTER | | 14 | 434 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 4 | 44 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."EMPNO" IS NULL)
5 - access("D"."DEPTNO"="E"."DEPTNO"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
715 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1 select e.empno, e.deptno, e.sal, d.loc, d.dname, d.deptno
2* from dept d left outer join emp e on d.deptno = e.deptno and e.empno is null
SQL> /
EMPNO DEPTNO SAL LOC DNAME DEPTNO
---------- ---------- ---------- ------------- -------------- ----------
NEW YORK ACCOUNTING 10
DALLAS RESEARCH 20
CHICAGO SALES 30
BOSTON OPERATIONS 40
Execution Plan
----------------------------------------------------------
Plan hash value: 1350698460
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 124 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 4 | 124 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."EMPNO"(+) IS NULL)
4 - access("D"."DEPTNO"="E"."DEPTNO"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
820 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
Outer쪽 필터조건은 on절에 기술하든 where절에 기술하든 결과집합이나 성능차이에 하등차이가 없다