January
9th,
2021
DB SCAN
오라클 옵티마이저를 통해서 어떤 순서로 scan을 할지 결정하게 된다.
인덱스를 이용한 DB 탐색 방법은 full scan, range scan, skip scan 등이 있다.
INDEX_FULL SCAN
- 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
- 선두 컬럼이 조건절에 없으면 옵티마이저가 index full scan을 고려한다.
- 인덱스 컬럼 순으로 정렬된다.
- Table Full Scan이 유리한 상황이 와도 Order By가 있다면 소트 연산을 생략하기 위해 Index Full Scan을 선택할 경우도 있다.
RANGE SCAN
- B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이다.
- 수직적 탐색 후 필요한 범위만 수평적으로 탐색한다.
- 인덱스 스캔 범위와 테이블 액세스 횟수를 줄이는 것이 성능 향상의 길이다.
UNIQUE SCAN
- 수직적 탐색으로만 데이터를 찾는 방식
- Unique 인덱스를 = 조건으로 탐색하는 경우
- Unique 인덱스가 존재하는 컬럼은 중복값 없이 입력되지 않게 DBMS가 정합성 관리를 해준다.
- Unique 인덱스가 존재해도 Between, Like 등의 범위 조건으로 검색하면 Index Range Scan을 한다.
- Unique 결합 인덱스도 검색 할 때, Index Range Scan을 사용한다.
SKIP SCAN
- 오라클 9i 버전에서 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 Index Skip Scan을 사용
- 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 갯수가 많을 때 유용
- 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 액세스
Skip Scan 예시
- Distinct Value 개수가 가장 적은 컬럼은 성별
- Distinct Value 개수가 가장 많은 컬럼은 고객번호
SELECT /*+ index_ss(사원 사원_idx) */ *
FROM 사원
WHERE 연봉 BETWEEN 2000 AND 4000
- ‘남’ 보다 작은 값이 있을까봐 첫번째 리프 블록 액세스
- 연봉>= 800인 2번째 리프블록은 skip
- 연봉 >= 1500인 3번째 리프블록 다음이 연봉 >= 5000이기 때문에 조건을 만족하는 값이 있을 가능성이 있어 3번째 리프 블록 액세스
- 연봉 >= 5000인 4번째 리프블록은 조건에 만족하지 않기 때문에 Skip
- 5번째 블록도 Skip
- 6번째 리프 블록은 >= 10000이라 skip이 될것같지만 성별이 바뀌기 때문에 액세스
- 7번째 블록은 성별 상관없이 연봉 >= 3000이고, 다음 레코드는 연봉 >= 5000이기 때문에 액세스
- 8번 ~ 9번 블록은 연봉 >= 5000, 7000이기 때문에 Skip
- 10번 블록은 연봉 >= 10000으로 조건에 만족하지 않지만 다음 성별이 있을 수 있으니 액세스
예시
NAME | TYPE |
---|---|
EMPLOYEE_NUM | NUMBER |
NAME | VARCHAR2 |
인덱스는 (EMPLOYEE_NUM, NAME)
SELECT *
FROM employee
WHERE name = "kong"
이 경우 선행 칼럼(EMPLOYEE_NUM)의 조건이 주어지지 않고, 이름으로만 조회를 시도하므로 인덱스를 경유해서 데이터를 액세스하지 않고 TABLE FULL SCAN을 한다. 때문에 선행
칼럼이 조건에 있어야하는데
SELECT *
FROM employee
WHERE employee_num IN (SELECT employee_num FROM employee)
AND name = "kong"
위와 같이 선행 인덱스가 되는 조건을 추가하면 employee_num으로 인덱스를 탄 뒤에 name을 scan하여 조회할 수가 있다. 그러나 이러한 경우 선행 칼럼의 구성요소가 많아지면 데이터베이스가 주어진 칼럼들을 곱한 각 경우의 수만큼 equal match를 시도하게 되는 문제가 있다. subquery를 사용할 수 없는 경우에도 문제가 있다.
skip scan은 이러한 선행 인덱스 문제를 해결해준다.
employee_num이 1, 2 두개의 데이터를 가지고 있는 경우라면 skip scan을 할때는 다음과 같다고 생각할 수 있다
SELECT *
FROM employee
WHERE employee_num = 1
AND name = "kong"
UNION ALL
SELECT *
FROM employee
WHERE employee_num = 2
AND name = "kong"