SQL BOOSTER - 단일 인덱스

 

단일 인덱스의 컬럼 정하기 

 

인덱스는 조건에 맞는 데이터를 빠르게 찾기 위한 객체이다. 

WHERE 조건절에 사용된 컬럼에 인덱스를 구성하는 것이 기본 원리다. 

 

SELECT /*+ GATHER_PLAN_STATISTICS*/
TO_CHAR(T1.ORD_DT,'YYYYMM'), COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.CUS_ID = 'CUS_0064'
AND T1.PAY_TP = 'BANK'
AND T1.RNO = 2
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM');

 

이제 성능 개선을 위해 단 하나의 단일 인덱스를 고려해보자 

후버 컬럼은 WHERE 조건절에 사용된 CUS_ID, PAY_TP, RNO컬럼이다. 

SELECT 'CUS_ID' COL, COUNT(*) CNT FROM T_ORD_BIG T1 WHERE T1.CUS_ID = 'CUS_0064'
UNION ALL 
SELECT 'PAY_TP' COL, COUNT(*) CNT FROM T_ORD_BIG T1 WHERE T1.PAY_TP = 'BANK'
UNION ALL 
SELECT 'RNO' COL, COUNT(*) CNT FROM T_ORD_BIG T1 WHERE T1.RNO = 2

SQL BOOSTER

 

인덱스 컬럼 선정의 규칙 중 하나는 선택성이 좋은 컬럼을 사용하는 것 

주어진 조건에 데이터가 적을수록 선택성이 좋고, 조건에 해당하는 데이터가 많을수록 선택성이 나쁘다.

 

RNO = 2 조건의 결과가 3047건으로 가장 적다. RNO 조건이 선택성이 가장 좋고, 그 다음 CUS_ID가 좋다.

성능을 위한 인덱스를 하나 만들어야 한다면, RNO 컬럼에 만드는 것이 좋다. 

 

CREATE INDEX X_T_ORD_BIG_2 ON T_ORD_BIG(RNO);

SELECT /*+ GATHER_PLAN_STATISTICS INDEX (T1 X_T_ROD_BIG_2) */
FROM T_ORD_BIG T1
WHERE T1.CUS_ID = 'CUS_0064'
AND T1.PAY_TP ='BANK'
AND T1.RNO =2
GROUP BY TO_CHAR(T1.ORD_DT ,'YYYYMM');

SQL BOOSTER

 

위 SQL을 결과에 대한 실행 계획으로 위에 테이블을 얻을 수 있다. 

 

• X_T_ORD_BIG_2 인덱스를 이용해 조건에 맞는 데이터를 찾는다. -> 3047건
• 찾아낸 3047건에 대해 TABLE ACCESS BY INDEX ROWID를 수행 -> 2건의 결과 
• 마지막으로 GROUP BY를 수행 

CREATE INDEX X_T_ORD_BIG_3 ON T_ORD_BIG(CUS_ID);

 

인덱스를 변경하고 같은 SQL을 돌려보자 결과는 아래와 같다.

SQL BOOSTER

이전보다 오래 걸링 원인은 INDEX RANGE SCAN 단계의 A-Rows가 340K이므로, TABLE ACCESS BY INDEX ROWID가 340k번 수행된다. 

SQL BOOSTER

RNO가 2인 데이터는 3047건이므로 TABLE ACCESS BY INDEX ROWID도 3047번이다. 하지만, CUS_ID는 340000건이므로 테이블 엑세스도 340000번 수행된다. 

 

---

단일 인덱스 VS 복합 인덱스

 

복합 인덱스는 단일 인덱스를 능가하는 성능을 낼 수 있다. 하나의 복합 인덱스로 여러 개의 인덱스를 대신 할 수 있는 장점도 있다.

인덱스가 많아질수록 입력, 수정 삭제 성능 감소가 발생한다. 

이와 같은 이유로 복합 인덱스를 이용해 인덱스의 수를 줄이는 것이 매우 중요하다. 

 

DROP INDEX X_T_ORD_BIG_3; --인덱스 제거

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_1) */
T1.ORD_ST, COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD LIKE '201703%'
AND T1.CUS_ID = 'CUS_0075'
GROUP BY T1.ORD_ST;

SQL BOOSTER

실행 계획을 보면,  실행시간은 6.61초, Bufferes는 338K, INDEX RANGE SCAN은 1850K이다

 

이번에는 WHERE 조건절에 사용된 ORD_YMD와 CUS_ID 컬럼 두 개를 모두 포함하는 복합 인덱스를 만들어보자 

CREATE INDEX X_T_ORD_BIG_3 ON T_ORD_BIG(ORD_YMD,CUS_ID);

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_3) */
T1.ORD_ST, COUNT(*)
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD LIKE '201703%'
AND T1.CUS_ID = 'CUS_0075'
GROUP BY T1.ORD_ST;

SQL BOOSTER

 

실행계획을 확인하면, 전체 실행 시간이 1.49초로 개선되었다. 

INDEX RANGE SCAN의 A-Rows가 30000에 불과하다. 

SQL BOOSTER

 

복합 인덱스를 사용한 리프 블록을 보면, ORDER BY 결과처럼 ORD_YMD별로 정렬된 후 CUS_ID로 데이터가 정력되어 있다.

 

단일 인덱스의 경우 ORD_YMD 조건은 인덱스를 이용했지만, CUS_ID를 확인하기 위해서는 테이블에 방문해야만 알 수 있다.