[Oracle] 오라클 쿼리 변환 - 서브쿼리 Unnesting

쿼리 변환 (Query Transformation)

- 쿼리 옵티마이저가 SQL을 분석해 의미적으로 동일(같은 결과를 리턴)하면서도 더 나은 성능이 기대되는 형태로 재작성

① 휴리스틱 쿼리 변환

결과만 보장된다면 무조건 쿼리 변환 수행 (일종의 Rule-based 최적화 기법)

② 비용기반 쿼리 변환

변환된 쿼리의 비용이 더 낮을 때만 쿼리 변환 수행 

서브쿼리의 분류

- 인라인 뷰 (Inline View) : from 절에 나타나는 서브쿼리

- 중첩된 서브쿼리 (Nested Subquery) : 결과집합을 한정하기 위해 where 절에 사용된 서브쿼리

  * 서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를 '상관관계 있는 서브쿼리'라고 함

- 스칼라 서브쿼리 (Scalar Subquery) : 한 레코드당 정확히 하나의 컬럼 값만을 리턴, 주로 select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 컬럼이 올 수 있는 대부분 위치에서 사용 가능

서브쿼리 Unnesting의 의미

- 중첩된 서브쿼리를 풀어냄 (서브쿼리 No-Unnesting : 그대로 둠)

SELECT * FROM EMP A

WHERE EXISTS (

SELECT 'X' FROM DEPT

WHERE DEPTNO = A.DEPTNO

)

   AND SAL >

(SELECT AVG(SAL) FROM EMP B

 WHERE EXISTS (

SELECT 'X' FROM SALGRADE

WHERE B.SAL BETWEEN LOSAL AND HISAL

    AND GRADE = 4)

);

-- 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하며 필터링

-- 옵티마이저는 Unnesting을 하는 것이 최적일지 아닐지를 고민하고 선택

서브쿼리 Unnesting의 이점

- 서브쿼리를 메인쿼리와 같은 레벨로 풀어내면 다양한 access 경로와 JOIN 메소드를 평가할 수 있음

- 옵티마이저는 많은 JOIN 테크닉을 가지므로 JOIN 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높아짐

* 관련 힌트

① unnest : 서브쿼리를 Unnesting 함으로써 JOIN방식으로 최적화하도록 유도

② no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도

서브쿼리 Unnesting 기본 예시

SELECT * FROM EMP

WHERE DEPTNO IN (SELECT /*+ no_unnest */ DEPTNO FROM DEPT);

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

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

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

|   0 | SELECT STATEMENT   |         |     5 |   173 |     3   (0)| 00:00:01 |

|*  1 |  FILTER            |         |       |       |            |          |

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

|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE "DEPTNO"=:B1))

   3 - access("DEPTNO"=:B1)

SELECT * FROM EMP

WHERE DEPTNO IN (SELECT /*+ unnest */ DEPTNO FROM DEPT);

▼

SELECT *    -- leading 힌트를 이용해 드라이빙 테이블을 정할 수 있음

  FROM (SELECT DEPTNO FROM DEPT) A, EMP B

WHERE B.DEPTNO = A.DEPTNO;

▼

SELECT EMP.* FROM DEPT, EMP

WHERE EMP.DEPTNO = DEPT.DEPTNO;

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

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

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

|   0 | SELECT STATEMENT              |         |    10 |   350 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID  |         |     3 |    99 |     1   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |         |    10 |   350 |     2   (0)| 00:00:01 |

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

|*  4 |    INDEX RANGE SCAN           | PK_DEPT |     3 |       |     0   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   3 - access("DEPTNO"="DEPTNO")

서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때

SELECT * FROM DEPT

WHERE DEPTNO IN (SELECT DEPTNO FROM EMP);

-- EMP 테이블에서 DEPTNO는 non-unique

≠

SELECT *

  FROM  (SELECT DEPTNO FROM EMP) A, DEPT B

WHERE B.DEPTNO = A.DEPTNO;

-- M쪽 집합인 EMP 테이블 단위의 결과집합 생성, 결과 오류

- 이럴 경우 옵티마이저의 선택

① 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 sort unique 오퍼레이션을 수행, 1쪽 집합으로 만든 다음 JOIN

SELECT B.*

  FROM (SELECT /*+ no_merge */ DISTINCT DEPTNO FROM EMP ORDER BY DETPNO) A, DEPT B

WHERE B.DETPNO = A.DEPTNO;

② 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인 방식으로 JOIN

* 세미 조인 원리

for (i=0;; i++) {

for(j==0;; j++) {

if(i==j) break;    // JOIN 성공 시 break

}

}

SELECT /*+ leading(DEPT) */ * FROM DEPT

WHERE DEPTNO IN (SELECT /*+ unnest nl_sj */ DEPTNO FROM EMP);