SQL BOOSTER - 인덱스로 커버된 SQL

 

인덱스를 이용해 검색할 때 테이블 접근 횟수를 줄이는 것이 매우 중요하다.

 

이 횟수를 얼만큼 줄이느냐에 따라 SQL의 성능이 좋아진다. 더 나아가 테이블 접근 자체가 생략된다면 성능에 가장 좋다. 

 

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 X_T_ORD_BIG_$) */
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

 

실행계획의 Buffers 수치를 보면, TABLE ACESS BY INDEX ROWID 단계에서 30.125로 급증했다.

 

그러므로 TABLE ACESS BY INDEX ROWID만 제거하면 SQL 성능을 끌어올릴 수 있다.

 

TABLE ACCESS BY INDEX ROWID를 수행한 이유는 ORD_ST 때문이다. (해당 컬럼은 INDEX가 없다)

 

해당 컬럼을 포함한 인덱스를 새로 생성해보자 

DROP INDEX X_T_ORD_BIG_4;

CREATE INDEX X_T_ORD_BIG_4 ON T_ORD_BIG(CUS_ID, ORD_YMD,ORD_ST);

SQL BOOSTER

 

실행계획을 보면, 전체 Buffers 수치가 146으로 탁월하게 줄어들었다! SQL에 필요한 모든 컬럼이 인덱스에 있기 때문이다.

 

이처럼 테이블 접근 없이 인덱스만으로 SQL이 처리되는 것을 인덱스로 커버된 SQL이라고 한다.

 

명심할 것은 모든 SQL에 이처럼 인덱스를 생성하면 안된다! 데이터 변경 성능이 나빠진다. 

 

인덱스로 SQL을 완전히 커버하는 기술은 최후의 순간에만 사용하길 바란다!!!

 

---

Predicate Information - ACCESS 

 

인덱스를 탔다라는 말은 실행계획에서 SQL이 인덱스를 사용해 처리하고 있으면 이와 같은 표현을 한다.

 

문제는 SQL을 제대로 타야한다!

 

SELECT /*+ GATHER_PLAN_STATISTICS */
T1.ORD_ST, COUNT(*)
FROM T_ORD_BIG T1
WHERE SUBSTR(T1.ORD_YMD,1,6) = '201703'
AND T1.CUS_ID = 'CUS_0075'
GROUP BY T1.ORD_ST;

SQL BOOSTER

 

실행계획은 위와 같다 X_T_ORD_BIG_4 인덱스로 INDEX RANGE SCAN하고 있다. 

 

인덱스를 제대로 탔는지 Predicate Information을 살펴보면 알 수 있다. 

 

INDEX RANGE SCAN에서 CUS_ID 조건을 access로 처리했고, SUBSTR조건은 filter로 처리했다. 

 

access는 인덱스 리프 블록의 스캔 시작 위치를 찾는 데 사용한 조건

 

filter는 리프 블록을 차례대로 스캔하면서 처리한 조건이다. 

 

이는 리프 블록의 검색 위치를 찾을 때는 CUS_ID 조건만 사용되었다! 인덱스를 제대로 탔으면 ORD_YMD에 대한 조건도 access에 표시되어야한다.

 

인덱스에는 SUBSTR 적용 이전의 값만 저장되어 있다. 그러므로 인덱스로 구성한 컬럼을 변형해 조건절에 사용하면 인덱스를 제대로 사용할 수 없다. 

 

SUBSTR을 제거하고 LIKE 조건을 이용해보자 

 

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

SQL BOOSTER

 

이번에야말로 제대로 타서 access에 이를 잘 사용하고 있다. 

 

같은 인덱스를 사용한다고 해도 어느 조건까지 access 했는지에 따라 성능 차이가 난다.! 

 

항상 Predicate Information을 살펴보자 

 

---

 

너무 많은 인덱스의 위험성 

 

인덱스는 전반적으로 SQL 성능에 도움을 준다. 하지만 너무 많아지면 인덱스를 구성하는 컬럼이 너무 많아지는 것을 항상 경계해야한다. 

 

SELECT T1.SEGMENT_NAME, T1.SEGMENT_TYPE, T1.BYTES/1024/1024 as SIZE_MB, T1.BYTES/T2.CNT BYTE_PER_ROW
FROM DBA_SEGMENTS T1, (SELECT COUNT(*) CNT FROM ORA_SQL_TEST.T_ORD_BIG) T2
WHERE T1.SEGMENT_NAME LIKE '%ORD_BIG%'
ORDER BY T1.SEGMENT_NAME;

SQL BOOSTER

 

테이블은 2,048 메가다. 1번 인덱스는 670메가, 3번 4번 인덱스는 1000메가에 달한다.

 

4번 인덱스는 테이블 절반에 가까운 크기다. 인덱스들의 크기를 합치면 테이블보다 훨씬 크다.

 

BYTE_PER_ROW 데이터 한 건당 몇 바이트 정도인지 대략 보여준다.

 

SQL BOOSTER

 

인덱스들을 모두 합친 크기가 테이블보다 더 큰 것이 한눈에 보인다. 그림에는 데이터가 INSERT 되는 과정도 표현되어 있다. 

 

데이터 한 건이 INSERT 될 때마다 테이블을 비롯해 모든 인덱스에 INSERT가 발생한다. 일반적으로 테이블에 대한 INSERT보다 인덱스에 대한 INSERT 부담이 더 크다. 

 

인덱스는 리프 블록이 정렬되어야하므로 꼭 정해진 위치에 데이터가 INSERT 되어야 하기 때문이다. 

 

위와 같은 상황에서는 인덱스를 하나 더 추가할 수 있을까? 쉽지 않을 수 있다. 

 

인덱스가 많으니 추가하지 말라는 것이 아니라, 추가하기 까지 많은 고민이 필요하다