오라클 옵티마이저 힌트 - 쿼리블록

옵티마이저 힌트는 쿼리 변환(Query Transformation)을 고려해서 사용해야 합니다. 특히, 쿼리 변환을 통해 새로운 쿼리 블록이 생기거나 여러 쿼리 블록이 병합되는지 확인해야 합니다.

옵티마이저 힌트에 문제가 없는데 무시되고 있다면 쿼리 변환 때문에 의미가 맞지 않는 힌트일 가능성이 높습니다.

이 글에서는 쿼리 블록이 변환되면서 힌트에 문제가 생기는 사례를 소개합니다.

쿼리 블록이란

옵티마이저 힌트는 쿼리 블록 단위로 동작합니다. 쿼리 블록은 SQL을 구성하는 기본 단위입니다. 간단히 구분하는 방법은 인라인 뷰, 서브쿼리, 스칼라 서브쿼리가 각각 쿼리 블록이라고 보면 됩니다.

아래의 [1]번처럼 메인 쿼리 블록 한 개로만 이루어진 쿼리도 있고, [2]번처럼 여러 개의 쿼리 블록을 사용하는 쿼리도 있습니다.

-- [1] 쿼리 블록 1개
SELECT *
FROM   EMP E, DEPT D
WHERE  E.DEPTNO= D.DEPTNO

-- [2] 쿼리 블록 3개
SELECT E.EMPNO,                            --> 쿼리 블록 1
       E.EMPNM,
      (SELECT JOBNM                        --> 쿼리 블록 2
       FROM   JOB J                         
       WHERE  J.JOBCD = E.JOBCD ) JOBNM    
FROM   EMP E
WHERE  E.DEPTNO IN (SELECT D.DEPTNO        --> 쿼리 블록 3
                    FROM   DEPT D)

쿼리 블록은 키워드로도 식별할 수 있습니다. 메인 쿼리 블록에 포함된 서브 쿼리 블록은 괄호 내부에 아래 키워드로 시작합니다. 옵티마이저 힌트는 각각의 쿼리 블록마다 이 키워드 바로 뒤에 /*+ */ 주석 형태로 사용할 수 있습니다.

SELECT
INSERT
UPDATE
DELETE
MERGE

쿼리 블록 변환

옵티마이저는 최적화 단계에서 아래 [3]번 쿼리를 [4]번 쿼리처럼 변환할 수도 있습니다. 그러면 [3]번에서 사용한 /*+ LEADING(A B) */ 힌트는 어떻게 될까요? 변환한 [4]번 쿼리에는 별칭 B 테이블이 없으니 이 힌트는 더 이상 의미가 없습니다. 옵티마이저는 이런 힌트는 사용하지 않고 무시합니다.

옵티마이저가 [3]번 쿼리의 인라인 뷰를 변환하지 않고 그대로 사용할 수도 있습니다. 이 때는 실행 계획을 생성 할 때 /*+ LEADING(A B) */ 힌트를 정상적으로 사용합니다.

-- [3] 뷰 머징 쿼리 변환 전
SELECT /*+ LEADING(A B) */ *
FROM   CUST A,
      (SELECT X.*, Y.PROD_NO
       FROM   ORD X, ORD_DTL Y
       WHERE  X.ORDNO = Y.ORDNO) B
WHERE  A.CUST_NO = B.CUST_NO;

-- [4] 뷰 머징 쿼리 변환 후
SELECT /*+ LEADING(A B) */ *
FROM   CUST A, ORD X, ORD_DTL Y
WHERE  A.CUST_NO = X.CUST_NO
AND    X.ORDNO   = Y.ORDNO

일반적으로 사용자는 쿼리 변환이 발생할지 안 할지 보장할 수 없습니다. 옵티마이저가 쿼리 최적화에 필요한 요소들을 종합적으로 판단해 결정합니다.

일단 옵티마이저 힌트를 사용하기로 했다면 완전하게 빈틈 없이 사용하는 것이 좋습니다. 가능하면 쿼리 변환 여부도 직접 힌트를 통해 제어하고, 다른 힌트와 충돌하지 않도록 하는 것이 가장 좋습니다.

[5]번 쿼리처럼 쿼리 변환 관련 힌트와 다른 힌트를 함께 사용하면 보다 완전하게 힌트를 사용할 수 있습니다.

-- [5] 
SELECT /*+ MERGE(B) LEADING(X Y A) */
       *
FROM   CUST A,
      (SELECT X.*, Y.PROD_NO
       FROM   ORD X, ORD_DTL Y
       WHERE  X.ORDNO = Y.ORDNO) B
WHERE  A.CUST_NO = B.CUST_NO;

마무리

비교적 간단한 뷰 머징(View Merging) 쿼리 변환과 조인 순서를 예로 들었습니다. 실무에서는 보다 복잡하고 다양한 쿼리와 힌트가 많이 사용됩니다. 복잡한 쿼리에 사용한 힌트일 수록 힌트 충돌, 의미 오류 등으로 무시되는 힌트가 많이 발견됩니다.

오라클 최근 버전(12c이후)에는 사용자의 힌트가 제대로 사용되고 있는지 확인할 수 있는 Hint Report 기능이 있습니다. 다음 글에서는 Hint Report에 대해 소개하겠습니다.

오라클 옵티마이저 힌트 – 쿼리블록

쿼리 블록이란

쿼리 블록 변환

마무리