Index of Oracle 공부

(서론을 길게 쓰고 싶지만 참겠습니다. 왜 주절거리고 싶을까...;;)

Oracle의 index는 B*tree를 사용합니다. 이게 binary tree가 아니라 balanced tree 더군요.
그래서 알아서 밸런스를 맞춥니다. 하나의 parent node에 붙은 child node는 두개뿐이 아니라 더 늘어날 수 있는거죠.
index에서는 element가 삭제되면 그 자리를 곧바로 다시 껴맞춰서 빈자리가 안보이게 하는 방식이 아니라
그 빈자리를 나중에 재사용하는 방식입니다.
데이터가 쌓여서 인덱스가 다음과 같이 만들어졌다고 해보죠.

A,B,C,D,E,F

아...위의 알파벳이 매우 학점같아 보여서 잠시 눈물이... ㅠ.ㅠ
이때 C가 가리키던 데이터를 삭제하면

A,B,C,D,E,F

이렇게 됩니다. 이때 G가 들어가면

A,B,D,E,G 가 되는거죠. (C자리에 G가 들어가지 않는건... index니까 순서가 당연히 맞아야겠죠.ㅋ)

어쨋든 rebuild라는걸 합니다.

rebuild는 데이터를 보고 새로운 인덱스를 만든 뒤에 기존의 인덱스를 지우고 새로 만들어놓은 인덱스로 바꿔치는 방식을 사용합니다. 이렇게 하기 위해서는 새로운 인덱스를 만들기 위한 공간이 필요하겠죠? 그리고 필수적으로 데이터가 수정되는 것을 방지하기 위해서 데이터에 락을 걸어놓고 인덱스를 rebuild하게 됩니다. 만약 real time이 중요한 테이블의 경우 이렇게 락을 거는데 민감할 수 있으므로 oracle에서는 online rebuild라는 것을 제공하여 데이터 입력&수정을 하면서도 index를 rebuild 할 수 있도록 지원합니다. 하지만 enterprise 버전에서만 가능하므로 standard 버전 사용자는 꿈도 꾸지 마시길...
그래서 여기서는 standard 버전을 기준으로 설명합니다.

update, insert와 같은 DML 구분은 rebuild되는 동안 기다려야 합니다. 만약 테이블이 update나 insert되는 중에 rebuild를 하려면 이미 table에 락이 걸려있는 상태이므로 "테이블이 비지해요"라는 에러 메시지를 보게 될 것입니다. 그러니 시간 잘 골라서 하시길.ㅋ

인덱스를 optimizing하는 방법중에 coalesce라는 것이 있습니다. 이것은 지워진 인덱스자리를 채워넣는 방식으로 rebuild와는 조금 다릅니다. 새 인덱스를 만들기 위한 추가적인 공간이 필요 없는 대신 인덱스가 차지하고 있던 공간이 줄어들지는 않습니다. 즉, 총 용량은 변함이 없는 상태에서 빈칸채워넣기를 하는거죠. rebuild와 coalesce 모두 인덱스를 재정렬함으로써 검색 속도를 빠르게 해주는 장점은 있지만 저같이 인덱스 크기가 너무 커져버린 경우는 rebuild가 필요하겠죠.

-----------------------------------------------------------------------------------------------------
글은 계속 추가됩니다.
이전 글을 수정했습니다. 인덱스에서 사용하는 B*tree에 대한 오해가 있었던거죠.
-----------------------------------------------------------------------------------------------------

2008.04.15 오늘에서 내용을 추가하네요-_-

이전에 인덱스에 대한 오해에 대하여 몇자 수정을 해놓았습니다만...rebuild에 대한 오해도 있었기에 간단한 언급을 하고 실제로 index rebuild를 해보도록 하죠.
index rebuild는 분명 용량적인 측면에서는 해결책이 될 수 있습니다. 얼기설기 들어 있는 데이터를, 그것도 확장으로 인해서 불필요하게 큰 공간에 얼기설기 들어 있는 데이터를 잘 정리해서 실제 크기에 맞는 공간에 딱 맞춰서 데이터를 쌓아주니 용량이 줄어들어서 좋겠죠. 하지만 그 이후가 문제입니다. 딱 맞춘 양복을 입은 사람이 살이 쪄버리면 결국 새로운 옷을 사기 위해서 돈을 더 내야하는 것처럼, 인덱스도 딱 맞춰진 공간에 있다가 데이터가 늘어나서 추가 공간이 필요하면 그 공간 할당을 위해서 꽤 많은 리소스를 사용합니다. (이 부하가 꽤 크다고 하네요.) 이런 rebuild의 어두운 면도 있다는 점을 머리에 새겨두시고...이제 한번 rebuild를 해보죠.

아래는 index 이름을 parameter로 받아서 리빌드 해주고 그 전후의 분석 정보를 알려주는 procedure입니다.
create or replace PROCEDURE rebuild_index (index_name IN VARCHAR2)
IS
CURSOR icur IS
SELECT name, blocks, btree_space, used_space, pct_used, lf_rows, del_lf_rows FROM index_stats;
idx icur%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE ('INDEX_NAME BLOCKS BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS');
DBMS_OUTPUT.PUT_LINE ('---------------------------- --------- ------------ ---------- --------- -------- -----------');
EXECUTE IMMEDIATE 'ANALYZE INDEX ' || index_name || ' VALIDATE STRUCTURE';
OPEN icur;
FETCH icur INTO idx;
CLOSE icur;
DBMS_OUTPUT.PUT_LINE (RPAD (idx.name, 28) || LPAD (idx.blocks, 10) || LPAD (idx.btree_space, 13) || LPAD (idx.used_space, 11) || LPAD(idx.pct_used, 10) || LPAD(idx.lf_rows, 9) || LPAD(idx.del_lf_rows, 12));
EXECUTE IMMEDIATE 'ALTER INDEX ' || index_name || ' REBUILD';
EXECUTE IMMEDIATE 'ANALYZE INDEX ' || index_name || ' VALIDATE STRUCTURE';
OPEN icur;
FETCH icur INTO idx;
CLOSE icur;
DBMS_OUTPUT.PUT_LINE (RPAD (idx.name, 28) || LPAD (idx.blocks, 10) || LPAD (idx.btree_space, 13) || LPAD (idx.used_space, 11) || LPAD(idx.pct_used, 10) || LPAD(idx.lf_rows, 9) || LPAD(idx.del_lf_rows, 12));
END rebuild_index;


우선 index에 대해서 분석을 해야겠죠? index에 잠시 lock을 걸어두고 분석을 해보면 그 인덱스가 사용하는 block수, btree 공간, 실제 사용하는 공간 등을 알 수 있습니다.
그리고 이제 "alter index index_name rebuild"로 실제 rebuild를 합니다. (이때 인덱스 크기에 따라 시간이 꽤 걸립니다.)
그리고 rebuild가 끝나면 결과를 확인하기 위해서 한번 더 분석을 해봅니다. 아래는 그 결과의 예시입니다.

NAME BLOCKS BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
------------------------------ ---------- ----------- ---------- ---------- ---------- -----------
INDEX_IDX 13312 98400024 68397866 70 1799748 13532
INDEX_IDX 10240 76226860 68111046 90 1786220 2


실제로 차지하는 block수와 btree 공간, 그리고 삭제된 leaf node가 차지하던 공간이 현저히 줄어듬을 확인할 수 있습니다.

핑백

  • Soon it shall also come to pass. : [ORACLE] table과 index 파악하기 2007-09-14 10:28:50 #

    ... 귀찮아-_-) lf_rows는 leaf node로 등록된 row의 수, del_lf_rows는 삭제된 leaf node의 수를 말합니다. 일전에 올렸던 '바보같은 인덱스'에서 알 수 있듯이 table에서 데이터가 삭제되면 그 데이터와 매핑되어있던 인덱스도 삭제되어야 합니다. 인덱스의 크기는 줄어들지 않고, 트리구조상 leaf ... more

  • 그쟈잉 : [ORACLE] table과 index 파악하기 2009-03-06 19:47:28 #

    ... 알아봐야되는데...귀찮아-_-)lf_rows는 leaf node로 등록된 row의 수, del_lf_rows는 삭제된 leaf node의 수를 말합니다.일전에 올렸던 '바보같은 인덱스'에서 알 수 있듯이table에서 데이터가 삭제되면 그 데이터와 매핑되어있던 인덱스도 삭제되어야 합니다.인덱스의 크기는 줄어들지 않고, 트리구조상 leaf nod ... more

덧글

댓글 입력 영역



Google Analytics