- 인덱스를 사용하면 데이터가 많아도 금방 조회가 되는 경우가 있는데, 대량 데이터를 조회할 때 인덱스를 사용하니 테이블 전체를 스캔할 때 보다 훨씬 느리다
ROWID
- 인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빠르게 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값인 ROWID를 얻기 위함임
- ROWID는 데이터파일 번호, 오브젝트 번호, 블록 번호와 같은 물리적인 요소로 구성되어 있긴 하지만, 물리적이라기 보단 논리적인 주소에 가까움
→ 물리적으로 직접 연결되지는 않고, 디스크 상의 실제 테이블 레코드를 찾아가기 위한 논리적인 주소를 담고 있음
I/O 매커니즘
- DBA는 디스크 상에서 블록을 찾기 위한 주소 정보임
- 매번 디스크에서 블록을 읽을 수 없으므로, I/O성능을 높이려면 버퍼캐시를 활용해야 한다. 그래서 블록을 읽을 때는 바로 디스크로 가기 전에 버퍼캐시 부터 찾음
- 인덱스 스캔 → 리프블록 → ROWID → DBA → 해시함수(해싱알고리즘) → 버퍼헤더(메모리상 주소 = 포인터) →버퍼블록 접근
=> ROWID가 가리키는 테이블 블록을 버퍼캐시에서 먼저 찾아보고, 못 찾을 때만 디스크에서 블록을 읽음
=> ROWID를 이용하는 테이블 액세스는 고비용임
클러스터링 팩터
- 클러스터링 팩터, CF, 군집성 계수
- 특정 컬럼을 기준으로 값을 값을 가지는 데이터가 서로 모여있는 정도
- CF가 좋은 컬럼에서 생성한 인덱스를 검색 효율이 매우 좋음
- 데이터들이 물리적으로 근접해 있으면 흩어져 있을 때보다 데이터를 찾는 속도가 빠름 (근접해 있으면 블록 I/O과정 없이 포인터로 바로 액세스 가능)
인덱스 손익분계점
- 인덱스 ROWID를 사용한 테이블 액세스는 고비용 구조임
- 읽어야 하는 데이터의 개수가 일정량을 넘는 순간, Index Range Scan보다 그냥 Table Full Scan이 더 빠름
- Table Full Scan은 항상 테이블 전체를 읽기 때문에, 한 테이블에서 몇 건을 조회하든 성능이 일정함
- 인덱스를 이용한 조회에서는 전체 데이터 중에서 몇 건을 조회하는냐에 따라 성능이 크게 달라짐
- 추출 건수가 늘어나면 인덱스 스캔량도 늘고, 테이블 랜덤 엑세스가 늘어나기 때문
- Table Full Scan은 시퀀셜 액세스, 인덱스는 랜덤 액세스
- Table Full Scan은 멀티블록I/O, 인덱스는 싱글블록I/O
인덱스 손익분기점
=> CF에 따라 달라지지만, 인덱스의 손익 분기점을 5~20%(전체데이터 대비 추출 건수)의 낮은 수준임
- 여기서 말하는 5~20% 수준의 손익 분기점을 10만건~100만건 사이의 테이블에나 적용되는 수치임
- 1000만건 수준의 테이블에서는 손익분기점이 더 낮아짐
- 10만건~100만건 테이블을 조회할 때에는 버퍼캐시에서 데이터를 찾을 가능성이 있지만.... 1000만건 테이블을 조회하는 경우 버퍼캐시에서 데이터를 찾을 가능성이 매우매우매우 낮아서, 거의 모든 데이터를 디스크에서 읽기 때문에, 손익분기점 자체가 의미가 없어져서, 인덱스를 이용하는 것 보다 그냥 Table Full Scan이 더 빠름
- 인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 찾고자 하는 데이터가 전체 구간에 흩어져 있어서 Index Range Scan이 불가능하거나 비효율적임
- 인덱스가 정렬되어 있다는 특성을 활용하여 소트 연산을 생략할 수 있는 효과를 가질 수 있음
Ex)
인덱스 : 장비번호+변경일자+변경순번
select *
from 상태변경이력
where 장비번호 = 'C'
and 변경일자 = '20200622'
- 해당 쿼리의 결과는 장비번호가 C이고 변경일자가 20200622인 것들을 변경순번 순으로 조회함
- 이때 ORDER BY연산을 하지 않음 (ORDER BY를 명시해도 수행하지 않음)
select *
from 상태변경이력
where 장비번호 = 'C'
and 변경일자 = '20200622'
order by 변경순번 desc
- 내림차순 정렬을 요구하는 경우 인덱스 스캔을 반대로 함
select *
from 상태변경이력
where 장비번호 = 'C'
order by 변경일자 || 변경순번
- 이런 경우 인덱스를 활용한 소팅 연산을 생략할 수 없음
- 인덱스에는 가공되지 않은 값을 저장했는데, 가공한 값( || )으로 정렬을 해달라 해서..
Ex
인덱스 : 주문일자+주문번호
select to_char(A.주문번호, 'FM000000') as 주문번호, A.업체번호, A.주문금액
from 주문 A
where A.주문일자 = dt
and A.주문번호 > NVL(next_ord_no, 0)
order by 주문번호
- 이 쿼리도 인덱스를 이용한 소팅 연산 생략 불가
- to_char로 가공된 값인 주문번호로 정렬을 해달라고 했기 때문
select to_char(A.주문번호, 'FM000000') as 주문번호, A.업체번호, A.주문금액
from 주문 A
where A.주문일자 = dt
and A.주문번호 > NVL(next_ord_no, 0)
order by A.주문번호
- order by의 주문번호에 A. 를 붙이면 인덱스를 이용한 소팅 생략 가능
Ex
인덱스 : 장비번호+변경일자+변경순번
select MIN(변경순번)
from 상태변경이력
where 장비번호 = 'C'
and 변경일자 = '20200622'
select MAX(변경순번)
from 상태변경이력
where 장비번호 = 'C'
and 변경일자 = '20200622'
- 최솟값/최대값을 구할 때 소팅 연산이 생략됨
- 최소값 : 수직적 탐색으로 가장 왼쪽으로 내려가서 읽는 첫 번째 레코드(FIRST ROW)
- 최댓값 : 수직적 탐색으로 가장 오른쪽으로 내려가서 읽는 첫 번째 레코드(FIRST ROW)
select NVL(MAX(TO_NUMBER(변경순번)), 0)
from 상태변경이력
where 장비번호 = 'C'
and 변경일자 = '20200622'
select NVL(TO_NUMBER(MAX(변경순번)), 0)
from 상태변경이력
where 장비번호 = 'C'
and 변경일자 = '20200622'
- 첫 번째 쿼리는 문자 타입의 변경순번 컬럼을 TO_NUMBER로 가공했기 때문에 소팅 연산 생략 불가
- 정상적으로 사용 = 수직적 탐색을 통해 Leaf블록에서 스캔 시작점을 찾고 거기서부터 수평적 탐색을 하다가 멈추는 것
2. Index Full Scan
- 인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고, 모든 Leaf 블록을 전부 스캔해야 함
=> 인덱스 컬럼을 가공하면 Index Range Scan을 사용할 수 없음
- 인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문임
- Index Range Scan을 특정 Range만 스캔한다는 것으로 Range에는 시작점과 끝점이 있음
- 인덱스는 가공되지 않은 값이 저장되어 있는데, 가공된 값을 가지고 검색을 하려면 시작점을 특정 지을 수 없음
- OR와 IN은 옵티마이저의 쿼리 변환 기능을 통해 Range Scan이 가능할 수도 있음
where substr(생년월일, 5, 2) = '05'
-- 5월에 태어난 사람, substr로 가공함
where nvl(주문수량, 0) < 100
-- nvl로 가공함
where like '%대한%'
-- '대한'으로 시작하면 Range Scan이 가능하지만 '대한'을 포함하는 값은 흩어져 있음
where (전화번호 = tel_no OR 고객명 = cust_nm)
-- 수직적 탐색을 통해 전화번호가 '01012345678' 이거나 이름이 '홍길동'인 어느 한 지점을 특정할 수 없음
where 전화번호 in (tel_no1, tel_no2)
-- in은 or와 같은 것임
인덱스 사용 조건
- Index Range Scan을 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 함
Ex:
인덱스 : [소속팀 + 사원명 + 연령]
= 데이터를 소속팀 순으로 정렬하고, 소속팀이 같으면 사원명으로 정렬하고, 사원명도 같으면 연령으로 정렬함
select 사원번호, 소속팀, 연령, 입사일, 전화번호
from 사원
where 사원명 = '홍길동'
-- 인덱스 선두 컬럼인 소속팀이 조건절 첫 줄에 없으므로 Index Range Scan은 불가능
-- 이름이 같은 사원이더라도 소속팀이 같으면 멀리 떨어져 있으므로 Index Full Scan해야함 (Leaf 블록 전 구간에 흩어짐)
Ex:
인덱스 : [기준연도 + 과세구분코드 + 보고회차 + 실명확인번호]
select * from TXA1234
where 기준연도 = stdr_year
and substr(과세구분코드, 1, 4) = txtn_dcd
and 보고회차 = rpt_tmrd
and 실명확인번호 = rnm_cnfm_no
-- 인덱스 컬럼 중하나인 과세구분코드가 substr로 가공되었지만,
-- 인덱스 선두 컬럼인 기준연도는 가공되지 않은 상태로 조건절에 있어서 Index Range Scan이 가능
- 인덱스를 탄다라는 말은 Index Range Scan을 한다는 뜻인데, Index Range Scan을 한다고 무조건 성능이 좋은 것이라고 할 수 없음
- Index Range Scan을 하며 인덱스를 잘 타는 것 같아도, 실제로 인덱스를 정말 잘 활용하는지는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 함
