[SQL] 부등호가 등호보다 시간이 100배 빨랐다..?

[문제]

상식적으로 부등호가 등호보다 시간이 더 오래걸려야 맞는데 시간이 오히려 더 빨랐다

--소요시간 300ms
/*+ index(A index_a) index(B index_b) */
SELECT	*
FROM	TABLE A, TABLE B
WHERE	A.COLUMN1 = B.COLUMNS
AND	A.COLUMN2 > '20230728'


-- 소요시간 30sec
/*+ index(A index_a) index(B index_b) */
SELECT	*
FROM	TABLE A, TABLE B
WHERE	A.COLUMN1 = B.COLUMNS
AND	A.COLUMN2 = '20230731'

상식적으로 말이 안됐다. 그래서 플랜을 봤다.

--소요시간 300ms
/*+ index(A index_a) index(B index_b) */
SELECT	*
FROM	TABLE A, TABLE B
WHERE	A.COLUMN1 = B.COLUMNS
AND	A.COLUMN2 > '20230728'
/*
A테이블인덱스 -> B테이블인덱스
cost 대부분 0
B테이블인덱스 탈 시 접근하는 card 200 건
*/

-- 소요시간 30sec
/*+ index(A index_a) index(B index_b) */
SELECT	*
FROM	TABLE A, TABLE B
WHERE	A.COLUMN1 = B.COLUMNS
AND	A.COLUMN2 = '20230731'
/*
B테이블인덱스 -> A테이블인덱스
cost 대부분 0
A테이블인덱스 탈 시 접근하는 card 3000 건
*/

플랜을 보니 인덱스의 순서가 서로 바꼈었다. 접근 순서가 달랐을 뿐인데 시간은 100배의 차이가 낫다.... wow..

 

---

 

[원인]

문제의 원인은 옵티마이저가 통계정보를 제대로 수집하지 못해 발생한 문제였다. 

통계정보란?
 - 데이터베이스의 SQL 성능을 좌우하는 요소 중 하나
 - oracle의 옵티마이저가 실행계획 수립 시 참조하는 정보
 - 실행계획을 최적화하기 위해 수집된 통계정보를 참고하여 최적의 실행계획을 세움
 - analyze란 명령어를 사용하여 table 및 index, cluster의 통계정보를 생성
 - analyze를 하게되면 통계정보를 생성 후 data dictionary에 저장

이처럼 옵티마이저는 잡스케줄(오라클10g이상, 이하는 DBA가 수동으로 생성)이 자동으로 생성한 통계정보를 바탕으로 쿼리를 수행하게 되는데 항상 옳은 통계정보만 생성되는 것이 아니기 때문에 위와 같은 문제가 발생할 수 있다.

 

---

 

[해결]

해결방법은 역시 옵티마이저가 빠른 성능을 낼 수 있도록 인덱스 순서를 강제하는 것이다.

1. 힌트 수정

1) ORDERED

ORDERED 은 FROM 절에 명시돼있는 테이블 순서대로 접근하고자할 때 사용한다.

--문제된 SQL
/*+ index(A index_a) index(B index_b) */
SELECT	*
FROM	TABLE A, TABLE B
WHERE	A.COLUMN1 = B.COLUMNS
AND	A.COLUMN2 = '20230731'


--ORDERD 사용 시
/*+ ORDERED */
SELECT	*
FROM	TABLE A, TABLE B
WHERE	A.COLUMN1 = B.COLUMNS
AND	A.COLUMN2 = '20230731'

 

2) LEADING

LEADING은 HINT 절에 명시돼있는 테이블 순서대로 접근하고자할 때 사용한다.

--문제된 SQL
/*+ index(A index_a) index(B index_b) */
SELECT	*
FROM	TABLE A, TABLE B
WHERE	A.COLUMN1 = B.COLUMNS
AND	A.COLUMN2 = '20230731'


--LEADING 사용 시
/*+ LEADING(A B) */
SELECT	*
FROM	TABLE A, TABLE B
WHERE	A.COLUMN1 = B.COLUMNS
AND	A.COLUMN2 = '20230731'