[ORACLE] INDEX 타지 않는 경우 (feat. INDEX HINT)

시스템을 운영하다 보면 꼭 한번씩 DB상의 처리 속도가 늦어져 장애🤯로 이어질 뻔한 상황이 생기는데, 로직 상의 문제인 경우도 있지만, 내가 생각했던 INDEX를 타지 않거나 옵티마이저(OPTIMIZER)가 실행계획을 변경시켜 버리는 상황이 종종 발생하곤 했었다.

 

옵티마이저(OPTIMIZER)?? 그게 뭔데? 🙋‍♂️

 

옵티마이저(OPTIMIZER)에 대해 간단히 설명하면, 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다.

 

먼저, INDEX를 타지 않는 경우에 대해 알아보고, 옵티마이저에 의존하지 않고 INDEX를 탈 수 있도록 HINT를 주는 방법에 대해 추가적으로 알아보자.

INDEX를 타지 않는 경우

1) INDEX가 걸려있는 컬럼 절을 변형한 경우

- 수식이나 함수 등으로 INDEX 컬럼을 변형해야 하는 경우 INDEX 컬럼을 변형하지 말고 여기에 대입되는 컬럼이나 상수 부분에 적용해야 한다.

-- IDX_COL이 DATE 타입의 컬럼인 경우
-- BAD CASE) SELECT IDX_COL FROM TBL_JY WHERE TO_CHAR(IDX_COL, 'YYYYMMDD') = '20230101';

SELECT IDX_COL FROM TBL_JY WHERE IDX_COL = TO_DATE('20230101', 'YYYYMMDD');

2) 내부적으로 데이터 형 변환이 일어난 경우

- 서로 대입되는 항목끼리 데이터 타입이 다르면 내부적인 형 변화에 따라 컬럼이 변형된 효과를 나타낸다.

-- IDX_COL이 DATE 타입의 컬럼인 경우
-- BAD CASE) SELECT IDX_COL FROM TBL_JY WHERE IDX_COL = '20230101';

SELECT IDX_COL FROM TBL_JY WHERE IDX_COL = TO_DATE('20230101', 'YYYYMMDD');

3) 부정형으로 조건을 사용한 경우

- 부정문은 INDEX를 활용하지 못한다. 비교연산자 또는 NOT EXIST 사용 (무조건은 없음!! 상황에 보고 맞게!!)

-- BAD CASE) SELECT IDX_COL FROM TBL_JY WHERE IDX_COL != 30;

SELECT IDX_COL FROM TBL_JY WHERE IDX_COL > 30 AND IDX_COL < 30;
또는
SELECT IDX_COL FROM TBL_JY WHERE NOT EXISTS
    (SELECT IDX_COL FROM TBL_JY WHERE IDX_COL = 30);

4) LIKE 연산자를 사용하였을 경우

- LIKE 연산자를 사용하는 경우 %를 앞에 넣어 사용하게 되면 INDEX를 타지 않는다.

-- BAD CASE) SELECT IDX_COL FROM TBL_JY WHERE IDX_COL LIKE '%S%';

SELECT IDX_COL FROM TBL_JY WHERE IDX_COL LIKE 'S%';

 

위의 방법대로 인덱스를 타지 않는 경우들을 생각해서 쿼리를 잘 짜더라도, 옵티마이저에게 의존하다 보면 나도 모르는 사이에 실행 계획이 바뀔 수도 있다. 야간에 바뀌면 정말 최악.....😤😱😭

 

따라서, 중요한 로직의 쿼리라면 항상 오라클이 최적의 경로를 만들어 낼 것이라는 믿음을 잠시 접어두고 특정 SQL에서 어떤 INDEX의 선택도가 높은지 알고 있다면 INDEX HINT를 통해 더 효율적인 실행 계획을 구사할 수 있다.

 

단, 힌트, 인덱스, 조인의 개념을 정확히 알고 사용하지 않는 무분별한 HINT의 사용은 성능 저하를 초래하기 때문에  최적의 실행경로를 알고 있는 경우 적절하게 사용하자.

 

인덱스 힌트는 /*+ INDEX[_ASC OR _DESC](테이블명[테이블 별칭] 인덱스명) */ 와 같이 사용할 수 있고, 아래 예제를 참고하자!

SELECT /*+ INDEX(A EMP_IDX01) */
A.EMP_NO
, A.EMP_NAME
FROM EMP A

SELECT /*+ INDEX_DESC(EMP EMP_IDX01) */
EMP_NO
, EMP_NAME
FROM EMP

 

참고 : http://dbcafe.co.kr/wiki/index.php/%EC%98%A4%EB%9D%BC%ED%81%B4_%EC%9D%B8%EB%8D%B1%EC%8A%A4_%ED%83%80%EC%A7%80_%EC%95%8A%EB%8A%94_%EA%B2%BD%EC%9A%B0

https://kwomy.tistory.com/74