태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

왜 인덱스 크기가 계속 커지는가 - 삭제된 공간이 재활용됨에도 불구하고

오라클 2008.06.13 21:09
아래 글에서 Index 내의 삭제된 공간은 재활용이 이루어짐을 정리한 바 있다.

2007/12/24 - [Advanced Oracle] - [Oracle is Mad] Index Rebuild를 둘러싼 논쟁 - Part2

하지만, 이 내용을 세미나 시간에 이야기하면 항상 이 질문이 따라 온다.

삭제하고 데이터 추가하는 방식으로 사용하는데 Index 크기가 계속 커지던데요?

다음과 같은 두 가지 상황에서 이런 일이 발생한다.

1. Commit 하지 않는 Delete

아래와 같은 작업에서는 Index의 크기가 변하지 않는다. 삭제 후 추가 작업이기 때문이다.

delete from t1 where ...; -- 5000 블록 삭제
commit;
insert into t1 select...; -- 5000 블록 추가

하지만, 아래와 같은 작업 시에는 Index의 크기가 추가된 데이터만큼 늘어난다.

delete from t1 where ...; --  5000 블록 삭제
insert into t1 select...; -- 5000 블록 추가

Oracle의 Transaction 관리 메커니즘은 위와 같은 경우 delete에 의해 확보된 Free Block이 Commit에 의해 확정되지 않은 상태에서는 Batch Insert에 의해 재사용되지 않도록 한다.


2. 작업 패턴 상의 문제

아래와 같은 세 가지 패턴의 작업이 있다. 세 패턴 모두 최종 결과는 10,000 건이다.
(모두 DML 사이에 Commit이 이루어진다고 가정한다)

-- Case1
insert(10,000)

delete(5,000)
delete(5,000)
insert(10,000)

-- Case2
insert(10,000)
delete(5,000)
insert(10,000)
delete(5,000)

-- Case3
insert(10,000)
insert(10,000)
delete(5,000)
delete(5,000)


비록 최종 데이터는 모두 10,000건이지만, 차지하는 공간은 10,000 : 15,000 : 20,000 이다. Index의 크기는 일단 커지고 나면 다시는 줄지 않기 때문이다. (Rebuild를 하지 않는 한)

위의 두 가지 사례가 반복적으로 작용하면 마치 Index의 공간이 다시는 재활용되지 않고 무한정 커지는 것으로 생각될 수 있다. Index 크기가 계속 증가하는 현상이 발생할 때는 위와 같은 상황에 해당하지 않는지 면밀하게 점검할 필요가 있을 것이다.

PS) 그 외에도 사례가 더 있을 수 있을 거 같다. 이 글을 읽고 많은 분들이 의견을 달았으면 좋겠다.

신고
tags :
Trackback 0 : Comments 4
  1. oracler 2008.06.20 21:49 신고 Modify/Delete Reply

    인덱스 크기는 오로지 rebuild 로만 줄이는것이 가능하다.............
    그러면 shrink 로도 인덱스 사이즈는 "원래" 줄어들수 없다는 건가요.?
    asa_recommendation 돌리면 index 도 shrink 해서 size 줄이라고 나오는데
    그럼 oracle 권고가 틀리다는건지요 ?
    그리고 size 측정은 정확히 무엇을 기준으로 하는건가요 dba_segment의 byte 인가요 dba_index 의 block 인가요. 가령, stats 으로 돌릴때와 analyze 돌릴때 동일컬럼에 대해 _index 나 _tables 의 통계정보가 미미한
    차이가 나기때문에 사이즈 측정을 무엇을 기준으로 하는지가 명확해야 할것 같습니다. (num_rows 같은거 다르게 나옴..)

  2. 욱짜 2008.06.20 22:06 신고 Modify/Delete Reply

    여기서 rebuild는 Online rebuild, offline rebuild, shrink space를 모두 포함한 일반적인 용어로 이해하시면 됩니다.
    Size 측정 기준은 상호 검증을 해보아야 할 거 같은데요? 기회가 되면 검증해보겠습니다. oracler 님께서도 검증 가능하시면 테스트 후 결과를 올려 주셔도 좋겠습니다.

  3. oracler 2008.07.01 00:42 신고 Modify/Delete Reply

    음....진심으로 말씀드리건데,
    행여나 딴지를 거는것처럼 느끼실까봐 많이 걱정되네요,
    제가 부족해서 (많이 몰라서) 여쭤본겁니다. 진심입니다.
    모르기때문에 질문이 이것저것 많음을 양해하여 주시면 감사하겠습니다.

    저번에 인덱스 힌트 이상한것 질문시 스크립트 올리라고 하셨는데요,
    어떤 상황이든지 그런 특정한 상황 재연 테스트 스크립트를 아무나 만드나요?
    그런 DB 전문가는 대한민국, 우리나라에 몇명 안될것입니다. 그중 한분이 조선생님이실것이구요.

    IT 에서 어떤 이상야릇한 상황을 시간이 지나서 재연할수 있다는것은 그 방면의 공인 전문가일것입니다.
    이 웹싸이트에 접속하는 여러 사람들중의 하나인 저에게
    그런 전문가적인 상호검증을 해봐라라든가, 스크립트를 올려달라는건
    저에겐 너무나 벅찬 말씀일수도 있다는점을 재고해주세요.

    리빌드가 쉬링크를 포함한다든 말은 처음 들어보는 "용어"입니다.
    온라인 오프라인만 같은 테두리로 생각했지 쉬링크까지의 같은 그룹인지는 정녕 몰랐네요.
    RBS 쉬링크는 알지만 RBS 도 리빌드가 되는건지 이거참 에구 어렵네요.

  4. 욱짜 2008.07.01 09:16 신고 Modify/Delete Reply

    Rebuild라는 용어는 저도 고민이 많았습니다. Offline Rebuild, Online Rebuild, Shrink Compact, Shrink Space, Coalesce 같은 명령들이 사실은 다 재구축(rebuild)의 범주에 속하는데(사실은 Move 명령도 Rebuild의 범주에 속하죠) 이미 Rebuild라는 명령문이 물리적으로 존재해버리니까 용어의 선정이 어렵더라구요. 그래서 저도 가끔 혼용해서 쓰고 있습니다.

    오해를 줄이려면 정확한 용어를 사용하는게 더 낫겠다는 판단이 듭니다. 즉 Rebuild와 Shrink는 전혀 다른 용어로 정확하게 분류하는게 좋겠네요.

    그리고 모든 케이스가 다 Test로 재현가능한 것은 아니죠. 하지만, 언제는 재현 가능하도록 하는 것이 최선이라고 봅니다. Test Case를 정확하게 만들고 재현할 수 있다면 원리와 해결책은 자연스럽게 따라 나오기 때문입니다.

    Test Case를 만들고 재현하는 것이 항상 어려운 것은 아닙니다. 여기도 아마 80:20의 법칙이 적용될 것으로 봅니다. 즉 80%는 재현이 쉬운 케이스일 것이고, 20%는 어렵거나 불가능한 경우일겁니다.

    제가 가끔 받는 질문들의 약 80%는 사실 문의하는 분들이 약간의 시간만 들이면 충분히 재현가능한 것들입니다. Test Case를 만들고 재현하는 기술이 늘면 Oracle과 관련된 지식도 자연스럽게 증가합니다. 처음엔 조금 어렵더라도 계속 시도하면 그 열매는 매우 클겁니다.

Write a comment

티스토리 툴바