ORACLE INDEX SKIP SCAN
from OTN http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#51553
from ORACLE FAQs | opt_param hint http://www.orafaq.com/forum/t/145812/0/
from 욱짜의ORACLE블로그 | opt_param hint http://ukja.tistory.com/106
1. INDEX SCAN의 종류INDEX UNIQUE SCAN
A.
INDEX
SKIP SCAN
B.
INDEX
FULL SCAN
C.
FAST
FULL INDEX SCAN
(OTN 링크에 추가적인 스캔방법이
있다.)
2. INDEX SKIP SCAN
A.
INDEX
SKIP SCAN은 CBO (비용기반최적화) 를 필요로 한다.
B.
일반적으로 인덱스 스캔은 테이블블록
스캔보다 속도가 빠르다. 인덱스 스킵스캔은 조합키(COMPOSITE
INDEX) 사용시 선행키값없이 인덱스를 사용하는 방법이다.
C.
SKIP
INDEX는 복합키를 논리적으로 작은 인덱스로 분해한다.
D.
스킵스캐닝에서는 조합키의 기본컬럼(LEADING COLUMN)은 명기되지 않는다. 달리 말해 스킵된다.
E.
서브인덱스의 수는 기본컬럼(LEADING COLUMN)의 DISTINCT VALUE와 동일하다.
F.
SKIP
SCANNING 은 LEADING COLUMN의 DISTINCT VALUE가 적을수록 유리하다. 그리고 NONLEADING COLUMN의 DISTINCT VALUE가 클수록
좋다.
( ex : index(sex, employee_id) )
( from OTN )
A complete
scan of the index is not performed, but the subindex with the value F is searched first, followed by a search of the subindex with the
value M
본래 LEADING COLUMN 없이 조합키를 사용하려 시도하면 INDEX FULL
SCAN이 일어난다.
3. INDEX SKIP SCAN 예제
SQL> create table skip
2 (sex varchar2(1),
3 employee_id number,
4 reg1 date default sysdate,
5 reg2 date default sysdate,
6 reg3 date default sysdate);
Table created.
테스트할 인덱스를 만들었다. 고정사이즈 컬럼을 만드는 경우
( ex : CHAR ( INTEGER ) )
SKIP SCAN을 이용하지 못하는 경우가 있어 위와같이 ROW SIZE를
늘리기 위해 DEFAULT 값으로 SYSDATE를 사용하였다.
/*+ 고정길이를 이용해 만들어보기, HINT 찾아보기*/
SQL> declare
2 temp number;
3 begin
4 for i in 1..50000 loop
5 temp := 100000 - i;
6 insert into skip(sex,employee_id) values('M',i);
7 insert into skip(sex,employee_id) values('W',temp);
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
기존에 이야기한 이상적인 SKIP SCAN 을 위한 데이터를
만들었다. 선행컬럼의 DISTINCT VALUE는 낮게 ( 비트맵같이 ) 조합인덱스를 이루는 다른 컬럼의 DISTINCT VALUE 는 크게 나오게 만들었다. (UNIQUE 제약조건
같이 )
SQL> commit;
Commit complete.
SQL> select index_name from user_indexes;
INDEX_NAME
------------------------------
EMP_ID_PK
T1_NUM_IDX
SQL> create index skip_idx on skip(sex,
employee_id);
Index created.
COMPOSITE INDEX를 생성하였다.
SQL> set autotrace traceonly explain
SQL> desc skip
Name
Null? Type
-----------------------------------------
-------- ----------------------------
SEX VARCHAR2(1)
EMPLOYEE_ID NUMBER
REG1
DATE
REG2
DATE
REG3
DATE
SQL> select sex,employee_id
2 from skip
3 where employee_id = 7788;
(LEADING COLUMN 조건 없이 제약조건을 주었다.)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'SKIP'
( FULL TABLE SCAN이 일어났다. )
SQL> analyze table skip compute
statistics;
Table analyzed.
(테이블의 통계정보를
수집했다.)
SQL> select sex,employee_id
2 from skip
3 where employee_id = 7788;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)
1 0 INDEX (SKIP SCAN) OF 'SKIP_IDX' (NON-UNIQUE)
(Cost=4 Card=
1 Bytes=5)
(SKIP SCAN이 일어났음을확인하였다.)
SQL> select object_id from user_objects
2 where object_name = 'SKIP_IDX';
OBJECT_ID
----------
6486
( INDEX BLOCK 의 덤프를 뜨기 위해 OBJECT ID를 찾았다. )
SQL> alter session set events 'immediate
trace name treedump level 6486';
Session altered.
<<다음은 USER_DUMP_DEST 에 떨어진 TRACE
FILE 내용이다. >>
branch: 0xc00ba4 12585892 (0: nrow: 2, level:
2)
branch: 0xc01146 12587334 (-1: nrow: 285, level: 1)
branch: 0xc0122e 12587566 (0: nrow: 228, level: 1)
위의 내용은 LEAF 블록을 제외한 나머지 ( BRANCH + ROOT ) 를 정리해 썼다. 위의 블록 주소를
가지고 각 블록에 대하여 덤프를 시행했다.
( 블록덤프 뜨는
내용은 ORACLE 9i INDEX BLOCK 게시물을 참고 )
아래의 덤프내용을 확인하기에 앞서 간단히 설명을 하면 이 인덱스는 3의 height 를 가지고 branch block 을 두개 가지고 있다.
Branch block dump (root)
=================
row#0[3948] dba: 12587566=0xc0122e
col 0; len 1; (1): 57
col 1; len 4; (4): c3 06 3b 21
col 2; TERM
위의 값 (?) 우측에 있는 헥사코드를 char 값으로 변환하면
( 블록덤프 뜨는
내용은 ORACLE 9i INDEX BLOCK 게시물을 참고 )
col0 에 W 값이 들어가 있으며
col1 에는 ? (공백) ; ! 값이 들어가있다.
인덱스 블록의 value는 구분지을 값이 들어간다. W는 WOMAN 으로 사용할 첫자이며
이를 기준으로 MAN / WOMAN 으로 나뉘어진다.
Branch block dump (0)
=================
row#0[3949] dba: 12585894=0xc00ba6
col 0; len 1; (1): 4d
col 1; len 3; (3): c2 03 0d
col 2; TERM
~~ 중략 ~~
row#253[960] dba: 12587302=0xc01126
col 0; len 1; (1): 4d
col 1; len 4; (4): c3 05 63 14
col 2; TERM
row#254[948] dba: 12587303=0xc01127
col 0; len 1; (1): 57
col 1; len 4; (4): c3 06 01 0d
col 2; TERM
~~ 중략 ~~
row#283[601] dba: 12587332=0xc01144
col 0; len 1; (1): 57
col 1; len 4; (4): c3 06 39 27
col 2; TERM
Branch block dump (1)
=================
row#0[3948] dba: 12587335=0xc01147
col 0; len 1; (1): 57
col 1; len 4; (4): c3 06 3d 1b
col 2; TERM
~~ 중략 ~~
row#226[1241] dba: 12587565=0xc0122d
col 0; len 1; (1): 57
col 1; len 4; (4): c3 0a 63 47
col 2; TERM
위의 내용을 본 결과 위 그림과 같이(혹은 오해를 살 여지가
있는 부분이기도 하다.) 남성과 여성으로 딱 나뉘어 각각이 고유한 인덱스를 가지는 것이 아니라 두 값을
다 가지며 단지 선행컬럼에 관한 값을 기준으로 조합 인덱스를 이루는 다른 컬럼으로 정렬이 되어있는 구조로서 인덱스가 이분되지는 않는다.
/*+ 구조적인
이야기에 대해선 더 알아볼 필요가 있는 듯 하며 */ 기본적으로 보통의 조합인덱스 구조 그대로이다. 특별히 다른 구조를 가진다라던가 하는 점은 보이지 않는다.
4. INDEX SKIP SCAN 사용예제
수행환경은 9.2.0.4.0 이다.
SQL> select sex,employee_id
2 from skip
3 where employee_id = 5000;
S EMPLOYEE_ID
- -----------
M
5000
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)
1 0 INDEX (SKIP SCAN) OF 'SKIP_IDX' (NON-UNIQUE)
(Cost=4 Card=
1 Bytes=5)
실행플랜을 보면 SKIP SCAN 한 것을 볼 수 있다.
사용 데이터가 skip scan 에 적합하지 않은 경우 cbo 는 이 실행플랜을 사용하지 않는다.
5. INDEX SKIP SCAN 사용환경
_optimizer_skip_scan_enabled 가 false로 되어 있는 경우가 있다.
이 경우에는 table 의 데이터가 skip scan 을 할 수 있는 환경이어도 이용되지 않는다.
skip scan을 할 수 있게 만들기 위해선 세가지 방법이 있다.
l alter system
l alter session
l opt_param hint
위 내용을 분류해 다시 보면 system / session /
statement level 로 분류된다.
alter system 의 경우는 시스템 restart 가 필요하다.
주의할 점은 parameter 명을 double quotation 으로 감싸야 한다.
SQL> alter system set
"_optimizer_skip_scan_enabled"=true scope=spfile;
alter session 의 경우는 아래와 같다.
SQL> alter session set
"_optimizer_skip_scan_enabled"=true;
opt_param 의 경우 10g R2 에서 소개된 hint로 이전
버전에 아무리 사용해도 효과가 없다.
사용방법은 아래와 같다.
SQL> select /*+
opt_param('_optimizer_skip_scan_enabled','TRUE') */
2 sex,employee_id
3 from skip
4 where employee_id = 5000;
