제가 발표할 부분은 인덱스에 관한 것인데 그렇다면 인덱스는 무엇인지부터 알아보겠습니다. 인덱스란 키 값을 기반으로 하는 테이블에서 검색과 정렬 속도를 올리는 기능을 담당하는 세그먼트입니다 컬럼의 키 값과 ROWID로 구성되어 있는데 먼저 ROWID가 어떻게 구성되어 있는지부터 살펴보겠습니다
ROWID는 오브젝트 번호 + 파일 번호 + 블록 번호 + row 번호를 포함한 총 18자리로 6 3 6 3 이런 포멧으로 구성되어 있습니다 키 컬럼은 우리에게도 익숙한 employees 테이블의 king이라던지 salary 24000이런 컬럼 값을 의미하는 것으로 만약 인덱스를 생성할 때 컬럼을 세개 주고 생성할 경우 키 컬럼에 저장될 값은 세개가 되게 되고 이것이 인덱스의 테이블에 저장이 되게 됩니다 이는 필연적으로 또 다른 저장공간을 필요로 하게 됨으로써 인덱스를 생성할때는 되도록이면 컬럼의 개수를 줄이는게 좋은 인덱스의 사용법중 하나입니다
가장 널리 이용되는 B*tree 인덱스의 구조입니다 root branch leaf 블록으로 구성되어 있으며 각각자신의 블록 번호와 하위 data의 범위값을 가지고 가진 키 값을 범위삼아 필요한 값을 탐색합니다 branch까지의 깊이를 나타내는 값을 BLEVEL value라 하고 index전체의 깊이를 나타내는 값을 height value라고 합니다
인덱스를 탐색하는 데는 여러가지 방법이 있는데 먼저 IFS은 인덱스가 가진 모든 컬럼을 탐색하는 방법으로 인덱스 탐색 방식 중 가장 넓은 범위를 검색하는 방식입니다 IRS은 일정 범위를 액세스하는 방식이며 인덱스 유니크 스캔은 equal(=) 조건으로 탐색하는 경우 발생하며 수직점 탐색으로 데이터를 찾는 스캔 방식입니다 =조건이므로 데이터 한건을 찾는 순간 더 이상 탐색하지 않습니다.
2개의 컬럼을 기준으로 하는 복합 인덱스 컬럼 값이 아닌 함수의 계산된 결과를 저장하는 함수 기반 인덱스인데 이것은 함수를 사용하는 컬럼의 결과 값을 미리 인덱스로 만들어 놓음으로써 원하는 함수에 대한 결과값을 바로 인덱스로 얻을 수 있다 그리고 내림 차순 인덱스를 생성할 것인 것 이는 오름차순 정렬과 내림 차순 정렬을 복합적으로 수행햐아 하는 경우 사용합니다
55500개의 row를 가진 customers로 인덱스를 사용하는 것과 사용하지 않는 것의 차이를 보겠습니다. 인덱스 없이 TFS해서 얻은 실행계획입니다 많은 cost를 사용한 것을 알 수 있습니다 여기서 cust_last_name를 기준으로 하는 두개의 키 컬럼값을 가지는 인덱스를 생성한 후 다시 조회해보겠습니다 예상대로 인덱스를 타고 range scan 즉 범위 스캔을 했고 만든 인덱스가 wade행의 모든 컬럼을 가지고 있지 않기에 Table access by index rowid를 통해 값을 조회하고 적은 cost를 사용한 것을 알 수 있습니다 만약 wade라는 행이 last_name과 first_name만을 가졌다고 한다면 이전에 만든 인덱스의 구조만을 탐색하는 것만으로도 즉 테이블을 탐색하지 않더라도 유저가 원하는 결과값을 반환할 수 있는데 이는 한번의 I/O만으로 결과를 출력한다는 뜻입니다
다음은 함수를 포함하는 쿼리를 날렸기 때문에 인덱스를 타지 못하고 풀 스캔을 하는 것을 볼 수 있습니다 그래서 함수기반 인덱스는 키 컬럼의 값을 저장하는 것이 아닌 함수에 의한 컬럼의 결과값을 저장함으로 인해서 이미 만들어진 결과를 검색하는 것을 말합니다 그리하여 인덱스 생성 이후 결과를 조회하면 인덱스를 타는 것을 확인할 수 있습니다
이번에는 내림차순 인덱스를 생성해서 인덱스가 가진 컬럼만 조회를 함으로써 테이블 스캔을 하지 않고 인덱스의 구조만 탐색해서 결과를 얻는 것을 확인할 수 있습니다. 이번에는 기준 컬럼의 중요성에 대해 보기 위해 똑 같은 컬럼을 조회하되 조건의 기준의 되는 컬럼을 바꿔보겠습니다 첫 번째는 생성한 인덱스와 같은 기준으로 조건을 걸었기 때문에 인덱스에만 가서 결과값을 얻을 수 있었습니다 두번째는 first_name을 기준으로 조회를 했습니다 IFFS 가 발생하는데 먼저 이 IFFS란 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock read방식으로 스캔을 합니다 파티션되어 있지 않아도 병렬 쿼리가 가능하며 인덱스 리프 노드의 양방향 연결 리스트 구조를 이용하지 않기 때문에 얻어진 결과 집합이 인덱스 키 순서대로 정렬되지 않습니다 IFFS는 인덱스가 가진 컬럼의 조건만 검색하면서 넓은 범위를 탐색할 때 나타나는 방식입니다.
반면 정확한 힌트를 사용할 경우 최상의 실행계획을 얻을 수 있습니다
인덱스를 사용하다 보면 인덱스에 대한 잦은 DML로 인해 구조가 불균형해질 수 있습니다 특히 delete되고 남은 공간은 재사용전까지 공간만 남은채 인덱스 scan시 불필요한 scan을 유발하는데 이를 해결하는 방법이 인덱스 리빌드 입니다
인덱스를 리빌드하는 방법으로는 online / offline 2가지 방법이 있는데 먼저 online 방법을 선택할 경우 인덱스에 대한 리빌딩 도중에도 DML작업을 수행할 수 있습니다 offline의 경우 인덱스에 대한 리빌딩이 끝나기 전가지 DML 작업을 완료할 수 없습니다
online옵션을 사용하여 인덱스의 리빌드가 가능한 이유는 온라인 명령을 통해 인덱스 리빌드 명령 후 DML로 인해 변경된 데이터를 기록하는 temporary journal table이 이 생기고 이 저널 테이블은 IOT type입니다 인덱스 리빌드가 끝나게 되면 오라클은 저널 테이블에 들어갔던 변경된 데이터를 merge합니다 즉 온라인 리빌드하는 동안 변경된 데이터를 다시 인덱스에 반영하는 과정을 거치는 것입니다
Online일 경우 해당 인덱스를 가지고 있는 테이블에 DML이 허용되지만 비교적 느린편입니다 테이블에 shared mode lock이 걸리기 때문에 DDL은 허용되지 않습니다 도중에 변경된 테이블은 새로운 인덱스가 리빌드 된 후 기본 테이블에 저장된다
Offline일 경우 인덱스를 가진 테이블에 Exclusive lock이 걸리고 DML및 DDL이 허용되지 않지만 비교적 빠른 속도를 보인다
리빌딩 인덱스는 인덱스공간의 절약을 통한 효율적인 인덱스 스캔을 보장하는 것은 사실이지만 리빌딩 인덱스 후 인덱스 블록의 split 현상에 대한 고민이 필요합니다 b*Tree 인덱스는 데이터를 추가하는 과정에서 리프 노드가 꽉 차면 split을 하면서 balance를 맞추게 되는데 만약 세션 A가 TX락을 베타적으로 획득한 상태에서 스플릿을 수행하는 도중 세션 B가 해당 리프 노드를 변경하고자 할 때 대기 이벤트가 발생하고 이것을 TX-index contention 이벤트라고 합니다
인덱스 스플릿 현상이 자주 발생하는 현상은 아니지만 동시에 여러 세션이 인덱스가 생성된 테이블에 대해 많은 양의 DML을 수행하는 경우에 주로 발생하며 상당한 성능 저하의 원인이 된다 데이터의 중간에 insert가 되서 발생하는 50-50 split현상과 데이터의 끝에 insert가 일어나서 발생하는 90-10 split 현상이 있다 인덱스 split에 대한 경합을 줄이는 기본적 방법은 동일한 리프블록에 집중적으로 데이터가 추가되는 현상을 막는 것이다 파티셔닝 기법으로 물리적 분산을 시키거나 인덱스 컬럼의 순서를 변경하여 흩어지게 만들 수 있다
인덱스의 오른쪽에 집중되는 현상을 right growing index 혹은 index scew 라고 한다 이것을 해결하는 방법 중 하나가 리버스 키 인덱스를 사용하는 방법인데 인덱스 key value값을 뒤짚어서 저장하는 방법으로 인덱스의 고른 분산을 유도할 수 있지만 정렬되어 자리 잡은 key 값을 분산 저장 하지만 이로 인해 리버스 키 인덱스는 range scan이 불가 key value가 reverse 된 채로 정렬되기 때문에 기존에 같은 범위에 있던 값들이 분산되므로
일반적으로 시퀀스로 생성되는 컬럼에 기본 키를 생성한다면 새로운 값을 insert 하고자 할 때 현재 인덱스 구조의 오른쪽 블록을 수정하려고 할 것이다 시퀀스 값의 발생에 따른 인덱스의 수정이 몇 개의 리프 블록에 집중되어 발생한다 그 현상을 보이고 리버스 키로 문제를 해결하는 것을 보기 위한 테스트다 다른 인덱스와는 달리 리버스 키 인덱스는 좀 더 많은 공간을 할당 받고 더 많은 리프 블록을 사용하여 핫 스팟 현상을 줄일 수 있는 구조로 만들어진걸 볼 수 있다
압축된 인덱스를 사용하게 되면 이전보다 더 많은 인덱스 엔트리가 버퍼 캐시에 존재하게 되고 hit ratio가 증가함으로써 물리적 I/O는 감소 그러나 그만큼 더 많은 인덱스를 버퍼 캐시에 올리기 때문에 그만큼 CPU를 좀 더 사용하게 되고 이에 따라 블록에 대한 경합도 증가하면서 CPU관련 작업이 늘어나게 된다
Bitmap index
하나의 인덱스 키 엔트리에 많은 로우에 대한 포인터를 저장하는 구조로서 비트를 저장할 때는 start rowed ~ end rowid로 저장을 하므로 일종의 압축 개념 즉 내부적 압축 알고리즘을 사용하여 bitmap을 생성하기 때문에 저장 공간이 매우 절약된다
전체 데이터에 비해 값의 종류가 적은 데이터에 적당 rows의 개수에 따라 카디널리티 값은 상대적으로 변하기 때문에 Case By Case 형태로 생각해야 한다
DW 혹은 다양한 조건의 쿼리를 실행해서 다각적 분석을 하는 환경에서 정말 필요한 방식이며 임의의 쿼리 즉 Ad-hoc 쿼리에 최적화되어 다양한 조건의 쿼리에 B*tree 인덱스에 비해 좋은 성능을 보장한다
OLTP 환경에서 적합하지 않는 이유는 하나의 비트맵 인덱스 키가 많은 로우를 가리키기 때문에 하나의 세션에서 인덱스 엔트리를 수정하게 되면 인덱스가 포인터하고 있는 모든 로우를 락킹하기 때문이다 이 때문에 같은 비트맵 인덱스 엔트리를 수정하는 모든 작업은 이전의 락킹이 풀릴풀릴 때 기다려야 하고 이는 동시성에 대한 심각한 제한이다