[SQL 튜닝 실습 1-3] 인덱스 스캔 튜닝

실습 목표 : 인덱스 구성 컬럼을 추가해서 테이블 랜덤 액세스 제거하기

 

실습준비

 

테이블 생성 : TB_ORD

CREATE TABLE TB_ORD
(
    ORD_NO VARCHAR2(10), --주문번호
    ORD_DT VARCHAR2(8), --주문일자
    ORD_NM VARCHAR2(150), --주문이름
    ORD_AMT NUMBER(15), --주문금액
    PRDT_CD VARCHAR2(6), --상품코드
    SALE_GB VARCHAR2(2), --판매구분
    PAY_GB VARCHAR2(2), --결제구분
    CUST_ID VARCHAR2(10), --고객ID
    INST_DTM DATE, --입력시간
    INST_ID VARCHAR2(50), --입력자ID
    UPDT_DTM DATE, --수정시간
    UPDT_ID VARCHAR2(5) --수정자ID
);

 

데이터 입력 : DUAL_5 테이블 생성해서 데이터 복제에 이용

CREATE TABLE DUAL_5
(
    DUMMY VARCHAR2(1)
);

INSERT INTO DUAL_5
SELECT DUMMY
FROM DUAL CONNECT BY LEVEL <= 5;

COMMIT;

 

TB_ORD 테이블을 NOLOGGING 모드로 설정

ALTER TABLE TB_ORD NOLOGGING;

 

TB_ORD 테이블에 500만 건(5×1000000) 을 입력한다 : 약 25분 정도 걸림!

INSERT /*+ APPEND */  INTO TB_ORD --APPEND 힌트 사용
SELECT
    LPAD(TO_CHAR(ROWNUM), 10, '0'),
    TO_CHAR(SYSDATE-TRUNC(DBMS_RANDOM.VALUE(1,3650)), 'YYYYMMDD'),
    DBMS_RANDOM.STRING('U', 150),
    TRUNC(DBMS_RANDOM.VALUE(1000, 100000)),
    LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 50)), 6, '0'),
    LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 10)), 2, '0'),
    LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 10)), 2, '0'),
    LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 100000))), 10, '0'),
    SYSDATE,
    'DBMSEXPERT',
    NULL,
    NULL
FROM DUAL_5, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 1000000);

COMMIT;

 

기본키 생성

ALTER TABLE TB_ORD
ADD CONSTRAINT TB_ORD_PK 
PRIMARY KEY (ORD_NO);

 

인덱스 구성

CREATE INDEX TB_ORD_IDX01 ON TB_ORD(ORD_DT, ORD_NM, ORD_AMT);

 

통계 정보 생성

ANALYZE TABLE TB_ORD COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

 

---

튜닝 전 상황

튜닝 전 SQL 문

 

SELECT
  ORD_DT,
  SALE_GB,
  PAY_GB,
  COUNT(*) AS 주문건수,
  SUM(ORD_AMT) AS 총주문금액,
  ROUND(AVG(ORD_AMT), 2) AS 평균주문금액
FROM TB_ORD
WHERE
  ORD_DT BETWEEN '20150101' AND '20151231'AND
  ORD_NM LIKE 'A%'AND
  ORD_AMT >= 1000
GROUP BY
  ORD_DT, SALE_GB, PAY_GB
ORDER BY
  ORD_DT, SALE_GB, PAY_GB;

실행 속도 : 37초

 

ORD_DT BETWEEN '20150101' AND '20151231'AND   ORD_NM LIKE 'A%'AND   ORD_AMT >= 1000

ORD_DT 컬럼을 조건으로 줘서 2015년의 주문 건을 검색한다
10_12번째 줄 ORD_DT + ORD_NM + ORD_AMT 으로 구성된 TB_ORD_IDX01 인덱스를 이용하게 된다.

 

[문제점]

TB_ORD_IDX01 인덱스를 사용해서 효율적인 인덱스 스캔을 하고 있지만, SELECT 절에 인덱스 컬럼이 아닌
다른 컬럼도 조회하고 있으므로 인덱스 스캔에 이은 테이블 랜덤 액세스 부하가 발생.
넓은 범위를 주로 조회하고 결과 건수가 많아지면, DBMS에 큰 부하를 주게 된다.

 

실행계획

 

수행 순서 : 3 > 2 > 1 > 0

3 : TB_ORD 테이블의 TB_ORD_IDX01 인덱스를 인덱스 범위 스캔한다. 

2 : TB_ORD_IDX01 인덱스의 리프 블록에 저장된 ROWID를 이용해서 테이블 랜덤 액세스를 수행한다.
인덱스 스캔에서 조건에 만족하는 ROWID가 많다면 자연스레 테이블 랜덤 액세스 부하가 일어난다.
ORDER BY 와 GROUP BY 연산을 수행한다.

1 : ORDER BY 와 GROUP BY 연산을 수행한다.

0 : SELECT 절의 연산을 수행한다.

 

---

1차 튜닝

인덱스 추가

ORD_DT + ORD_NM + ORD_AMT + SALE_GB + PAY_GB 컬럼으로 구성된 인덱스를 신규로 생성

CREATE INDEX TB_ORD_IDX02 ON TB_ORD(ORD_DT, ORD_NM, ORD_AMT, SALE_GB, PAY_GB);

 

통계정보 생성

신규로 생성한 인덱스에 대한 통계정보 생성

ANALYZE INDEX TB_ORD_IDX02 COMPUTE STATISTICS;

 

튜닝 후 SQL문

SELECT 
/*+ INDEX(TB_ORD TB_ORD_IDX02) */
    ORD_DT,
    SALE_GB,
    PAY_GB,
    COUNT(*) AS 주문건수,
    SUM(ORD_AMT) AS 총주문금액 ,
    ROUND(AVG(ORD_AMT), 2) AS 평균주문금액
FROM TB_ORD
WHERE ORD_DT BETWEEN '20150101' AND '20151231'
AND ORD_NM LIKE 'A%'
AND ORD_AMT >= 1000
GROUP BY ORD_DT, SALE_GB, PAY_GB
ORDER BY ORD_DT, SALE_GB, PAY_GB;

실행 속도 : 0.7초

 

/*+ INDEX(TB_ORD TB_ORD_IDX02) */

TB_ORD 테이블의 TB_ORD_IDX02 인덱스를 사용하도록 INDEX 힌트 사용

해당 인덱스에는 SALE_GB 컬럼과 PAY_GB 컬럼이 존재하기때문에 테이블 랜덤 액세스를 생략할 수 있다.

테이블 랜덤 액세스가 사라졌다는 것은 인덱스 구성 컬럼만으로 SQL문의 요청 내용을 검색한다는 뜻

 

1차 튜닝 후 실행계획

 

수행 순서 : 2 > 1 > 0

2 : TB_ORD 테이블의 TB_ORD_IDX02 인덱스를 인덱스 범위 스캔한다.

1 : ORDER BY와 GROUP BY 연산을 수행한다

0 : SELECT 절의 연산 수행한다