[SQL 튜닝 실습 1-7] 테이블 풀 스캔 튜닝

실습 준비

 

테이블 생성 : 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		
);		

 

데이터 입력

CREATE TABLE DUAL_1000					
(					
DUMMY CHAR(1)					
);					
					
INSERT INTO DUAL_1000					
SELECT DUMMY FROM DUAL CONNECT BY LEVEL <= 1000;					
					
COMMIT;					

 

NOLOGGING 모드 설정 : TB_ORD

ALTER TABLE TB_ORD NOLOGGING;	

 

데이터 입력 : TB_ORD 테이블에 1,000만 건(1000 × 10000) : 40분 정도 걸림
(*) SALE_GB 는 00, 01, 02 3개중 하나의 값으로 구성되므로 선택도는 33.33% 이 된다 

(MOD 함수 사용)

 

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)), 3)), 2, '0'), -- SALE_GB
    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_1000, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10000);
								
COMMIT;				

 

기본키 생성

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

 

선택도 : SALE_GB 컬럼의 선택도는 33.33%

SELECT
ROUND(1/COUNT(DISTINCT SALE_GB) * 100, 2) 선택도
FROM TB_ORD;

 

인덱스 생성 : SALE_GB 컬럼으로 구성된 인덱스 생성

CREATE INDEX TB_ORD_IDX01 ON TB_ORD(SALE_GB);

 

통계정보 생성

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

---

튜닝 전 상황

 

튜닝 전 SQL문

SELECT
/*+ INDEX(TB_ORD TB_ORD_IDX01) */
*
FROM TB_ORD
WHERE SALE_GB IN ('01', '02');

수행 시간 : 0.004 초

 

INDEX 힌트를 사용해서 TB_ORD_IDX01 인덱스 스캔을 하도록 유도

 

[SQL 문제점]

SALE_GB 컬럼은 33.33%로 선택도가 높은 컬럼임에도 인덱스 스캔을 했다.
테이블 풀 스캔은 테이블 랜덤 액세스를 안하므로 이런 경우에는 오히려 테이블 풀 스캔이 더 성능이 좋다.
인덱스 스캔 시 싱글 블록 I/O 읽기를 하게 되면 테이블 랜덤 액세스를 하므로 성능 부하가 있는 SQL문이 된다.
이런 경우에는 인덱스 스캔을 한다고 하더라도 반드시 효율적인 SQL문이라고 볼 수 없다

 

실행 계획

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

3 : TB_ORD 테이블을 인덱스 범위 스캔한다

2 : TB_ORD_IDX01 인덱스 스캔 후 나온 ROWID를 이용해서 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID) 를 수행한다. 

1 : IN 조건 안에 있는 SALE_GB 컬럼의 값을 기준으로 ID 3번과 2번을 반복하게 된다. 

IN 조건을 사용할 경우 오라클은 내부적으로 IN절 안에 있는 값을 기준으로 반복해서 비교하게 된다.
즉, 처음에는 SALE_GB = '01' 로 비교하고 다음에는 SALE_GB = '02' 로 비교하게 된다.
=' 연산으로 N번 비교하는게 특징이고, 이러한 연산을 인 리스트 반복자(INLIST ITERATOR)라고 한다

0 : SELECT 절의 연산 수행

 

---

1차 튜닝

튜닝 후 SQL문

SELECT /*+ FULL(TB_ORD) */
*
FROM TB_ORD
WHERE SALE_GB IN ('01', '02');

수행 시간 : 0.004 초

 

TB_ORD 테이블을 풀 스캔하기 위해 FULL 힌트를 줬다.

테이블 전체를 멀티 블록 I/O 읽기 방식으로 읽었고,

테이블 랜덤 액세스도 발생하지 않아  TB_ORD_IDX01 인덱스를 사용한 인덱스 스캔보다 성능이 빨라졌다. 

(*) CPU 성능에 따라, 월등히 차이가 난다. 

 

실행 계획

1 : TB_ORD 테이블을 테이블 풀 스캔한다.

0 : SELECT 절의 연산 수행