oracle - 실행계획 SQL연산(NESTED LOOP)

실행계획 SQL연산(NESTED LOOP)

먼저 아래 SQL문장을 보도록 하죠…

SQL> SELECT E.ENAME,

                     D.DNAME

             FROM   EMP E, DEPT D

         WHERE  E.DEPTNO = D.DEPTNO

      

Execution Plan

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

SELECT STATEMENT Optimizer=CHOOSE

TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’

NESTED LOOPS

    TABLE ACCESS (FULL) OF ‘DEPT’

    INDEX (RANGE SCAN) OF ‘IDX_EMP_DEPTNO’ (NON UNIQUE)

이 경우 DEPT 테이블이 드라이빙 테이블이 되고 EMP 테이블은 비드라이빙 테이블이 되는데, 이는 DEPT 테이블을 FULL SCAN 하면서 로우 하나씩 꺼내 EMP 테이블에 DEPTNO가 같은 것이 있는지를 인덱스(IDX_EMP_DEPTNO)를 이용하여 검색 후 ROWID로 실제 Data를 추출한다는 것입니다.

이상과 같이 조인되는 컬럼 중에 적어도 하나에 인덱스가 존재하는 경우 나타나는 ROW 연산을 NESTED LOOP라고 합니다. 만약 조인되는 컬럼들에 대해 인덱스가 전혀 없으면 오라클의 경우 MERGE JOIN이나 HASH JOIN을 동반하는 실행계획을 세우게 됩니다. 아래의 예를 참고 하세요~ (아래는 인덱스 컬럼에 변형을 가해 인덱스가 사용되지 않습니다.)

SQL> SELECT    E.ENAME,

                       D.DNAME

         FROM   EMP E, DEPT D

         WHERE  RTRIM(E.DEPTNO) = RTRIM(D.DEPTNO)

Execution Plan

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

SELECT STATEMENT Optimizer=CHOOSE

SORT (ORDER BY)

MERGE JOIN 

  SORT(JOIN)

     TABLE ACCESS(FULL) OF ‘DEPT’

SORT(JOIN)

     TABLE ACCESS(FULL) OF ‘EMP’

이번엔 FROM절 다음의 테이블의 나타나는 순서에 대해 설명 드리면 RULE-BASES Optimizer에서는 FROM절에서 나중에 나타나는 테이블이 드라이빙 테이블이 됩니다. 아래의 /*+ RULE */ 이라는 힌트는 RULE-BASES Optimizer로 동작하라는 의미를 부여하는 것인데 테이블에 대해 통계 정보가 없고 OPTIMIZER_MODE 파라미터를 CHOOSE라고 Setting 했다면 저절로 RULE-BASED Optimizer로 동작하게 됩니다.

[테이블에 대해 통계 정보가 생성되지 않은 경우라고 가정, EMP 테이블의 DEPTNO, DEPT 테이블의 DEPTNO 컬럼에 인덱스가 있다고 가정]

SQL> SELECT /*+ RULE */ E.ENAME,

                     D.DNAME

             FROM   EMP E, DEPT D

         WHERE  E.DEPTNO = D.DEPTNO

      

Execution Plan

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

SELECT STATEMENT Optimizer=Hint:RULE

TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’

NESTED LOOPS

    TABLE ACCESS (FULL) OF ‘DEPT’

    INDEX (RANGE SCAN) OF ‘IDX_EMP_DEPTNO’ (NON UNIQUE)

위에서 FROM절에서 나중에 나타나는 DEPT 테이블이 드라이빙 테이블이 되는 것입니다. 주의할 점은 RULE-BASED Optimizer이지만 조인 컬럼중 한쪽만 인덱스가 존재 한다면 FROM절에 나타나는 테이블의 순서와 상관없이 INDEX가 없는 테이블이 드라이빙 테이블이 됩니다. 그래서 이 경우 테이블의 건수가 많은 테이블이 드라이빙 테이블로 잘못 선정되면 성능에 악영향을 줄 수 있습니다.