by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

9. Outer 조인을 Inner 조인으로 변환




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절에 기술하든 결과집합이나 성능차이에 하등차이가 없다

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.