대용량 테이블에서 필요한 데이터를 빠르게 찾기위해서는 인덱스의 도움이 필요합니다. 적절한 인덱스가 없다면 테이블 전체를 읽어야 하기에 많은 시간이 소요됩니다.
인덱스 기본구조
일반적인 인덱스는 B*TREE 구조입니다. 최상단 루트노드에서 브랜치노드를 거쳐 최하단 리프노드까지 연결되는 구조입니다. 인덱스 블록(=노드)의 레코드는 컨텐츠(=KEY)와 페이지(=ROWID)로 구성됩니다. 루트와 브랜치 블록에는 각 하위 노드의 값의 범위인 키, 하위 노드의 주소정보를 페이지(=ROWID)가 있습니다.
블록내부의 레코드는 KEY 값을 기준으로 항상 정렬해있습니다. 정렬해있기때문에 범위스캔이 가능하고 연결 리스트 구조이기 때문에 정방향/역방향 탐색이 가능합니다. 인덱스 레코드의 크기는 데이터레코드의 1/3 수준으로 하나의 데이터 블록에 데이터 레코드의 3배만큼의 인덱스 레코드를 저장할 수 있습니다.
ORACLE에서 인덱스 생성시, 인덱스 구성 칼럼의 값이 모두 NULL인 레코드는 인덱스에 저장하지 않습니다. 하나라도 NULL이 아닌 데이터레코드를 인덱스로 만들 수 있습니다.
인덱스 생성직후에는 루트블록과 리프블록이 동일합니다. 하나의 블록에 저장가능한 레코드 수를 넘으면 루트블록과 리프블록이 분리됩니다. 브랜치 블록의 깊이가 N 이면 인덱스 블록에 저장가능한 레코드수 의 N제곱 만큼의 인덱싱이 가능합니다.
루트 블록은 인덱스 트리의 최상위 블록으로 각 레코드에 하위 블록의 값의 범위와 블록에 대한 주소가 있습니다. 루트블록에는 하위 블록의 수만큼의 인덱스가 존재합니다. 브랜치 블록은 루트 블록과 리프 블록 사이에 위치하는 블록으로 레코드에 하위 블록의 값의 범위와 주소가 있습니다.
각 브랜치 블록에는 하위 브랜치블록 혹은 리프 블록의 수 만큼의 레코드가 있습니다. 리프 블록의 레코드에는 인덱스 구성칼럼의 값인 키와 데이터레코드가 저장된 위치값(=ROWID) V으로 구성돼있습니다. 키값 순서대로 정렬해있고 전후의 리프블록 끼리 연결돼있습니다.
인덱스 탐색
인덱스탐색은 수직적 탐색과 수평적 탐색으로 구분할 수 있습니다.
수평적 탐색은 인덱스 리프블록에 저장된 레코드끼리 연결된 순서에 따라 스캔합니다. 수직적 탐색은 수평적 탐색의 시작점을 찾는 과정입니다. 루트블록에서 리프블록으로 하향식 탐색합니다. 수평적 탐색은 시작점에서 시작해 끝점을 찾는 과정입니다. 전체 데이터 대비 찾는 데이터의 수가 10%이내이면 전체스캔보다 인덱스를 활용한 스캔이 효과적입니다.
RANDOM ACCESS와 SEQUENTIAL ACCESS
하나의 레코드를 위해 블록 전체를 읽는 것이 RANDOM ACCESS입니다. 수직적 탐색과 리프블록에서 테이블에 접근할때 사용하는 ACCESS 방식입니다. SEQUENTIAL ACCESS는 블록에 존재하는 모든 레코드를 읽습니다. 수평적 탐색에 사용하는 ACCESS 방식입니다. 낭비되는 레코드 없이 적은 비용으로 효
유적인 탐색이 가능합니다. 또한 MULTIBLOCK I/O 를 활용해 디스크 접근횟수를 줄일 수 있습니다.
다양한 인덱스 스캔방식
INDEX RANGE SCAN
루트블록에서 리프블록 까지 수직적 탐색한 후, 리프 블록을 필요한 범위까지만 수평적으로 스캔하는 방식입니다. B*TREE 인덱스의 가장 일반적이고 정상적인 엑세스 방식입니다.
INDEX RANGE SCAN의 효율성은 수평탐색의 범위와 테이블로 엑세스하는 횟수를 얼마나 줄일 수 있느냐에 달려있습니다. INDEX RANGE SCAN이 가능하려면 인덱스를 구성하는 선두 칼럼이 조건절에 사용되야합니다.
INDEX RANGE SCAN 을 통해 생성된 결과집합은 정렬된 상태이기 때문에 SORT ORDER BY 연산을 생략하거나 MIN/MAX를 빠르게 추출할 수 있습니다.
INDEX FULL SCAN
인덱스 리프블록 전체를 탐색하는 방식으로, INDEX RANGE SCAN을 위한 최적의 인덱스가 없을때 차선책으로 이용합니다.
인덱스 레코드의 볼륨이 데이터 레코드의 볼륨보다 적기때문에 조건에 해당하는 레코드가 적다면 테이블 전체를 스캔하는 것 보다 인덱스 전체를 스캔하는 것이 효율적입니다. 즉, 최종 결과 값이 적을땐 TABLE FULL SCAN 보다 INDEX FULL SCAN이 효율적이고 최종 결과값이 많을 땐 TABLE FULL SCAN이 효율적입니다.
인덱스를 이용한 소트연산 대체
INDEX FULL SCAN 또한 결과집합이 인덱스 칼럼순으로 정렬되므로 SORT ORDER BY를 생략할 목적으로 사용할 수 있습니다.
INDEX UNIQUE SCAN
수직적 탐색만으로 인덱스를 찾는 방식으로, UNIQUE 인덱스를 EQUI 조건으로 탐색하는 경우 작동합니다.
INDEX SKIP SCAN
TABLE FULL SCAN 보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면 옵티마이저는 그 방식을 이용합니다. INDEX SKIP SCAN 은 인덱스 선구 칼럼이 조건절로 사용되지 못한 상황에서 효율적인 스캔을 가능하게 합니다. 루트 또는 브랜치 블록에서 읽은 칼럼값의 정보를 이용해 조건에 부합하는 케로드를 포함할 가능성이 있는 하위 블록만 엑세스 하는 방식입니다.
조건절에 사용되지 않은 인덱스 선두 칼럼의 DISTINCT VALUE 가 적어야합니다(성별, 지역 등). 반대로 인덱스 후행칼럼의 DISTINCT VALUE가 많을 때 유용합니다.
SELECT * FROM EMP
WHERE SALARY BETWEEN 2000 AND 4000
AND GENDER IN ('MALE', 'FEMALE')
-- EXECUTE PLAN
------------
SELECT STATEMENT OPTIMIZER = ALL_ROWS
INLIST ITERATOR
TABLE ACCESS ( BY INDEX ROWID) OF 'EMP'(TABLE)
INDEX (RANGE SCAN) OF 'EMP_IDX'(INDEX)
실행계획의 INLIST ITERATOR는 IN 절에 제공된 값의 수만큼 인덱스 탐색을 반복 수행함을 의미합니다.
INDEX FAST FULL SCAN
인덱스 트리구조를 무시하고 인덱스 세그먼트 전체를 MULTIBLOCK I/O 방식으로 스캔하기 때문에 INDEX FULL SCAN 보다 빠릅니다. TABLE RANDOM ACCESS 가 발생하는 경우 사용할 수 없으므로 쿼리에 인덱스 구성 컬럼만 있는경우 사용가능합니다.
INDEX RANGE SCAN DECENDING
INDEX RANGE SCAN과 기본적으로 동일하지만 인덱스를 뒤쪽에서 부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과 집합을 얻습니다.
인덱스의 종류
B-트리 인덱스
모든 DBMS가 기본적으로 제공하는 인덱스 구조입니다.
UNBALANCED INDEX
트리구조는 DELETE 작업으로 인해 불균형 상태에 놓일 수 있습니다. 루트블록과의 거리가 다른 리프블록과 다른 리프블록이 발생할 수 있습니다. 하지만 B-TREE 구조에서는 이런 경우가 발생하지 않습니다.
INDEX SKEW
인덱스 엔트리가 한쪽으로 치우치는 현상으로 인덱스 스캔효율에 악영향을 줍니다. DELETE 작업으로 발생한 빈 인덱스 블록은 인덱스 구조상에 그대로 남아있습니다.
상위 브랜치 블록에서 빈 리프 블록을 가리키는 엔트리가 남아있으면 인덱스 정렬 순서상 그곳에 위치할 새로운 레코드가 이를 재사용합니다.
새로운 값이 입력되기전에 다른 블록에서 인덱스 분할이 이뤄지면 역시 빈 블록을 재사용합니다. 이때 상위 브랜치 블록에서 해당 리프블록을 가리키는 엔트리가 다른쪽 브랜치의 자식이 되고 FREELIST에서도 제거됩니다. 레코드가 모두 삭제된 빈 블록은 재사용이 가능하지만 다시 채워질때까지 인덱스 스캔효율은 저하됩니다.
INDEX SPARE
인덱스 블록 전반의 밀도가 낮아지는 현상을 말합니다. 같은 조회조건이라면 인덱스 레코드를 삭제하더라도 스캔하는 인덱스 블록의 수는 항상 동일합니다.
인덱스 재생성
인덱스의 스캔 효율이 나빠지면 인덱스를 재생성하거나 DBMS가 제공하는 명령어를 이용해 빈공간을 제거할 수 있습니다. 인덱스 재생성작업은 부하가 크므로 예상효과가 확실할 때만 시행하는 것이 바람직합니다.
- 인덱스 분할에 의한 경합이 현저히 높을때
- 자주 사용하는 인덱스의 스캔효율을 높이고자 할때, 특히 NL조인에서 반복 엑세스 되는 인덱스의 높이가 증가했을 때
- 대량 DELETE 작업을 수행한 직후
- 총 레코드 수가 일정함에도 인덱스의 크기가 계속 커지는 경우
비트맵 인덱스
ORACLE은 비트맵 구조의 인데스를 제공합니다. 부정형 조건에도 사용할 수 있습니다. 비트맵 인덱스는 NULL 도 저장하기 때문에 NULL 비교에도 사용할 수 있습니다.
DISTINCT VALUE의 수가 적은 칼럼으로 인덱스를 구성할때 좋고 B*TREE 인덱스에 비해 용량이 작기때문에 인덱스가 여러개 필요한 대용량 테이블 분석에 유용합니다. 비트맵 인덱스는 여러 인덱스를 동시에 활용할 수 있다는 장점으로 인해 다양한 조건절이 사용되는 정형화되지 않은 임의 질의 환경에 적합합니다.
단, 비트맵 인덱스는 LOCK에 의한 DML 부하가 심한 단점이 있습니다. 레코드 하나만 변경되더라도 해당 비트맵 버뮈에 속한 모든 레코드에 LOCK이 발생합니다.
함수기반 인덱스
ORACLE이 제공하는 함수기반 인덱스는 칼럼에 특정함수를 적용한 값으로 B*TREE 인덱스를 생성합니다.
SELECT *
FROM 주문
WHERE NVL(주문수량, 0) < 100
위처럼 칼럼을 가공한 조건절에 사용할 수 있습니다. 유용한 사례는 대소문자를 구분해 입력 받은 데이터를 대소문자 구분없이 조회할 때입니다. UPPER(column name) 함수를 씌워 인덱스를 생성하고 UPPER 조건으로 검색합니다.
함수기반 인덱스는 입력,수정 시 함수를 적용해야하므로 다소 부하가 발생할 수 있습니다. 함수가 사용자 정의 함수일 경우는 더 심합니다.
리버스 키 인덱스
순차적으로 입력되는 값을 거꾸로 변환해 저장하면 데이터가 고르게 분포합니다. 따라서 리프블록 맨 우측에만 집중되는 트랜잭션을 리프블록 전체에 고르게 분산시키는 효과를 얻을 수 있습니다. 단, 리버스 키 인덱스는 데이터를 거꾸로 입력하기 때문에 = 조건으로만 검색할수 있고 범위 조건에는 사용할 수 없습니다.
클러스터 인덱스
인덱스 클러스터 테이블은 클러스터키 값이 같은 레코드가 한 블록에 모이도록 저장합니다. 한 블록에 클러스터키가 같은 레코드를 모두 담을 수 없을 때는 새로운 블록을 할당해 체인으로 연결합니다. 여러 테이블 레코드가 물리적으로 같은 블록에 저장되도록 클러스터를 할당할 수도 있습니다. 여러 테이블을 서로 조인된 상태로 저장합니다.
ORACLE에서 인덱스 클러스터를 만들고 클러스터 인덱스를 정의하는 방법은 다음과 같습니다.
create cluster c_deptno# (deptno number(2)) index;
create index i_deptno# on cluster c_deptno#;
-- 생성한 클러스터에 다음과 같이 테이블을 담는다.
create table emp
cluster c_detpno# (deptno)
select * from emp;
클러스터 인덱스는 일반적으로 B*TREE 구조를 사용하지만, 리프블록의 레코드가 해당 키 값을 저장하는 첫번째 데이터 블록만을 참조합니다. 클러스터 인덱스의 키 값은 항상 UNIQUE 하며 테이블 레코드와 1:M 관계를 맺습니다. 일반 인덱스 레코드는 테이블 레코드와 1:1 관계를 맺습니다.
이런 구조저 특성으로 클러스터 인덱스를 스캔하면 값 하나당 랜덤엑세스가 한 번씩만 발생합니다. 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 검색할 때 유리합니다. 새로운 값이 자주 입력되거나 수정이 자주 발생하는 컬럼은 클러스터 키로 적합하지 않습니다.
인덱스 튜닝 기초
인덱스를 정상적을 사용하기 위해서는 인덱스 선두 칼럼이 조건절에 사용되야 합니다. 그렇지 않으면 범위 스캔을 위한 시작점을 찾을 수 없어 인덱스 전체나 테이블 전체를 스캔해야합니다. 인덱스 선두 칼럼이 조건절에 사용되더라도 범위 스캔이 불가능하거나 인덱스를 아예 사용하지 못하는 경우도 있습니다.
RANGE SCAN이 불가능하거나 인덱스 사용이 불가능한 경우
- 인덱스 선두 칼럼을 조건절에서 가공하면 RANGE SCAN이 불가합니다.
- 인덱스 선두 칼럼을 부정형으로 비교하면 RANGE SCAN이 불가합니다.
- 인덱스 선두 칼럼을 NULL 비교하면 RANGE SCAN이 불가합니다.
위 경우 모두 정상적인 RANGE SCAN은 불가하지만 인덱스의 사용은 가능합니다. INDEX FULL SCAN으로 스캔합니다.
IS NOT NULL 비교의 경우, 단일 칼럼 인덱스는 NULL 값을 인덱스 레코드로 저장하지 않기 때문에 모든 레코드가 is not null 조건을 만족합니다. 결합 인덱스일 경우 구성칼럼중 하나라도 값이 NULL이 아니면 인덱스 레코드에 저장합니다. 이 경우 필터링을 통해 is not null 조건에 해당하는 레코드를 찾을 수 있습니다.
IS NULL 비교에 경우, 단일 칼럼 인덱스는 NULL 값을 인덱스 레코드로 저장하지 않기 때문에 인덱스를 사용할 수 없습니다. 단, NOT NULL 제약이 걸린 칼럼이라면 가능합니다. 결합 인덱스는 인덱스 구성 칼럼이 모두 NULL인 레코드는 인덱스를 만들지 않기 때문에 인덱스를 사용할 수 없습니다. 다른 인덱스 칼럼에 NULL 이 아닌 값이 하나라도 있거나 NOT NULL 제약이 있으면 범위탐색이 가능합니다. 물론 인덱스 선두 칼럼이 조건절에 반드시 존재해야합니다.
인데스 구성칼럼의 가공
인덱스 칼럼을 가공하면 정상적인 INDEX RANGE SCAN이 불가능합니다.
묵시적 형변환
사용자가 명시적으로 인덱스 구성칼럼을 가공하지 않더라도 조건절에서 비교되는 두 값의 데이터 타입이 다르면 내부적으로 형변환이 발생합니다. 문자형과 숫자형이 만나면 옵티마이저가 문자형을 숫자형으로 변환합니다. 이에 숫자형-문자형 비교는 정상적인 인덱스 사용이 가능합니다. 반대의 경우는 칼럼의 묵시적 형변환이 발생합니다.