(Oracle) 인덱스 힌트

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

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

쿼리 플랜에서 특정 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) */

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 */