SQL Query Tuning [2] 적용(Index)

Query Tuning

앞서 설명한 실행 계획을 통해 얻을 수 있는 데이터는 절대적인 것이 아니다. cost 값이 높음에도 불구하고 응답 속도가 더 빠른 Query 구문이 있는가하면, 그 반대 역시 존재하기 때문이다. 따라서 우리는 다음과 같이 시도해볼 수 있다.

1. Index 설정( + Hint 설정)
2. Function Index 설정( + Hint 설정)
3. Hash Join 설정 ( + Hint 설정)

 

---

 

Index 설정( + Hint 설정)

1. Index 조회

인덱스 설정을 하기에 앞서, 미리 걸어놓은 인덱스가 존재하는지 확인해야 한다. 구문은 다음과 같다.

-- INDEX 조회
SELECT *
FROM USER_INDEXES
WHERE TABLE_NAME = 'table_name';

위의 query 구문을 실행하면 다음과 같이 테이블에 설정된 인덱스를 확인할 수 있다. 필자는 실제 사용중인 테이블을 대상으로 조회하였기에 정상적으로 나오고 있다.

 

---

 

2. Index 상세 조회

테이블에 두 개의 인덱스를 걸어놓았기 때문에 조회된 값 역시 두 개의 데이터인 것을 알 수 있지만, 조금더 디테일하게 보고싶다면 어떻게 해야할까? 아래의 구문을 확인하자.

-- 일반 INDEX 상세 조회
SELECT a.table_name
	 , a.index_name
	 , a.column_position
	 , a.column_name
FROM user_ind_columns a
WHERE a.table_name = 'table_name'
ORDER BY a.index_name, a.column_position

다음과 같이, 어느 인덱스에 컬럼이 어떤 순서로 잡혀있는지 상세 정보를 확인할 수 있다.

 

---

 

3. Index 생성

인덱스를 생성하기 위해서는 어떤 테이블에 어떤 컬럼을 대상으로 할 것인지가 중요하다. 인덱스는 말 그대로 '색인' 이기 때문에, 우리가 목표로 하는 컬럼을 잘 생각해야한다. 아래의 구문을 살펴보자.

CREATE INDEX test_index
ON table_name(text_a, text_b, number_a, number_b);

필자가 예시로 잡은 인덱스는 text_a → text_b  → number_a  → number_b 순서로 잡혀졌음을 알 수 있다. 인덱스를 선언하여 미리 색인화 시킴으로써, 우리는 조회에 소모되는 시간을 조금이나마 줄일 수 있다. 인덱스 순서는 인덱스 상세 정보에서도 확인할 수 있으니 참고하도록 하자.

 

---

 

4. Index 적용

인덱스가 제대로 설정이 되었으며, 내부적으로 작동하는지 확인해보도록 하자.

SELECT *
FROM table_name a
WHERE 1=1
AND a.text_a = '조건문'

기껏 만들어놓은 인덱스가 작동하지 않는다. Query 구문을 실행하게 되면 해당 질의에 대하여 DB 내부의 최적화 시스템(Optimizer : 옵티마이저)이 1차적으로 판단하게 된다. 현재 옵티마이저는 인덱스를 적용하는 것 보다 전체 데이터를 조회하는 Full Scan 방식이 더 효율적이라고 판단한 것이다.

 

그 까닭은 정말 쉽게 찾아볼 수 있다. 이전 게시물의 SQL Tuning 방식 중 '선택 항목 최적화' 라는 항목이 기억나는가? 필자가 생각하기에 ' 불필요한 데이터를 함께 조회하지 말아야 한다.' 라는 내용 말이다. 전체 데이터를 조회하기에 당연히 색인이 불필요할 뿐이다. 따라서 옵티마이저는 당연하게도 인덱스를 활용하지 않았다.

 

다음과 같이 필요한 정보만 조회하도록 하자.

SELECT text_a, text_b, number_a, number_b
FROM table_name a
WHERE 1=1
AND a.text_a = '조건문'

아름답게 인덱스가 설정된 것을 볼 수 있다. 질의문을 실행하는데 필요한 예상 자원소모량(cost) 또한 절반으로 뚝 줄어들었으며, 읽어들이는 byte 역시 대폭 감소한 것을 확인할 수 있다. 대부분의 상황에서 인덱스는 옳다.

 

---

 

5. Index Hint 적용

위의 상황이 펼쳐지지 않고 옵티마이저가 강제로 인덱스를 적용하지 않을 때가 종종 있다. 그 친구 판단으로는 사용하지 않는 것이 더 효율적이라고 생각했을 것이다. 그럼에도 강제로 사용하고 싶을 때에는, Hint(힌트)라고 불리는 다음의 구문을 사용하자.

SELECT /*+ index(a test_index) */ text_a, text_b, number_a, number_b
FROM table_name a
WHERE 1=1
AND a.text_a = '조건문'

힌트 구문을 사용할 때에는 /*+ */ 형태로 사용한다. 주석안에 플러스 문자가 들어가있어야 한다. 여러 힌트 구문들이 많으니, 구글링해서 알아보도록 하자. 서브쿼리를 좀 더 빠르게 읽어들이게끔 유도하는 힌트와 같은 여러가지 힌트 구문들이 많다. 필자는 다 테스트를 진행해봤는데 오히려 조회에 걸리는 시간이 느려져서 안쓴다...

 

---

 

[ 연관 게시글 ]

SQL Query Tuning [1] 개요 및 설명

SQL Query Tuning [2] 적용(Index)

SQL Query Tuning [3] 적용(Function Index)

SQL Query Tuning [4] 적용(Hash Join)