[Oracle] 인덱스와 힌트를 사용해 쿼리 속도 개선하기

---

저번 포스팅에서는 인덱스의 간단한 개념과 생성 조회 삭제등의 명령어를 알아보았다.

오늘은 예시를 통해 인덱스를 사용해보고 쿼리속도가 얼마나 개선되는지 알아보자

 예제테이블 생성

-- 실습 테이블 생성
CREATE TABLE INDEXTEST (
  A1 NUMBER NOT NULL,
  A2 NUMBER NOT NULL,
  A3 VARCHAR2(50) NOT NULL,
  A4 VARCHAR2(100));

 

 임의 데이터 생성

-- 100만건 생성 
INSERT INTO INDEXTEST
SELECT
  MOD(ROWNUM-1, 90) * 4 A1,
  ROWNUM - 1 A2,
  TO_CHAR(ROWNUM - 1, 'RN') A3,
  LPAD('A',100,'A') A4
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;
  
COMMIT; 

 

 인덱스 생성

-- INDEXTEST 테이블 인덱스 생성
CREATE INDEX IDX_IT_1_2 ON INDEXTEST(A1,A2);
CREATE INDEX IDX_IT_2_1_3 ON INDEXTEST(A2,A1,A3);
CREATE INDEX IDX_IT_3_1_2 ON INDEXTEST(A3,A1,A2);

 테스트1 : 힌트를 사용하지 않는 SELECT

--Test1 : 힌트를 사용하지 않은 select
SELECT A1, A2, A3 FROM INDEXTEST;

해당 쿼리 Cost나 통계정보를 확인하기 위한 방법

1. 계획 설명(F10) 버튼 클릭

계획설명(F10)을 클릭했을때 볼 수 있는 실행 계획 및 정보

2. Autotrace 사용하기

-- Autotrace 모드 사용하기
SET AUTOTRACE ON;

해당 쿼리를 실행한후 Select 스크립트를 실행하면 콘솔창에서 통계정보를 볼 수 있다.

 

Autotrace를 사용했을시 볼 수 있는 통계 정보

 

어쨌든 이 통계 정보를 통해 우리가 알 수 있는 정보를 정리하면

• 생성한 인덱스 중에서 자동으로 IDX_IT_2_1_3을 사용했다.
• 테이블 전체를 FULL SCAN했다.
• 해당 쿼리를 사용하는 논리적비용(Cost)이 1285가 나왔다.

일단 이정도의 정보만 알아두고 다음 테스트로 넘어가자.

 테스트 2: A1, A2 인덱스 사용

--Test2 : A1,A2에 있는 인덱스 사용
SELECT /*+ index(INDEXTEST (A1, A2)) */ 
A1, A2, A3 
FROM INDEXTEST;

인덱스를 사용 했는데 오히려 Cost가 2746으로 더 올라버렸다.

 

※힌트란?

힌트는 SQL 튜닝의 핵심부분으로 일종의 지시구문이다.

SQL에 포함되어 쓰여져 Optimizer의 실행 계획을 원하는 대로 바꿀 수 있게 해준다.

사용 형태는 SQL 문장 내에 /*+ 힌트내용 */ 이 추가된다.

주의! 주석 표시에 더하기(+)가 있다. 

 

--힌트 사용 예시
--_ASC가 생략되어있을경우 자동으로 ASC 정렬을 사용한다
SELECT /*+ INDEX_ASC(INDEXTEST (A1)) */ 
A1,A2,A3 FROM INDEXTEST

--힌트 사용 예시(desc)
SELECT /*+ INDEX_DESC(INDEXTEST (A1)) */ 
A1,A2,A3 FROM INDEXTEST

 테스트 3: A1, A2 인덱스 사용(Where절 추가)

이번에는 인덱스를 사용하고 있는 컬럼에 조건을 걸어보자

--Test3 : A1, A2에 있는 인덱스 사용(where 절 추가)
SELECT /*+ index(INDEXTEST (A1, A2)) */ 
A1, A2, A3 FROM INDEXTEST
WHERE A2 < 10;

갑자기 Cost가 92로 확 줄어버렸다.

전체를 스캔하려면 where 절에 해당 컬럼이 무조건 참인 조건을 하나 집어 넣으면 될 것 같다.

 

 결론

간단한 테스트 3개로 결론을 정리하면

• 힌트를 사용한다고 무조건적으로 쿼리 속도의 개선이 있는 것은 아니다.
• 힌트를 사용하는 컬럼에 조건절이 붙어야 Cost를 확실하게 줄일 수 있다.

---

간단하게 예제를 만들어보면서 쿼리 속도 개선을 진행해보았다.

이전 포스팅에서도 말했지만 인덱스를 이용한 쿼리 속도 개선은 최후의 수단으로 생각하고 일단 SQL 설계가 잘 되어있는지 확인해보자.

 

관련글 : [데이터베이스/Oracle] - [Oracle] 인덱스(Index) 생성/조회/삭제 및 주의사항