엔코아의 자료입니다.
Index Skip Scan<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
오라클 9i에서 새로운 인덱스 액세스 메쏘드로 소개된 index skip scan을 소개합니다.
1. Index Skip Scan의 개념
인덱스를 경유한 테이블 액세스의 속도를 빠르게 하기 위해서 오라클 9i는 index skip scan이라는 새로운 인덱스 스캐닝 방식을 제공한다. Index skip scan의 데이터 access 방식을 한마디로 정리하면,
l 관련 테이블(컬럼)의 데이터 분포에 대한 통계 정보가 제대로 만들어져 있고,
l selectivity가 낮은 칼럼 + selectivity가 높은 칼럼으로 구성된 복합인덱스(concatenated index)를 가진 어떤 테이블 데이터를 질의할 때
l 선행 칼럼에 대한 선택 조건은 주지 않고, selectivity가 높은 칼럼의 선택 조건만 where 구문에 명시하면 기존에는 인덱스 선행 칼럼에 대한 조건이 명시 되지 않았기 때문에 전체 테이블에 대한 full scan이 발생했지만, 오라클 9i는 index skip scan이란 새로운 인덱스 스캐닝 방식으로 빠른 수행 속도를 보장한다.
간단한 sample을 들자면,
① sample table
|
테이블명: BALANCE 잔고테이블 | ||
|
BRANCH_NO |
VARCHAR2(3) |
지점번호 001 ~ 100 |
|
ACCNT_NO |
NUMBER |
10자리 예금별 일련번호 |
|
BALANCE |
NUMBER |
|
② index
인덱스: BALANCE_IDX1 = (BRANCH_NO, ACCNT_NO )
③ query
SELECT * FROM BALANCE WHERE ACCNT_NO = 1000000031;
④ plan
TABLE ACCESS BY INDEX ROWID COPYX
INDEX SKIP SCAN COPYX_IX1
이전 버전의 오라클에서 인덱스의 선행 칼럼인 지점번호에 대한 조건이 주어지지 않은 상태에서 계좌번호만으로 데이터를 조회하려고, 사용한 위와 같은 쿼리는 인덱스를 경유해서 데이터를 액세스하지 않고 Table Full Scan을 해 왔던 것을 우리는 잘 알고 있다. 이를 때 우리는 강제로 인덱스를 통한 조회를 유도하기 위해서 우리는 선행칼럼(지점번호)에 대한 정보를 알고 있는 경우 다음과 같이 subquery를 사용해서 상품 종류에 대한 정보를 제공하거나 프로그램에서 상수로 이 조건을 직접 공급시켜서 인덱스를 통한 테이블 액세스를 유도해 왔다. ( 대용량 데이터 베이스 솔루션 2 )
SELECT *
FROM BALANCE
WHERE BRANCH_NO IN ( SELECT BRANCH_NO FROM BRANCH )
AND ACCNT_NO = 10000031
그러나 이 경우 복합인덱스를 구성하고 있는 선행칼럼 개수가 많아질 때 (IN으로 여러 칼럼 조건이 공급될 때), 데이터 베이스가 주어진 칼럼들의 곱한 각 경우의 수 만큼의 equal match를 시도하게 되는 (실제 데이터는 존재하지 않으나 index의 root를 뒤져봄) 경우의 문제와 이 조건을 subquery로 공급하지 못하고 상수로 코딩해야 하는 경우 프로그램 변경등의 부담이 있어 왔다.
이에 대한 대안으로 제시된 index skip scan은 어떤 방식으로 인덱스를 액세스하는 것일까? 오라클이 간략하게나마 소개하고 있는 개념은 scan단위가 몇 개의 논리적인 sub-index로 나뉜다는 것인데 위의 예제의 수행 과정을 풀어보면, 만약 실 데이터가 두개의 지점번호만 가진 경우( 001과 002)를 가졌다고 가정하면 BALANCE_IX1 인덱스는 로지컬 하게 다음과 같이 두개의 sub-index로 나뉘어져서 스캔된다고 볼 수 있다. 즉 첫번째 sub-index는 001을 선두 값으로 갖고, 다른 하나의 sub-index는 002을 선두 값으로 갖는 파트라고 생각할 수 있다. 이것은 개념적으로는
SELECT *
FROM BALANCE
WHERE BRANCH_NO = ‘001’
AND ACCNT_NO = 100000031
UNION ALL
SELECT *
FROM BALANCE
WHERE BRANCH_NO = ‘002’
AND ACCNT_NO = 100000031
이라는 조건으로 차례대로 실행된다고 볼 수 있다. 이것을 그림으로 표현해 보면 다음과 같다.
[그림1]
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" />
그러나 도대체 sub-index는 어떤 단위로 만들어지고, scan할 block들을 어떤 순으로 access하는 것일까? Index skip scan의 index block access 방식을 확인해 보기 위해서 몇 가지 실행event dump를 확인한 결과 Skip scanning은 복합 칼럼인덱스를 로지컬하게 작은 단위의 sub-index로 분할해서 동작함을 알 수 있는데, logical sub-index의 개수는 선행 컬럼의 distinct value 개수와 밀접하게 관련 있음을 알 수 있다. 물리적으로 index skip scan이 발생할 때 데이터베이스는 우선 인덱스의 root 블록을 읽은 후, branch block을 차례로 access하면서 concatenated 되어 있는 복합인덱스 구성 value로 하위의 leaf block을 읽을지 말지 결정한다. 그러므로 논리적인 개념의 sub-index라는 것은 실제적으로는 branch block의 distinct value단위라고 볼 수 있을 것이다. 따라서 index skip scan은 전체 인덱스 블록을 모두 scanning하는 index fast full scan보다는 적인 수의 block을 access하게 되고, 칼럼 값을 바로 =로 공급한 (IN으로 공급한 경우) 보다는 많은 수의 블록을 읽게 된다. 실제 수행 속도의 경우도 첨부한 테스트 결과 집계 경우와 같이 시스템의 성격에 따라서 acceptable한 수준이라고 할 수 있다
[표1] Index Skip Scan 수행속도 테스트
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
2. Index Skip Scan의 활용
Index skip scan이라는 새로운 인덱스 액세스 메쏘드는 IN으로 선행칼럼의 조건을 모두 공급해주어야 했던 튜닝 부담을 어느 정도 경감시켜주지만 최고 query 성능을 보장하지는 못한다. 그리고 index skip scan을 믿고 사용하기에는 어느 정도 불안정한 요소가 있는데 우선은 첨부화일의 테스트 결과표에서 보여주는 바와 같이 query의 유형에 따라서 index skip scan이 발생하는 경우가 어느 정도 제한적이며, 임의로 힌트를 주어서 index skip scan을 강제하거나 막을 수 없기 때문에 데이터 분포에 대한 확신이 있을 때에만 사용할 수 있다.
단, 선행칼럼의 조건을 입력 받지 않고도 어느 정도의 수행속도를 보장한다는 일면에서는, 인덱스의 구조나 엑세스 원리에 대한 기본적인 이해가 없는 EUC 환경의 사용자가 임의의 쿼리를 수행하는 경우에도 어느 정도의 수행속도를 보장할 수 있고, 인덱스 구성시에 자주 사용되는 칼럼과 selectivity가 높은 칼럼중에서 어느 것을 선두로 둘 것이냐의 의사결정에 어느 정도의 고려 사항이 된다고 하겠다.
----------------------------------------------------
[참조]
query의 유형에 따라서 skip scan의 발생 양상이 예상과 틀린 경우도 많아서 몇 가지 유형의 query에 대한 검증과 skip scan에 대해서 일반적으로 가지는 의문점을 테스트한 결과를 첨부함.
[ITBANK진순쌤, 정보보안전문가] [펌]Index Skip Scan
이올린에 북마크하기
![[Illustration]](http://www.linuxfocus.org/common/images/illustration3.gif)
