“skip
scanning” for
index
:namespace
prefix = o ns = "urn:schemas-microsoft-com:office:office" />
구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr
전통적인
“복합 인덱스(composite
index)”의 경우 가장 많이 사용되고 분포도가 좋은 컬럼을
leading column으로 하는 것을 원칙으로 하고 있습니다. 그 이유는 여러
컬럼으로 인덱스가 구성되었을 경우 맨 처음 나타나는 컬럼이 중요한데, 예를 들어
where절에 leading column은 기술하지 않고 그 외의 컬럼을 기술하는
경우에는 index를 사용 할 수 없지만 index
컬럼만을 where절에 기술하는 경우엔 인덱스의 사용이 가능 하기 때문 입니다. (물론 select 항목이
index 영역에서 몽땅 가지고 올 수 있는 경우엔 CBO인 경우 leading columns이 where 절에 없더라도
인덱스를 full scan하여 데이터를 가지고 오긴 합니다.)
이를
보완하기 위해 Oracle9i에는 leading
column을 모르더라도 index를 사용 할 수 있는 방법이 있는데 이러한 형태로
데이터를 찾아 가는 것을 “skip
scanning”이라고 합니다.
skip scanning의 특징은 다음과 같습니다.
- 때때로 발생하는 leading 컬럼이 아닌 컬럼의 값만을
where절에 사용하는 Query를 지원
- 잠재적으로 많은 수의 인덱스 생성 필요성의 감소를 의미하며 따라서 DML의 성능 향상에
도움
- 복합 인덱스 생성시 어떤 것을 리딩 컬럼으로 할지에 대해 고민하지 않아도 되며 분포도(선택성)가 좋은 컬럼과 사용 빈도가 높은 컬럼이 있을 때 composite index를 만들 것인지 아니면 별도의 인덱스
2개를 만들 것인지에 대해 고민하지 않아도 된다는 것입니다.
- reverse key, bitmap, domain,
function-based index는 지원하지 않습니다.
처리
방식
- index는 branch
block을 통해 leaf block을 찾아갑니다.
- 따라서 branch block별 첫번째 엔트리부터 마지막
엔트리까지 대소 비교를 통해 사용자가 찾으려는 두번째 컬럼의 존재 유무를 확인합니다.
- 원하는 값을 찾을 가능성이 있으면 해당 leaf block
전체를 read 합니다.
- 원하는 값이 없다는 판단을 하면 (branch block의 대소 비교를 통해) skip 합니다.
- 원하는 값이 있을 가능성이 있어 read하는 경우 원하는 값의
범위를 넘는 값이 나타나면 나머지를 skip 합니다.
- 이상과 같은 skip scanning을 통해 block을 읽는 범위를 줄여 성능 향상을 가지고 올 수 있습니다.
---------------------------------------
[예]
SQL>CREATE TABLE emptest
AS
SELECT
ROWNUM AS
id
, MOD(ROWNUM,5) AS grp --5개씩 그룹핑
(0,1,2,3,4값만가짐)
, '홍길동'||ROWNUM AS
name --랜덤 문자5개
, DBMS_RANDOM.STRING('u',10) AS addr --랜덤문자
30개
FROM dual
CONNECT BY
ROWNUM <= 2500000 --250만건
만들자...
SQL> select count(*) from emptest;
COUNT(*)
----------
2500000
SQL> desc emptest;
이름 널?
유형
-----------------------------------------
-------- ---------------
ID
NUMBER
GRP
NUMBER
NAME VARCHAR2(46)
ADDR
VARCHAR2(4000)
SQL> alter table emptest add primary key (id);
-- 결합
인덱스를 만듭니다.
SQL> create index idx_emptest_name_addr on emptest(name,
addr);
인덱스가
생성되었습니다.
SQL> set autotrace on explain
SQL> -- 통계 정보를 생성 합니다.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,
'EMPTEST');
PL/SQL 처리가 정상적으로 완료되었습니다.
-- index힌트를 사용
SQL> select
2 name,
addr
3 from
emptest
4
where addr = '서울1234579';
NAME
ADDR
--------------------------------------------
홍길동1234579
서울1234579
경 과: 00:00:00.31
Execution Plan
----------------------------------------------------------
0 | SELECT
STATEMENT | | 171 |
8379 | 12820
(1)|
|* 1 | INDEX FULL SCAN | IDX_EMPTEST_NAME_ADDR
| 171 | 8379 | 12820
SQL> -- index_ss(skip scanning)힌트를
사용
SQL> select
2 name,
addr
3 from
emptest
4
where addr = '서울1234578'
5 ;
NAME
ADDR
--------------------------------------------
홍길동1234578
서울1234578
경 과: 00:00:00.50
Execution Plan
----------------------------------------------------------
0 | SELECT
STATEMENT | |
171 | 8379 | 10006 (1)|
|* 1 | INDEX SKIP SCAN | IDX_EMPTEST_NAME_ADDR
| 171 | 8379 | 10006
SQL> -- index_ffs(fast full scan)힌트를
사용
SQL> select
2 name,
addr
3 from
emptest
4
where addr = '서울1234575'
5 ;
NAME
ADDR
--------------------------------------------
홍길동1234575
서울1234575
경 과: 00:00:00.29
Execution Plan
| 0 | SELECT
STATEMENT | | 171 |
8379 | 3490
|* 1 | INDEX FAST FULL SCAN| IDX_EMPTEST_NAME_ADDR
| 171 | 8379 |
Index_ffs 힌트를 사용했을 때 결과가 가장 좋았다. 각자 해보시길…
댓글 없음:
댓글 쓰기