학습목표
- 인덱스 스캔의 효용성에 대해 자세히 알아본다
- 인덱스 풀 스캔(Index Full Sacn)과 인덱스 패스트 풀 스캔(Index Fast Full Scan)의 장단점 및 사용 방법에 대해 자세히 알아본다
- 인덱스 스캔보다 테이블 풀 스캔(Table Full Scan)이 유리한 상황을 알아본다
인덱스와 테이블의 관계
- 인덱스와 테이블은 각각의 객체(Object)
- 인덱스와 테이블은 논리적/물리적으로 완전히 분리되어 있음
- 인덱스 스캔이 성공하면 해당 ROWID를 이용하여 테이블 액세스를 하게 됨
- 이러한 연산을 테이블 랜덤 액세스(Table Random Access)라고 함
- 테이블 랜덤 액세스가 많아지면 시스템에 많은 부하를 줌
- 테이블 랜덤 액세스를 줄이는 것이 인덱스 스캔 튜닝의 핵심 이슈
인덱스 Selectivity
- 인덱스 검색시, 적절한 매칭도를 가져야만 인덱스로써 의미를 가짐
- 인덱스 매칭도가 현저히 떨어질 때는 오히려 인덱스로 인한 비효율이 발생됨
SELECT COUNT(*)
FROM EMPLOYEE A
WHERE A.DEPT = 'HR'
AND A.YEARS = '2023'
AND A.SAL > 2000;
--IDX01 : DEPT + MGR + YEAR
--IDX02 : DEPT + SAL + YEARMatching 조건(ACCESS_PREDICATE)
- 조건이 인덱스 구성 컬럼과 일치하여 스캔해야 할 범위를 결정하는 조건
- 위 IDX01에서는 DEPT만 Matching 조건(MGR이 WHERE 조건에 없기 때문)
Check 조건(FILTERED_PREDICATE)
- 테이블에 접근할지 말지를 결정하는 조건
- 스캔범위를 결정하는데 참여할 수 없음
- 인덱스 데이터를 이용하여 조건의 True/False를 판별
인덱스 매칭도
- 인덱스 선두 컬럼부터 조건이 빠짐이 없어야 한다.
- 인덱스 구성 순서상에서 Equal조건(=, in)에 해당하는 인덱스 컬럼과 바로 그 뒤에 위치하는 범위(Between, 부등호)를 의미하는 컬럼까지 해당
- 인덱스 구성 순서상에서 범위를 의미하는 컬럼의 다음에 오는 인덱스 컬럼은 어떠한 조건이 부여되더라도 Matching컬럼이 되지 못한다.
- ex) IDX02에서 YEAR는 Matching조건이 안 됨(SAL이 범위 조건이기 때문)
Check 조건의 활용
- 조건이 False인 경우 해당 건에 대하여 테이블을 찾아가지 않아도 되므로 Performance측면에서는 중요
인덱스 컬럼 가공사례와 튜닝 방안
인덱스 컬럼을 가공하거나 변형하면 제대로 타지 않는다.
-- 예시 1
SELECT * FROM 업체
WHERE SUBSTR(JOB, 1, 4) = 'SALE' --BAD
WHERE JOB LIKE 'SALE%' --GOOD
-- 예시 2
SELECT * FROM 사원
WHERE 월급여 * 12 = 36000000 --BAD
WHERE 월급여 = 36000000/12 --GOOD
-- 예시 3
SELECT * FROM 고객
WHERE 연령 || 직업 = '30공무원' --BAD
WHERE 연령 = 30 AND 직업 = '공무원' --GOOD
-- 예시 4 (회원번호 3자리, 지점번호 1자리)
:STR = 1001
SELECT * FROM 회원사지점
WHERE 회원번호 || 지점번호 = :STR --BAD
WHERE 회원번호 = SUBSTR(:STR, 1, 3) --GOOD
AND 회원번호 = SUBSTR(:STR, 4, 1)
-- 예시 5 (지수구분코드 1자리, 지수업종코드 3자리)
SELECT * FROM 주문
WHERE 거래일자 BETWEEN '20230101' AND '20230331'
AND 지수구분코드 || 지수업종코드 IN (('1001'),('2003')) --BAD
AND (지수구분코드, 지수업종코드) IN (('1','001'),('2','003')) --GOOD
GROUP BY 거래일자
-- 예시 6
SELECT * FROM 주문
WHERE NVL(주문수량,0) >= 100 --BAD
WHERE 주문수량 >= 100 --GOOD