(Oracle) 인덱스 힌트

쿼리 플랜에서 특정 INDEX 탄다고 나왔지만 실제로는 타지 않는 사례

[!warning] 쿼리 플랜 결과와 실제 옵티마이저 수행이 다른 경우가 생각보다 많기 때문에, 의도한 인덱스가 있다면 인덱스 힌트는 항상 써주는 것이 좋다.

쿼리 플랜에서 특정 INDEX를 탄다고 나와도, 실제로는 그 INDEX를 타지 않아 굉장히 오래 걸릴 수도 있다. 플랜은 그저 예상이다.

인덱스 정보

INK5_DR (CMPL_YMD ASC, RSLT_CD ASC, SETL_YN ASC)
INK1...10 총 10개 인덱스 존재함.

문제의 쿼리

SELECT *
FROM DR d
WHERE d.CMPL_YMD > '2023-04-24'
AND d.RSLT_CD = '99'

문제의 쿼리 플랜

분명 쿼리플랜에서는 INK5_DR 인덱스를 탄다고 알려주지만, 실제로 돌려보면 DBMS가 INK5_DR 인덱스를 태우지 않아 쿼리가 굉장히 오래걸린다.

아래처럼 직접 index hint 적어주면 제대로 인덱스를 탄다.

SELECT /*+ index(d INK5_DR) */
    *
FROM DR d
WHERE d.CMPL_YMD > '2023-04-24'
AND d.RSLT_CD = '99'

parallel_index() 쓰는 경우에도 별도로 index()를 같이 적어주어야 한다.

문제의 쿼리

SELECT /*+ parallel_index(d INK5_DR 4) */
    NVL(SUM(d.amt), 0),
    COUNT(*)
FROM DR d
WHERE {conditions...}
GROUP BY {conditions...}

문제 쿼리 실제 수행 결과

/*+ parallel_index(d INK5_DR 4) */ 했기 때문에 당연히 INK5_DR 탈거라고 생각했지만, 옵티마이저가 INK1_DR을 사용해 INDEX SKIP SCAN(참고) 해버렸다.

아래처럼 직접 index hint 적어주면 제대로 인덱스를 탄다.

SELECT /*+ index(d INK5_DR) parallel_index(d INK5_DR 4) */

[!warning] parallel_index 쓰는 경우에도 index 빼먹지 않도록 한다.
비슷하게 parallel 쓰는 경우에는 full 빼먹지 않도록 한다.

index hint 문법 관련

자주 하는 실수 - alias

if (alias를 지정하지 않는 경우) 테이블명으로 써도 인덱스힌트가 걸린다.
if (alias를 지정하는 경우) 테이블명이 아니라 alias를 써야만! 인덱스 힌트가 걸린다. 
    테이블명으로 걸면 적용되지 않는다.

• 단일 테이블 쿼리가 확장되면서 join 테이블이 추가되고, 여기에 각각 alias를 붙여줄 때, 인덱스 힌트도 alias로 고쳐주어야 하는데 이를 누락하는 경우가 종종 있다.
• 따라서 단일 테이블 쿼리여도, 테이블과 index hint 모두 alias를 써주는게 안전하다.
• Oracle index hint syntax

SELECT와 오라클 힌트 앞에 다른 주석이 있어도 괜찮다.

The index hint is enclosed within a comment, so any other comments before or after the hint will be treated as regular comments and will not affect the hint itself.

SELECT /* ... */ /*+ hint */