3.2 부분범위 처리 활용
3.1에서 테이블 랜덤 액세스가 미치는 성능 영향, 최소화 하기 위해 인덱스에 컬럼 추가 하는 방법
3.2에서 테이블 랜덤 액세스로 인해 인덱스 손익분기점의 한계를 극복하는 방법.
3.2.1 부분범위 처리
DBMS가 클라이언트에 데이터를 전송할 때도 일정량씩 나누어 전송한다. Fetch Call을 받기 전까지 대기함.
서버 프로세스는 CPU를 OS에 반환하고 대기 큐에 sleep 상태로 변함.
이처럼 일정량씩 나누어 전송하는 것을 ‘부분범위 처리’ 라고 한다.
정렬조건이 있을때 부분범위 처리
모든 데이터를 다 읽어 created 순으로 정렬을 마치고 클라이언트에게 데이터 전송을 시작할 수 있다. = 전체범위처리. Sort Area 와 Temp 테이블스페이스까지 정렬을 마치면 그때부터 일정량씩 나눠 클라이언트에게 데이터를 전송한다.
create 컬럼이 선두인 인덱스가 있다면 인덱스는 항상 정렬된 상태를 유지하므로 부분범위 처리가 가능하다.
Array Size 조정을 통한 Fetch Call
3.2.3 OLTP 환경에서 부분범위 처리에 의한 성능개선
OLTP : Online Trasaction Processing 줄임말. 온라인 트랜젝션 처리하는 시스템.
상황에 따라 수천수만 건을 조회하는 경우도 있다. 많은 건수 조회하려면 많은 테이블 랜덤 액세스가 발생하여 오래 기다려하는 상황이 있다.
상위 일부 데이터만 확인하는 (입출금 조회, 뉴스, 게시판) 경우 앞쪽만 빠르게 보여줘도 된다.
인덱스 선두 컬럼 구성하여 소트 연산을 생략.
3.3.3 엑세스 조건과 필터 조건
엑세스 조건 : 인덱스 스캔 범위를 결정, 인덱스 수직적 탐새을 통해 스캔 시작점을 결정하고 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는데 영향을 미치는 조건절
인덱스 필터 조건 : 테이블로 액세스 할지를 결정하는 조건절
테이블 액세스 단계에서 조건절은 모두 필터 조건이다. 테이블 필터 조건은 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지 결정
3.3.4 비교 연산자 종류와 컬럼 순서에 따른 군집
같은 값을 찾을 때 ‘=’ 사용. 인덱스 컬럼 앞쪽부터 ‘=’ 조회하면 조건절을 만족하는 레코드가 모여있다. 하나를 누락한 경우 흩어지는 상태 됨
between, like, 부등호
3.3.5 인덱스 선행 컬림이 등치(=) 조건이 아닐 때 생기는 비효율
인덱스 효율성은 등치(=) 조건으로 사용할 때 가장 좋음.
리프 블록을 스캔하면서 읽은 레코드는 하나도 걸러지지 않고 모두 테이블 액세스로 이어지므로 인덱스 스캔 단계에서의 비효율은 없다.
뒷쪽이 등치 조건이 아닌 경우도 비효율은 없음. 선행 컬럼이 조건절에 없거나 부등호, betweenm like 같은 범위 검색 조건이면, 비효율이 생긴다.
BETWEEN 을 IN-LIST 전환
인덱스 구성을 바꾸기 쉽지 않기에 IN-LIST로 바꿔주면 큰 효과가 있다.
IN-LIST 개수 만큼 UNION ALL 브랜치가 생성되고, 각 브랜치 마다 모든 컬럼을 ‘=’ 조건으로 검색하므로 BETWEEN 같은 비효율이 사라진다.
--IDX : 인터넷매물, 평형 ...
SELECT ...
FROM 아파트매매
WHERE 인터넷매물 IN ('1', '2', '3')
AND 시세코드 = '123123'
AND 평형 = '59'
-- BETWEEN 경우 1,2,3 전체 탐색함.
-- IN-LIST 변경
--옵티마이저는 UNION ALL 실행계획 변경
SELECT ...
FROM 아파트매매
WHERE 인터넷매물 = '1'
AND 시세코드 = '123123'
AND 평형 = '59'
UNION ALL
SELECT ...
FROM 아파트매매
WHERE 인터넷매물 = '2'
AND 시세코드 = '123123'
AND 평형 = '59'
UNION ALL
SELECT ...
FROM 아파트매매
WHERE 인터넷매물 = '3'
AND 시세코드 = '123123'
AND 평형 = '59'
BETWEEN을 IN-LIST 전환시 주의사항
IN-LIST 개수가 많지 않아야 하고, 많을 경우 수직적 탐색이 많이 발생하므로 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다.
인덱스 리프 블록이 멀리 떨어져 있는 경우 IN-LIST
가까이 있는 경우 BETWEEN 효과적이다.
3.3.7 Index Skip Scan 활용
Between 조건을 IN-LIST 변환하면 도움이 되는 상황에서 조건절을 바꾸지 않고 효과를 낼 수 있음.
select /*+ INDEX_SS(t 월별고객별판매집계_IDX2) */ .....
from ...
인덱스 첫번째 컬럼이 where 조건절에 없고, 두번째 컬럼 있을 경우 사용하면 효과
3.3.8 IN 조건은 ‘=’ 인가
테이블의 레코드가 연속되지 않는 리프로 인덱스 구성으로 저장되어 있다면, IN-LIST Iterator 방식으로 풀리는 것이 효과적이다. IN-LIST Iterator 방식으로 풀린다는건 IN 조건이 ‘=’ 조건이 된다는 뜻
SELECT * FROM ..
UNION ALL
SELECT * FROM ...
UNION ALL
SELECT * FROM ..
인덱스를 수직적으로 세 번 탐색한다. IN-LIST Iterator 방식으로 풀지 않는다면 테이블 전체 또는 인덱스 전체를 스캔하면서 필터링해야한다.
💡 유니크한 컬럼에 in 절 사용해 IN-LIST Iterator 방식으로 풀리는건 효과적이지 않음.
NUM_INDEX_KEYS
- 고객번호 + 상품ID 인덱스 구성
- num_index_keys(table_alias, index_name, index_col) 구성
SELECT /*+num_index_keys(a 고객상품_X1 1)
FROM 고객별상품가입
WHERE 고객번호 = :cust_no
and 상품ID = ('.', '..', '...')
‘1’은 인덱스 첫 번째 컬럼까지만 엑세스 조건으로 사용하라는 의미
상품ID까지 인덱스 엑세스 조건으로 사용하려면 ‘2’ 수정하면됨.
3.3.9 BETWEEN과 LIKE 스캔 범위
LIKE와 BETWEEN은 둘다 범위검색 조건이다. LIKE보다 BETWEEN을 사용하는것이 더 좋음.
LIKE로 개발하는 경우 해당 컬럼이 인덱스 구성 컬림일떄는 주의가 필요하다.
3.3.11 옵션 조건 처리
OR 조건은 선두에 두고 사용하면 인덱스를 만들어 놔도 사용할 수 없게 된다.
SELECT * FROM 고객
where (:cust_id is null or 고객ID = :cust_id)
and 거래일자 between :dt1 and :dt2
고객ID + 거래일자 순으로 인덱스를 구성해도 사용이 불가능하다. 인덱스 스캔 단계에서 필터링 해도 비효율 적인데, 테이블 액세스 단계에서 필터링한다.
거래일자 between 조건을 찾기위해 인덱스에 100만건 스캔하면, 그만큼 테이블을 랜덤 액세스 후 고객ID를 필터링한다.
- 인덱스 엑세스 조건으로 사용불가
- 인덱스 필터 조건으로 사용불가
- 테이블 필터 조건으로 사용가능
3.4 인덱스 설계
3.4.1 인덱스 설계 어려운 이유
몇 가지 공식만 알면 되지만, 현실적으로 인덱스가 수십 개씩 달리고 관리비용, 시스템 부하를 증가시키는 요인이 됨.
- DML 성능저하
- 데이터베이스 사이즈 증가
- 데이터베이스 관리 및 운영 비용 상승
테이블에 인덱스가 여섯개 달려있으면, 신규 데이터를 입력할때마다 여섯 개 인덱스에도 모두 입력해야한다.
테이블과 달리 인덱스는 정렬 상태를 유지해야하므로 수직적 탐색을 통해 블록을 찾지만 여유 공간이 없을 경우 인덱스 분할도 발생함. 삭제할 때도 마찬가지다.
💡 인덱스 개수를 최소화하려면 기존 인덱스 구성을 변경하면 되지만 운영단계에서는 힘듬
3.4.2 중요한 두가지
- 가장 정상적이고 일반적인 방식은 Index Range Scan이다. 인덱스 선두 컬럼을 조건절에 반드시 사용하여야 하고 따라서 “인덱스 구성할때 조건절에 항상 사용하거나 자주 사용하는 컬럼을 선정”
- 그렇게 선정한 컬럼중 ‘=’ 조건을 자주 조회하는 컬럼을 앞쪽에 두어 사용한다.
3.4.3 스캔 효율성 이외 판단 기준
위 두가지 기준과 그 외 고려해야할 판단 기준
- 수행 빈도
- 업무상 중요도
- 클러스터링 팩터
- 데이터량
- DML 부하
- 저장공간
- 인덱스 관리 비용 등
설계자의 성향이나 스타일에 따라 결과물이 달라진다. 이중 중요한 하나를 꼽으면 수행 빈도다.
NL조인할 때 Outer쪽에서 액세스하는 인덱스는 스캔과정에서 비효율이 있더라도 큰 문제가 아닐수도 있음.
3.4.4 설계
가계약이라는 테이블을 생성 후 인덱스 추가하려는데 BETWEEN(일자) 조건 컬럼 뒤에 인덱스를 24개 필요하다. INSERT가 매우 많이 발생하는 상황에서 24개를 모두 생성하는건 비효율적이다.
일자/일시 를 조건 선두에 두고, 자주 사용하는 필터 조건을 모두 뒤쪽에 추가하는 방식
- X01 : 청약일자 + 취급부서 + 취급지점 + 취급자 …
- X02 : 보험개시일자 + 취급부서 + 취급지점 + 취급자 …
- X03 : 보험종료일자 + 취급부서 + 취급지점 + 취급자 …
- X04 : 데이터생성일시 + 취급부서 + 취급지점 + 취급자 …
일자 조회구간이 길지 않으면 인덱스 스캔 비효율이 적음.
인덱스 스캔 효율보다 테이블 액세스가 더 큰 부하요소다.
3.4.5 소트 연산을 생략하기 위한 컬럼 추가
인덱스는 항상 정렬 상태를 유지하므로 ORDER BY, GROUP BY를 위한 소트 연산을 생략할 수 있게 해준다.
I/O를 최소하하면서 소트 연산을 생략하려면
- ‘=’ 연산자로 사용한 조건절 컬럼 선정
- ORDER BY 절에 기술한 컬럼 추가
- ‘=’ 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정
3.4.6 결합 인덱스 선택도
선택도란 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율이고 선택도에 총 레코드 수를 곱해 ‘카디널리티’를 구한다.
