-
대용량 테이블에서 필요한 데이터를 빠르게 찾기 위해 인덱스의 도움이 필요하다.
그림 III-3-1 은 인덱스 컬럼이 양의 정수만 저장할 수 있는 데이터 타입이라고 가정했을 때 예시이다.
처음에는 단 하나의 루트 노트에서 데이터가 쌓이면서 루트, 브랜치, 리프 노드를 모두 갖춘 나무 형태로 성장한다.
루트에서 리프블록까지의 거리를 인덱스 깊이(Height)라고 부르며, 인덱스를 반복적으로 탐색할 때 성능에 영향을 미친다.
http://wiki.gurubee.net/pages/viewpage.action?pageId=12517389 루트와 브랜치 블록은 각 하위 노드들의 데이터 값 범위를 나타내는 키 값과 그 키값에 해당하는 블록을 찾는데 필요한 주소정보를 가진다.
리프 블록은 인덱스 키 값과 그 키 값에 해당하는 테이블 레코드를 찾아가는데 필요한 주소 정보(ROWID)를 가진다.
키 값이 같을 때는 ROWID 순으로 정렬된다.
리프 블록은 항상 인덱스 키 값으로 정렬돼 있기 때문에 범위 스캔 (Range Scan, 검색조건에 해당하는 부분만 읽고 멈춤) 이 가능하고, 정방향(Ascending) 과 역방향(Descending) 스캔이 둘 다 가능하도록 양방향 연결(Double linked list)구조로 연결돼 있다.
아래는 null 값을 인덱스에 저장하는 데 있어 오라클과 SQL Server 의 차이점이다.
- 오라클에서 인덱스 구성 컬럼이 모두 null 인 레코드는 인덱스에 저장하지 않는다.
반대로 말하면 인덱스 구성 컬럼 중 하나라도 null 값이 아닌 레코드는 인덱스에 저장한다.
- SQL Server 는 인덱스 구성 컬럼이 모두 null 인 레코드도 인덱스에 저장한다.
- null 값을 Oracle 은 맨 뒤에 저장하고 SQL Server 는 맨 앞에 저장한다.
null 값을 처리하는 방식이 DBMS 마다 다 다르고, 이런 특성이 null 값 조회에 인덱스가 사용될 수 있는지를 결정하므로
인덱스를 설계하거나 SQL 을 개발할 때 반드시 숙지해야한다.
인덱스 탐색
1. 수평적 탐색
인덱스 리프 블록에 저장된 레코드끼리 연결된 순서에 따라 좌에서 우 또는 우에서 좌로 스캔하기 때문에 '수평적' 이라고 한다.
2. 수직적 탐색
수평적 탐색을 위한 시작 지점을 찾는 과정이라고 할 수 있으며, 루트에서 리프 블록까지 아래쪽으로 진행하기 때문에 '수직적'이다.
위에 그림 III-3-1 에서 키 값이 53인 레코드를 찾기
1. 루트 블록에서 53이 속한 키 값을 찾는다. 두번때 블록이 선택되고 거기에 있는 3번 블록으로 간다.
2. 3번 블록에서 53이 속한 키 값을 찾는다. 9번 블록으로 간다.
3. 9번 블록은 리프 블록으로 값을 최종적으로 찾는 곳이다. 3번째 레코드에서 찾아지므로 ROWID 를 이용해
데이터 블록을 찾아간다. ROWID 를 분해하면 오브젝트 번호, 데이터 파일번호, 블록번호, 블록 내 위치 정보를 알 수 있다.
4. 테이블 블록에서 레코드를 찾아간다.
3번을 다시 확인 해 만약 3번 과정에 9번 블록에서 키 값이 53인 레코드가 두 개 이상이라면 53번이 더 이상 나오지 않을 때까지 4번을 테이블 엑세스 과정을 반복한다.
다양한 인덱스 스캔 방식
1. Index Range Scan
인덱스의 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔한다.
B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 인덱스 방식이고, Oracle 에서의 실행 계획은 다음과 같다.
create index emp_deptno_idx on emp(deptno); set autotrace traceonly explain select * from emp where deptno=20; Excution Plan ================================================== 0 SELECT STATEMENT Optimizer=ALL ROWS 1 0 TABLE ACCESS(BY INDEX ROWID) OF 'EMP' (TABLE) 2 1 INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX)실행계획 상에 INDEX RANGE SCAN 이 나타난다고 해서 항상 빠른 속도를 보장하는 것은 아니다.
인덱스를 스캔하는 범위(Range)를 얼마만큼 줄일 수 있느냐, 테이블로 엑세스하는 횟수를 얼마만큼 줄일 수 있느냐가 관건이다. 이는 인덱스 설계와 SQL 튜닝의 핵심 원리 중 하나이다.
INDEX RANGE SCAN이 가능하게 하려면 인덱스를 구성하는 선두 컬럼(deptno)이 조건절에 사용돼야 한다. 그렇지 못한 상황에서 인덱스를 사용하도록 힌트를 강제한다면 Index Full Scan 방식으로 처리된다.
Index Range Scan 과정을 거쳐 생성된 결과 집합은 인덱스 컬럼 순으로 정렬된 상태가 되기 때문에 이런 특징을 잘 이용하면 sort order by 연산을 생략하거나 min/max 값을 빠르게 추출할 수 있다.
2. Index Full Scan
수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식으로서,
대개는 데이터 검색을 위한 최적의 인덱스가 없을 떄 차선으로 선택된다.
create index emp_idx on emp(ename,sal); set autotrace traceonly explain select * from emp where sal > 2000 order by ename; Excution Plan ================================================== 0 SELECT STATEMENT Optimizer=ALL ROWS 1 0 TABLE ACCESS(BY INDEX ROWID) OF 'EMP' (TABLE) 2 1 INDEX (FULL SCAN) OF 'EMP_IDX' (INDEX)위 SQL 처럼 인덱스 선두 컬럼(ename) 이 조건절에 없으면 옵티마이저는 우선적으로 Table Full Scan 을 고려한다.
그런데 대용량 테이블이어서 Table Full Scan 부담이 크다면 옵티마이저는 인덱스를 활용하는 방법을 다시 생각해본다.
데이터 저장공간은 '가로 x 세로', 즉 '컬럼길이 x 레코드수' 에 의해 결정되므로 대게 인덱스가 차지하는 면적은 테이블보다 훨씬 적게 마련이다. 만약 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 일부에 대해서만 테이블 엑세스가 발생하는 경우라면 테이블 전체를 스캔하는 것보다 낫다. 이럴 때 옵티마이저는 Index Full Scan 방식을 선택할 수 있다.
인덱스를 이용한 소트 연산 대체
Index Full Scan 은 Index Range Scan 과 마찬가지로 그 결과 집합이 인덱스 컬럼 순으로 정렬되므로 Sort Order By 연산을 생략할 목적으로 사용될 수도 있다. 이는 차선책으로 선택됐다기보다 옵티마이저가 전략적으로 선택한 경우에 해당한다.
select /*+ first_row*/ * from emp where sal > 1000 order by ename; Excution Plan ================================================== 0 SELECT STATEMENT Optimizer=ALL ROWS 1 0 TABLE ACCESS(BY INDEX ROWID) OF 'EMP' (TABLE) 2 1 INDEX (FULL SCAN) OF 'EMP_IDX' (INDEX)대부분의 사원의 연봉이 1000을 초과하므로 Index Full Scan 을 하면 거의 모든 레코드에 대해 테이블 액세스가 발생해 Table Full Scan 보다 불리하다! 만약 SAL 이 인덱스 선두 컬럼이여서 Index Range Scan 을 하더라도 마찬가지이다.
그럼에도 여기서 인덱스가 사용된 것은 사용자가 first_row 힌트를 이용해 옵티마이저 모드를 바꿨기 때문이다.
즉 옵티마이저는 소트 연산을 생략함으로써 전체 집합 중 처음 일부만 빠르게 리턴할 목적으로 Index Full Scan 방식을 선택한다.
그러나 처음 의도와 다르게 사용자가 데이터 읽기를 멈추지 않고 끝까지 fetch 한다면 Table Full Scan 보다 많은 I/O 가 일어나 자원이 낭비된다.
3. Index Unique Scan
수직적 탐색만으로 데이터를 찾는 스캔 방식으로, Unique 인덱스를 "=" 조건으로 탐색하는 경우에 작동한다.
create unique index pk_emp on emp(empno); alter table emp add constraint pk_emp primary key(empno) using index pk_emp; set autotrace traceonly explain select empno,ename from emp where empno = 7788; Excution Plan ================================================== 0 SELECT STATEMENT Optimizer=ALL ROWS 1 0 TABLE ACCESS(BY INDEX ROWID) OF 'EMP' (TABLE) 2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)4. Index Skip Scan
인덱스 선두 컬럼이 조건절로 사용되지 않으면 TABLE FULL SCAN 이 기본적으로 선택된다고 했다.
또 TABLE FULL SCAN 보다 I/O 를 줄일 수 있으면 INDEX FULL SCAN 방식이 사용된다고 했다.
ORACLE 은 인덱스 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용하는 새로운 스캔방식을 9i 버전에서 선보였다.
아래는 성별과 연봉 두 컬럼으로 구성된 결합 인덱스에서 선두 컬럼인 성별 조건이 빠진 SQL 문이 Index Skip Scan 방식으로 수행될 때 실행계획이다.
select * from emp where sal between 2000 and 4000; Excution Plan ================================================== 0 SELECT STATEMENT Optimizer=ALL ROWS 1 0 TABLE ACCESS(BY INDEX ROWID) OF 'EMP' (TABLE) 2 1 INDEX (SKIP SCAN) OF 'EMP_IDX' (INDEX)루트 또는 브랜치 블록에서 읽은 칼럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 '가능성이 있는' 하위 블록(브랜치 또는 리프 블록)만 골라서 액세스하는 방식이라고 할 수 있다.
이 스캔 방식은 조건절에 빠진 인덱스 선두 컬럼(sex)의 Distinct Value 개수가 적고 후행 칼럼(sal)의 Distinct Value 가 많을 때 유용하다.
In-List 튜닝 기법
-- Index Skip Scan 에 의존하는 대신 성별값을 In-List 로 제공해주면? select * from emp where sal between 2000 and 4000 and sex in ('남','여'); Excution Plan ================================================== 0 SELECT STATEMENT Optimizer=ALL ROWS 1 0 INLIST ITERATOR 2 1 TABLE ACCESS(BY INDEX ROWID) OF 'EMP' (TABLE) 3 2 INDEX (SKIP SCAN) OF 'EMP_IDX' (INDEX)1번 단계 INLIST ITERATOR 라고 표시된 부분은 조건절 IN-LIST 에 제공된 값의 종류만큼 인덱스 탐색을 반복 수행한다.
쿼리작성자가 직접 성별에 대한 조건식을 추가해 주면 INDEX SKIP SCAN 에 의존하지 않고도 빠른 결과 집합을 얻을 수 있다. 단 이처럼 IN-LIST 를 명시하려면 성별값 종류가 더 늘지 않음이 보장돼야 한다.
또 이 튜닝이 효과를 발휘하려면 IN-LIST 제공 값의 종류가 적어야 한다.
5. Index Fast Full Scan
인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔해 Index Full Scan 보다 빠르다.
Index Full Scan Index Fast Full Scan - 인덱스 구조를 따라 스캔
- 결과 집합 순서 보장
- Single Block I/O
- 병렬 스캔 불가 (파티션 안돼있다면)
- 인덱스에 포함되지 않은 컬럼 조회 시에도 사용- 세그먼트 전체 스캔
- 결과 집합 순서 보장 안됨
- Multiblock I/O
- 병렬 스캔 가능
- 인덱스에 포함된 컬럼으로만 조회 시에만 가능6. Index Range Scan Descending
Index Range Scan 과 동일한 스캔방식이다. 인덱스를 뒤에서 앞으로 스캔해 내림차순으로 정렬된 결과 집합을 얻는 점만 다르다.
아래처럼 emp 테이블을 empno 기준으로 내림차순 정렬하고자 할 때 empno 컬럼에 인덱스가 있으면
옵티마이저가 알아서 인덱스를 거꾸로 읽는 실행계획을 수립한다.
select * from emp where empno is not null order by empno desc; Excution Plan ================================================== 0 SELECT STATEMENT Optimizer=ALL ROWS 1 0 TABLE ACCESS(BY INDEX ROWID) OF 'EMP' (TABLE) 2 1 INDEX (RANGE SCAN DESCENDING) OF 'PK_EMP' (INDEX(UNIQUE))max 값을 구하고자 할 때고 해당 컬럼에 인덱스가 있으면 인덱스를 뒤에서 한 건만 읽고 멈추는 실행계획이 자동 수립된다.
create index emp_x02 on emp(deptno,sal); select deptno, dname, loc ,(select max(sal) from emp where deptno=d.deptno) from dept d; Excution Plan ================================================== 0 SELECT STATEMENT Optimizer=ALL ROWS 1 0 SORT (AGGREGATE) 2 1 FIRST ROW 3 2 INDEX (RANGE SCAN (MIN/MAX)) OF 'EMP_X02' (INDEX) 4 0 TABLE ACCESS(FULL) OF 'DEPT' (TABLE)
인덱스 종류
1. B*Tree 인덱스
B는 Balanced 의 약자로 인덱스 루트에서 리프 블록까지 어떤 값으로 탐색하더라도 읽는 블록 수가 같음을 의미한다.
Unbalanced Index, Index Skew, Index Sparse 가 발생할 수 있다.
Fragmentation 때문에 인덱스 크기가 계속 증가하고 스캔 효율이 나빠지면 인덱스를 재생성하거나 DBMS 가 제공하는 명령어를 이용해 빈 공간 제거가 유용할 수 있다. 하지만 일반적으로 인덱스 블록에는 어느 정도 공간을 남겨두는 것이 좋다. 왜냐하면 빈 공간을 제거해 인덱스 구조를 슬림(slim)화하면 저장 효율이나 스캔 효율에는 좋겠지만, 인덱스 분할이 자주 발생해 DML 성능이 나빠질 수 있기 때문이다.
인덱스 분할에 의한 경합을 줄일 목적으로, 초기부터 빈 공간을 남기도록 옵션을 주고 인덱스를 재생성할 수도 있다.
하지만 그 효과는 일시적이다. 언젠가 빈 공간이 다시 채워지기 때문이다.
결국 적당한 시점마자 재생성 작업을 반복하지 않는 한 근본적인 해결책이 되지는 못한다.
인덱스를 재생성하는 데 걸리는 시간과 부하도 무시할 수 없다. 따라서 인덱스의 주기적인 재생성 작업은 다음과 같이 예상효과가 확실할 때만 시행하는 것이 바람직하다.
- 인덱스 분할에 의한 경합이 현저히 높을 대
- 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때. 특히 ML 조인에서 반복 액세스 되는 인덱스 높이가 증가했을 때.
- 대량의 delete 작업을 수행 후 다시 레코드가 입력될 때까지 오랜 기간이 소요될 떄
- 총 레코드 수가 일정한데도 인덱스가 계속 커질 때
2. 비트맵인덱스
오라클은 비트맵 인덱스 구조를 제공한다.
비트맵 인덱스는 부정형 조건에도 사용할 수 있고, Null 도 저장한다.
컬럼의 Distinct Value의 개수가 적을 때 비트맵 인덱스를 사용하면 저장효율이 매우 좋다. B*Tree 보다 훨씬 적은 용량을 차지해 인덱스가 여러개 필요한 대용량 테이블에 유용하다. 다양한 분석관점을 가진 팩트성 테이블이 주로 여기 속한다.
** 반대로 Distinct Value 가 아주 많은 컬럼이면 오히려 B*Tree 인덱스보다 많은 공간을 차지한다.
테이블 랜덤 엑세스 발생 측면에서는 B*Tree 인덱스와 똑같기 때문에 그런 컬럼을 비트맵 인덱스로 검색하면 그다지 좋은 성능을 기대하기 어렵다. 스캔할 인덱스 블록이 줄어드는 정도의 이점만 얻을 수 있다.
따라서 하나의 비트맵 인덱스 단독으로는 쓰임새가 별로 없다. 그 대신 여러 비트맵 인덱스를 동시에 사용할 수 있는 특징때문에 대용햘 데이터 검색 성능을 향상시키는데 효과가 있다.
비트맵 인덱스는 여러 인덱스를 동시에 활용할 수 있어서 다양한 조건절이 사용되는, 특희 정형화되지 않은 임의 질의가 많은 환경에 적합하다.
** 다만 Lock 에 의한 DML 부하가 심한 것이 단점이다. 레코드 하나만 변경되도 해당 비트맵 범위의 모든 레코드에 Lock 이 걸린다. OLTP 환경에 비트맵 인덱스를 쓸 수 없는 이유가 여기에 있다.
3. 함수기반인덱스
오라클이 제공하는 FBI 는 컬럼 값 자체가 아닌, 컬럼에 특정함수를 적용한 값으로 B*Tree 인덱스를 만든다.
인덱스를 컬럼 값을 대상으로 만들었을 때, select 할 때 null 값을 0으로 치환하는 nvl 함수를 사용하면 인덱스가 사용되지 않는다.
하지만 아래처럼 함수를 이용해 인덱스를 만들면 정상적인 인덱스 사용이 가능하다.
nvl 이외에도 upper 함수를 씌워 대소문자 구분없이 조회할 때 함수기반 인덱스를 유용하게 사용할 수 있다.
create index emp_01 on emp( nvl(주문수량,0) ); select * from 주문 where nvl(주문수량,0) < 100;** 함수기반 인덱스는 데이터 입력, 수정 시 함수를 적용해야 하므로 다소 부하가 발생할 수 있다.
** 사용자 정의 함수일때는 부하가 더 심하다. 따라서 꼭 필요한 경우에만 사용하도록 해야 한다.
4. 리버스 키 인덱스
일련번호나 주문일시 같은 컬럼에 인덱스를 만들면, 입력되는 값이 순차적으로 증가하기 때문에 아래 그림처럼 가장 오른쪽 리프 블록에만 데이터가 쌓인다. 이런 현상이 발생하는 인덱스를 Right Growing 또는 Right Hand 라도 부른다.
동시에 Insert 가 심할 때 인덱스 블록 경합을 일으켜 초당 트랜젝션 처리량을 크게 감소시킨다.
그럴 때 리버스 키 인덱스가 유용할 수 있다. 입력된 키 값을 거꾸로 변환해서 저장하는 인덱스이다.
create index 주문일시_01 on 주문( reverse(주문일시) );이렇게 하면 리프 블록 맨 우측에만 집중되는 트랜젝션을 리프 블록 전체에 고르게 분산시킬 수 있다.
** 하지만 리버스 키 인덱스는 데이터를 거꾸로 입력하기 때문에 "=" 조건으로만 검색할 수 있고, 부등호나 between,like 는 사용할 수 없다.
5. 클러스터 인덱스
오라클에는 클러스터 테이블이라는 오브젝트가 있다. 클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 두 가지가 있는데, 이번에 나오는 클러스터 인덱스는 인덱스 클러스터와 연관이 있다.
인덱스 클러스터 테이블은 클러스터 키(deptno) 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조를 사용한다.
한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결한다.
심지어 여러 테이블 레코드가 물리적으로 같은 블록에 저장되도록 클러스터를 할당할 수도 있다(다중 테이블 인덱스클러스터). 여러테이블을 서로 조인된 상태로 저장하는 것인데, 일반적으로 하나의 데이터 블록이 여러 테이블에 의해 공유될 수 없다 (SQL 서버는 가능,혼합 익스텐트).
create cluster c_deptno# ( deptno number(2) ) index; create index i_deptno# on cluster c_deptno#; create table emp cluster c_deptno# (deptno) as select * from scott.emp;클러스터 인덱스도 일반적인 B*Tree 인덱스 구조를 사용하지만, 해당 키 값을 저장하는 첫번째 데이터 블록만 가리킨다는 점에서 다르다.
클러스터 인덱스의 키값은 항상 Unique 며, 테이블 레코드와 1:M 관계를 갖는다.
일반 테이블에서 생성한 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖는다.
이런 구조적 특성으로 클러스터 인덱스를 스캔하며 값을 찾을 때는 랜덤 엑세스가 값 하나당 한 번씩만 발생한다(클러스터 체인 스캔 제외). 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 검색할 때 유리하다.
** 새로운 값이 자주 입력(새 클러스터 할당)되거나 수정이 자주 발생(클러스터 이동)하는 컬럼은 클러스터 키로 선정하지 않는 것이 좋다.
6. 클러스터형 인덱스와 IOT
SQL 서버에서 지원되는 인덱스로는 클러스터형 인덱스와 비클러스터형 인덱스 두가지가 있다.
비클러스터형 인덱스는 B*Tree 인덱스와 동일하다.
* IOT (Index-Organized Table)
※ 클러스터형 인덱스와 IOT 구조
클러스터형 인덱스도 구조적으로는 B*Tree 와 같은 형대이다. 차이가 있다면 별도의 테이블을 생성하지 않고 모든 행 데이터를 인덱스 리프 페이지에 저장한다는 점이다. 인덱스 리프 페이지가 곧 데이터 페이지인 셈이다.
비클러스터형 인덱스(왼) 와 클러스터형 인덱스(오) 일반적인 힙 구조 테이블에 데이터를 삽입할 땐 순서 없이 랜덤 방식으로 이뤄진다. 반면 클러스터형 인덱스는 정렬상태를 유지하며 데이터를 삽입한다. 따라서 클러스터형 인덱스는 테이블마다 단 하나만 생성할 수 있다. 한 테이블이 두 개의 정렬 순서를 가질수 없으므로 당연한 제약이다.
테이블에 클러스터형 인덱스를 생성하면 항상 정렬된 상태를 유지해야 하기 때문에 데이터 입력 시 성능이 느린 단점을 갖는다. 비클러스터형 인덱스를 생성해도 정렬을 유지해야 한다는 점은 같지만, 클러스터형 인덱스는 인덱스 키 값 외에도 많은 데이터를 리프 페이지에 저장하기 때문에 그만큼 인덱스 분할이 자주 발생한다. 이 때문에 DML 부하가 더 심하다. 이런 단점에도 불구하고 클러스터형 인덱스를 사용하는 이유는 넓은 범위의 데이터를 검색할 때 유리하기 대문이다. 이런 특징은, 같은 값을 가진 레코드가 100% 정렬된 상태로 모여있고 리프 레벨이 곧 데이터 페이지라는 데서 나온다. 즉 정렬된 리프 페이지를 시퀀셜 방식으로 스캔하면서 검색 값을 모두 찾을 수 있고, 찾은 레코드에 대해서는 추가 테이블 랜덤 엑세스가 필요하지 않다.
클러스터형 인덱스와 오라클의 클러스터 인덱스는 다르다. 이름이 비슷해 헷갈리기 쉽지만 클러스터형 인덱스는 사실
오라클의 IOT 에 가깝다. 차이가 있다면 오라클 IOT 는 PK 에만 생성할 수 있다는 점이다.
SQL 서버의 클러스터형 인덱스는 중복 값이 있는 컬럼에도 생성할 수 있기 때문에 중복된 키 값을 내부적으로 식별하기 위해 'uniquifier' 라는 값(4바이트 크기)을 함께 저장한다.
※ 클러스터형 인덱스와 IOT 활용
클러스터형은 다음와 같은 상황에 유용하다.
- 넒은 범위를 주로 검색하는 테이블
- 크기가 작고 NL 조인으로 반복 룩업하는 테이블
- 컬럼 수가 적고 로우 수가 많은 테이블
- 데이터 입력과 조회 패턴이 서로 다른 테이블
- 데이터 입력과 조회 패턴이 서로 다른 테이블의 의미
어떤 회사에 100명의 영업사원이 있고, 영업사원들의 일별 실적을 집계하는 테이블이 있다.
테이블의 한 페이지에는 100개의 레코드가 담긴다. 그러면 매일 한 페이지씩 1년이면 365개 페이지가 생긴다.
실적 등록은 이처럼 일자별로 진행되지만 실적 조회는 주로 사원별로 이뤄전디.
select substring(일자,1,6) 월도 ,sum(판매금액) as 총판매액 ,avg(판매금액) as 평균판매액 from 영업실적 where 사번 = '1234' and 일자 between '20090101' and '20091231' group by substring(일자,1,6);만약 비클러스터형 인덱스를 이용한다면 사원마다 365개의 데이터 페이지를 랜덤 엑세스 방식으로 읽어야 한다.
특정 사원의 1년차 영업실적이 365개의 페이지에 흩어져 저장되기 때문이다. 이처럼 데이터 입력과 조회 패턴이 서로 다를 때, 다음과 같이 사번이 첫 번째 정렬 기준이 되도록 클러스터형 인덱스를 생성해 주면 한 페이지만 읽어 처리할 수 있다.
select clustered index 영업실적_idx on 영업실적(사번,일자);지금까지 설명한 클러스터형 인덱스의 특징은 오라클 IOT 에도 똑같이 적용된다.
방금 설명한 사례로 oracle 에서 IOT 를 생성하려면 다음과 같이 한다.
create table 영업실적 ( 사번 varchar2(5), 일자 varchar2(8).. ) constraint 영업실적_pk primary key(사번, 일자) organization index;※ 2차 인덱스로부터 클러스터형 인덱스와 IOT 참조방식
SQL서버는 클러스터형 인덱스를 가리키는 2차 인덱스를 비클러스터형 인덱스라고 부른다. 오라클에선 IOT 를 가리키는 2차 인덱스를 'Secondary Index' 라고 부른다. 2차 인덱스는 클러스터형 인덱스나 IOT 를 가리키는 키값을 내부적으로 포함하는데, 버전마다 구조가 조금씩 다르다. SQL 서버 6.5 이전에는 비클러스터형 인덱스가 클러스터형 인덱스 레코드를 직접 가리키는 rowid를 갖도록 설계했다. 문제는, 인덱스 분할에 인해 클러스터형 인덱스 레코드 위치가 변경될 때마다 비클러스터형 인덱스(한 개 이상일 수 있음)가 갖는 rowid 정보를 모두 갱신해주어야 한다는 데 있다.
실제로 DML 부하가 심하다고 느낀 마이크로소프트는 7.0 버전부터 비클러스터형 인덱스가 rowid 대신 클러스터형 인덱스의 키 값을 갖도록 구조를 변경했다. 이제 클러스터형 인덱스의 키 값을 갱신하지 않는 한, 인덱스 분할 때문에 비클러스터형 인덱스를 갱신할 필요가 없어졌다.
그런데 DML 부하가 줄어든 대신, 비클러스터형 인덱스를 이용하기 전보다 더 많은 I/O 가 발생하는 부작용을 안게 됐다. 비클러스터형 인덱스에서 읽히는 레코드마다 건건이 클러스터형 인덱스 수직 탐색을 반복하기 때문이다.
당연히 클러스터형 인덱스 높이가 증가할수록 블록 I/O도 증가한다.
오라클은 IOT 를 개발하면서 SQL 서버 6.5 이전과 7.0 이후 버전이 갖는 두 가지 엑세스 방식을 모두 사용할 수 있도록 설계했다. IOT 레코드의 위치는 영구적이지 않으므로 오라클은 Secondary Index 로부터 IOT 레코드를 가리킬 때 물리적 주소 대신 Logical RowId 를 사용한다. Logical RowId 는 PK와 Physical guess 로 구성된다.
Physical guess는 Secondary 인덱스를 최조 생성하거나 재생성한 시점에 IOT 레코드가 위치했던 데이터 블록 주소(DBA)이다. 인덱스 분할에 의해 IOT 레코드가 다른 블록으로 이동하더라도 Secondary 인덱스에 저장된 Physical guess는 갱신되지 않는다. SQL 서버 6.5에 발생한 DML 부하를 없애기 위함이고, 레코드 이동이 발생하면 정확한 값이 아닐 수 있으므로 guess 라는 표현을 사용했다.
이처럼 두 가지 정보를 다 가짐으로써 오라클은 상황에 따라 다른 방식으로 IOT 를 엑세스할 수 있게 했다.
경우에 따라 두 가지 방식을 다 사용하기도 한다. Physical guess 가 가리키는 블록을 찾아갔다가 찾는 레코드가 없으면 PK 로 다시 탐색하는 식이다.
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형