테이블 풀 스캔 튜닝
인덱스 스캔
개발자를 위한 오라클 SQL 튜닝 내용에서 참고한 내용입니다.
선택도
인덱스 스캔이 테이블 풀 스캔에 비해 반드시 유리하진 않는다.
선택도가 낮은 컬럼(SEX)을 인덱스로 생성하여 인덱스 스캔을 수행하는 경우, 테이블 전체 행들의 50%에서 인덱스 스캔을 통한 테이블 랜덤 액세스가 발생하게 된다.
일반적인 인덱스 스캔은 싱글 블록 I/O 읽기 인데, 테이블 풀 스캔은 멀티 블록 I/O 읽기이다.
두 가지 이유로 SEX라는 컬럼을 조건절에 조건을 주어 스캔할 경우 인덱스 스캔보다 테이블 풀 스캔이 더 유리하다.
선택도(Selectivity) 구하는 공식
선택도(%) = ( 1 / Distinct Value ) * 100
인덱스 손익 분기점
인덱스 손익 분기점은 인덱스 스캔보다 테이블 풀 스캔이 유리한 선택도의 수치를 의미한다.
일반적으로 선택도가 15%보다 크다면 테이블 풀 스캔이 유리하고, 그 이하라면 인덱스 스캔이 더 유리하다.
테이블 풀 스캔 튜닝
테이블 풀 스캔 튜닝은
선택도가 높은 컬럼의 인덱스를 사용
하여 인덱스 스캔을 하는 SQL문을 강제로 테이블 풀 스캔으로 처리하도록 하는 기법즉, 손익 분기점을 넘어 인덱스 스캔하기에는 비효율적인 SQL을 강제로 테이블 풀 스캔을 하도록 처리하는 것이다.
실습
테이블 풀 스캔을 유도하여 비효율적인 인덱스 스캔 예방하기
테이블 생성
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;
ALTER TABLE TB_ORD NOLOGGING;
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);
선택도 확인
SELECT ROUND(1 / COUNT(DISTINCT SALE_GB) * 100, 2) 선택도
FROM TB_ORD;
인덱스 생성
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');
SQL 분석
INDEX 힌트를 사용하여 TB_ORD_IDX01 인덱스 스캔을 하도록 유도한다.
SQL 문제점
SALE_GB 컬럼은 33.3%로 선택도가 높은 컬럼임에도 인덱스 스캔을 하였다.
테이블 풀 스캔은 테이블 랜덤 액세스를 하지 않으므로 이런 경우에는 오히려 테이블 풀 스캔이 더 성능이 좋다.
인덱스 스캔 시 싱글 블록 I/O 읽기를 하게 되면 테이블 랜덤 액세스를 하므로 성능 부하가 있는 SQL문이 된다.
이런 경우 인덱스 스캔을 한다고 하더라도 반드시 효율적으로 SQL문이라고 볼 수 없다.
Execute Plan
TB_ORD 테이블을 인덱스 범위 스캔(INDEX RANGE SCAN) 한다.
TB_ORD_IDX01 인덱스 스캔 후 나온 ROWID를 이용하여 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID)를 수행한다.
IN 조건 안에 있는 SALE_GB 컬럼의 값을 기준으로 ID 3번과 2번을 반복하게 된다.
IN 조건을 사용할 경우 오라클은 내부적으로 IN절 안에 있는 값을 기준으로 반복해서 비교하게 된다.
즉, 처음에는 SALE_GB = '01'로 비교하고 다음에는 SALE_GB = '02'로 비교하게 된다.
'=' 연산으로 N번 비교하는 것이 특징, 이런 연산을 인 리스트 반복자(INLIST ITERATOR)라고 한다.
튜닝 후 상황
튜닝 후 SQL
SELECT /*+ FULL(TB_ORD) */
*
FROM TB_ORD
WHERE SALE_GB IN ('01', '02');
SQL 분석
TB_ORD 테이블을 풀 스캔하기 위해서 FULL 힌트사용
테이블 전체를 멀티 블록 I/O 읽기 방식으로 읽었으며, 테이블 랜덤 액세스도 발생하지 않아 TB_ORD_IDX01 인덱스를 사용한 인덱스 스캔보다 성능이 빨라졌다.
Execute Plan
TB_ORD 테이블을 테이블 풀 스캔(TABLE ACCESS FULL)한다.
SELECT 절의 연산을 수행한다.
Last updated
Was this helpful?