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

실습 목표 : 인덱스를 최대한 활용해서 원하는 결과 검색하기

실습 준비

테이블 생성 : TB_CUST

CREATE TABLE TB_CUST 
(
    CUST_ID VARCHAR2(10), --고객ID
    CUST_NM VARCHAR2(50), --고객명
    BIRTH_DT VARCHAR2(8), --생일
    INST_DT VARCHAR2(8), --입력일자
    INST_ID VARCHAR2(10), --입력자ID
    INST_NM VARCHAR2(50) --입력자명
);

 

테이블 생성 : TB_ORD

CREATE TABLE TB_ORD
(
    ORD_NO VARCHAR2(15), --주문번호
    ORD_DT VARCHAR2(8), --주문일자
    PRDT_CD VARCHAR2(6), --제품코드
    ORD_AMT NUMBER(15), --주문금액
    DIS_AMT NUMBER(15), --할인금액
    INST_DT VARCHAR2(8), --입력일자
    INST_ID VARCHAR2(10), --입력자ID
    INST_NM VARCHAR2(50), --입력자명
    CUST_ID VARCHAR2(10) --고객ID
);

 

데이터 입력 : TB_CUST 테이블에 100만 건의 데이터 입력

INSERT INTO TB_CUST --고객테이블
SELECT
    LPAD(TO_CHAR(ROWNUM), 10, '0'), --고객ID
    DBMS_RANDOM.STRING('U', 10), --고객명
    TO_CHAR(SYSDATE-TRUNC(DBMS_RANDOM.VALUE(365,36500)), 'YYYYMMDD'), --생일
    TO_CHAR(SYSDATE, 'YYYYMMDD'), --입력일자
    'KSKY2', --입력자ID
    'KSKY216'  --입력자명
FROM DUAL CONNECT BY LEVEL <= 1000000;

COMMIT;

 

NOLOGGING 모드 설정 : TB_ORD

이 테이블은 1000만건 이상이 되는 데이터를 갖고 있기때문에, 속도 효율을 위해 이 모드로 전환한다.

ALTER TABLE TB_ORD NOLOGGING; 

 

데이터 입력 : TB_ORD 

(*) 3분 정도 걸림

INSERT /*+ APPEND */ INTO TB_ORD --APPEND힌트 사용
SELECT
  LPAD(TO_CHAR(ROWNUM), 15, '0'),--주문번호
  TO_CHAR(SYSDATE-TRUNC(DBMS_RANDOM.VALUE(365,3650)), 'YYYYMMDD'), --주문일자
  DBMS_RANDOM.STRING('X', 6), --제품코드
  TRUNC(DBMS_RANDOM.VALUE(1000, 100000)), --주문금액
  TRUNC(DBMS_RANDOM.VALUE(100, 10000)), --할인금액
  TO_CHAR(SYSDATE, 'YYYYMMDD'), --입력일자
  'KSKY2', --입력자ID
  'KSKY216',  --입력자명
  A.CUST_ID--고객ID
FROM TB_CUST A, ( SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10);

COMMIT;

 

기본키 생성

ALTER TABLE TB_CUST
ADD CONSTRAINT TB_CUST_PK 
PRIMARY KEY (CUST_ID);

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

 

외래키 생성

ALTER TABLE TB_ORD
ADD CONSTRAINT TB_ORD_FK
FOREIGN KEY (CUST_ID) REFERENCES TB_CUST(CUST_ID);

 

테이블 관계

 

* TB_CUST 테이블과 TB_ORD 테이블의 관계는 1:M 관계이다.
* 한 명의 고객은 여러 건을 주문할 수 있고, 한 건도 주문하지 않을 수 있습니다.
* 한 개의 주문은 반드시 한 명의 고객을 가져야 한다

NOTE : 참조 무결성 제약 조건 (외래키 제약 조건)
테이블 간의 관계를 표현한 것으로 한 테이블이 다른 한 테이블에 의존하는 관계이다.

 

 

외래키 제약 조건 테스트

INSERT INTO TB_ORD VALUES
('1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1');

명령의 1 행에서 시작하는 중 오류 발생 -
INSERT INTO TB_ORD VALUES
('1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1')
오류 보고 -
ORA-02291: 무결성 제약조건(KSKY2.TB_ORD_FK)이 위배되었습니다- 부모 키가 없습니다.

 

통계정보 생성 : 테이블과 인덱스에 대한 통계정보 생성

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

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

---

튜닝 전 상황

튜닝 전 SQL문

SELECT /*+ FULL(A) */  	
   COUNT(*)			
FROM TB_CUST A			
WHERE			
  A.CUST_NM LIKE 'AB%' AND			
  EXISTS			
    (			
        SELECT '1'			
         FROM TB_ORD C			
         WHERE			
       C.CUST_ID = A.CUST_ID AND			
       C.PRDT_CD LIKE 'AB%'			
     ) ;			

수행시간 12초

 

SELECT /*+ FULL(A) */ : FULL 힌트를 사용해서 TB_CUST 테이블의 스캔 방법을 테이블 풀 스캔으로 유도

 

A.CUST_NM LIKE 'AB%' AND : LIKE 문을 이용해서 CUST_NM 컬럼값이 'AB'로 시작하는 행을 찾는다.

EXISTS : EXISTS문을 이용해서 TB_ORD 테이블에 PRDT_CD 컬럼값이 'AB'로 시작하는 행을 검색한다.

 

SELECT '1' : 값의 내용이 아닌 단순히 값의 유무로 판단하기 위해서 '1'로 판단. 
즉, 내포 EXISTS안에서 자주 사용되며, 조건에 해당하는 값이 발견되면 '1'을 리턴한다.

 

[위의 SQL의 문제점]

1) TB_CUST 테이블의 CUST_NM 컬럼이 변별력있는 컬럼임에도 인덱스를 생성하지 않음.
2) TB_ORD 테이블의 CUST_ID와 PRDT_CD 컬럼이 변별력있는 컬럼임에도 인덱스를 생성하지 않음. 
3) OLTP 환경에서 빈번하게 사용하는 SQL문이라면 반복적인 테이블 풀 스캔은 전체 시스템에 큰 부하를 주게 된다.

튜닝 전 실행 계획

3 > 4 > 2 > 1 > 0

 

3 : 테이블 풀 스캔 : TB_CUST
4 : 테이블 풀 스캔 : TB_ORD
2 : 해시 세미 조인 : TB_CUST 와 TB_ORD 를 해시 조인함. 물론, 옵티마이저가 판단한 조인 방식.
1 : COUNT 함수 연산
0 : SELECT 절 연산 수행

 

---

1차 튜닝

인덱스 생성 : TB_CUST 테이블에 CUST_NM 컬럼으로 구성된 인덱스 생성

CREATE INDEX TB_CUST_IDX01 ON TB_CUST(CUST_NM);

 

복합 인덱스 생성 : TB_ORD 테이블에 CUST_ID 와 PRDT_CD 컬럼으로 구성된 복합 인덱스 생성

CREATE INDEX TB_ORD_IDX01 ON TB_ORD(CUST_ID, PRDT_CD);

 

통계 정보 생성

ANALYZE INDEX TB_CUST_IDX01 COMPUTE STATISTICS;
ANALYZE INDEX TB_ORD_IDX01 COMPUTE STATISTICS;

 

 

1차 튜닝 후의 SQL 문

SELECT /*+ INDEX(A TB_CUST_IDX01) */
  COUNT(*)
FROM TB_CUST A
WHERE A.CUST_NM LIKE 'AB%'
AND EXISTS
            (
             SELECT /*+ INDEX(C TB_ORD_IDX01) NL_SJ */ 
                     '1'
             FROM TB_ORD C
             WHERE C.CUST_ID = A.CUST_ID
             AND C.PRDT_CD LIKE 'AB%'
            );

수행시간 : 0.013 초

 

SELECT /*+ INDEX(A TB_CUST_IDX01) */

TB_CUST 테이블에 생성한 TB_CUST_IDX01 인덱스를 사용하도록 INDEX 힌트를 사용했다.

이것은 CUST_NM 조건을 인덱스 스캔하기 위한 조치이다.

 

SELECT /*+ INDEX(C TB_ORD_IDX01) NL_SJ */

TB_ORD 테이블의 TB_ORD_IDX01 인덱스를 사용하도록 INDEX 힌트를 사용했다.

또한 중첩 루프 세미조인을 할 수 있도록 NL_SJ 힌트를 사용했다.

중첩 루트 세미조인은 조건 만족 시 해당 조인 대상 행은 더 이상 스캔을 수행하지 않고 다음 행으로 넘어가는 조인 방식이다.

 

튜닝 1차 후 실행계획

4 > 3 > 5 > 2 > 1 > 0

 

4 : TB_CUST 테이블의 TB_CUST_IDX01 인덱스를 인덱스 범위스캔이라고 한다
인덱스 범위 스캔이란 수직적 탐색을 통해 인덱스 리프 블록 특정위치로 이동 후 지정된 범위를 스캔하는 방식으로 인덱스 스캔 방식중 제일 일반적인 방식이다

3 : TB_CUST 테이블의 TB_CUST_IDX01 인덱스는 CUST_NM으로 이뤄진 인덱스이다
해당 인덱스 스캔 후 CUST_ID를 갖고오기 위해 테이블 랜덤 액세스(BY INDEX ROWID)를 한다.
TB_CUST 테이블 기본키가 CUST_ID이긴 하지만, 여기서는 CUST_NM 컬럼으로 이뤄진 인덱스를 스캔했기때문에 테이블 랜덤 액세스는 불가피하다.
TB_CUST_IDX01 인덱스의 구성을 CUST_NM + CUST_ID 컬럼으로 한다면 해당 테이블 랜덤 액세스 부하를 줄일 수 있다.

5 : TB_CUST 테이블의 CUST_ID 컬럼을 바탕으로 TB_ORD 테이블의 TB_ORD_IDX01 인덱스를 인덱스 범위 스캔했다. 
TB_ORD_IDX01 인덱스는 CUST_ID + PRDT_CD 로 구성되서 별도로 테이블 랜덤 액세스하지 않았다.

2 : TB_CUST 테이블을 기준으로 TB_ORD 테이블과 중첩 세미 조인한다. 

즉, TB_CUST 테이블을 기준으로 TB_ORD 테이블과 조인 조건이 성립되면, 
해당 조인 대상 행에 대해 더는 스캔하지않고 멈추게 된다.

1 : COUNT 함수 연산 수행

0 : SELECT 절 연산 수행

 

---

2차 튜닝

튜닝 관점

위에서 1차 튜닝한 작업으로 충분히 효율적인 인덱스 스캔을 했지만, 이 SQL문을 더욱 빠르게 하는 방법이 있다.
바로 인덱스 컬럼을 추가해서 테이블 랜덤 액세스를 없애고 인덱스 스캔만으로 데이터를 조회하는 기법이다.
다음과 같이 TB_CUST_IDX01 인덱스를 재생성하고 통계정보를 생성한다. 
TB_CUST_IDX01의 인덱스를 CUST_NM과 CUST_ID로 재생성했는데, 이렇게 하면 별도의 테이블 랜덤 액세스를 하지않고 TB_CUST_IDX01 인덱스만을 스캔하게된다.

 

DROP INDEX TB_CUST_IDX01;						
CREATE INDEX TB_CUST_IDX01 ON TB_CUST(CUST_NM, CUST_ID);						
ANALYZE INDEX TB_CUST_IDX01 COMPUTE STATISTICS;						

실행결과 : 0.007 초

 

2차 튜닝 후 실행계획

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

3 : TB_CUST 테이블의 TB_CUST_IDX01 인덱스를 인덱스 범위 스캔한다. 
CUST_ID 컬럼이 인덱스 컬럼에 추가되서 테이블 랜덤 액세스가 사라진 것이 튜닝 포인트이다. 

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

2 : TB_CUST_IDX01 인덱스를 기준으로 TB_ORD_IDX01 인덱스와 중첩 루프 세미 조인(NESTED LOOPS SEMI)한다.

1 : COUNT 함수 연산을 수행한다. 

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