[박준연]“인덱스에 대한 구라” 누가 그랬을까~?

나 혹은 우리는 그동안 책에 쓰여진 지식을 별다른 의심 없이 절대적 진리로 믿곤 했다. 아마 ‘교과서를 줄줄 외워야 하는 잘못된 학습 방법이 나 혹은 우리에게 지식을 얻는데 있어 의심하는 습관을 앗아간 것이 아닐까’라는 추측을 해본다.

말머리를 써놓고 읽어보니 왠지 이제부터 쓸 글의 방향을 ‘대한민국의 잘못된 교육방법’ 쯤으로 바꿔야만 할 것 같은 기분이나 아직 그에 대한 깊은 통찰력이 없어 아쉬울 뿐이다. ^^;

 몇 일전의 진땀 나는 경험이 있었다. 결론부터 말하자면 그 경험에 의해 배운 것은 내 지식 바구니에 무언가를 담으려면 그만한 대가 혹은 노력 없이 그저 책의 내용 또는 TEXT를 담는 것은 팝콘이나 강냉이를 담는 것과 같다는 것을 배웠다.

 사건은 어떤 책(밝히지는 않겠다)을 주제로 스터디하는 자리에 내가 발표할 날에 일어났다. 주제는 B-tree 인덱스에 관한 내용을 정리해서 발표하는 것이었다. 준비할 시간이 촉박한 것도 아니었고, 스터디에 대한 의욕이 저하된 것도 아니었으며, 더군다나 내가 공부한 내용을 다른 사람에게 발표하여 정보를 공유하도록 하는 취지에 거부하는 것도 아니었으나 난 내 발표 준비를 대충했다… -_-;

 구차한 변명이야 왜 없겠냐만은…

어쨌든 책의 내용을 보고 정리하여 발표하는 것은 내가 그동안 공부한 것으로 마치 상식을 이야기 하듯 하면 될 줄 알았으나 큰 낭패를 보았다. 비겁한 변명처럼 들릴지 모르겠으나 결론은 책이 구라친 것과 일반적인 오해에서 비롯된 잘못된 상식 또 냉정히 이야기하자면 지식을 습득하는 나의 태도가 너무 순진했고 수동적이었기 때문이다.

 사건 당일 날 가장 critical하게 문제가 된 부분은

1. B-tree Index는 DML 작업 중 Delete 작업에 의해 로우 또는 블록 전체가 삭제 되면 그 공간 또는 블록을 사용하지 못한다.
2. B-tree Index를 rebuild하면 CF가 향상 된다.
3. Alter Index [Index name] rebuild online; 명령어는 정말 Online일 것이다. 즉 락을 잡지 않을 것이다.

라는 세 가지 문제였다. 2번은 일반적으로 잘못된 상상력으로 인한 오류라고 할 수 있다.
1번은 간단한 테스트를 통해 알아보자.

<TEST Scenario 1>
- 한 블록에 인덱스는 3개의 rows, 테이블은 4개의 rows가 들어가도록 테스트 테이블 생성 (블록 크기는 8k)
- Delete문을 실행하여 하나의 row와 한 블록에 해당하는 rows 삭제 후 인덱스 리프 블록 수에 어떤 변화가 일어나는지 조회를 통해 알아보자.

SQL> create table test (id number, name char(2000), day date);

테이블이 생성되었습니다.

SQL> create index test_name_idx on test(name);

인덱스가 생성되었습니다.

SQL> insert into test values (1,'A',sysdate);

1 개의 행이 만들어졌습니다.
SQL> insert into test values (2,'B',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (3,'C',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (4,'D',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (5,'E',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (6,'F',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (7,'G',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (8,'H',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (9,'I',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (10,'J',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (11,'K',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test values (12,'L',sysdate);
1 개의 행이 만들어졌습니다.

 테스트 테이블 생성 후 row 삽입 후 해당 테이블에 대한 통계정보 생성

SQL> exec dbms_stats.gather_table_stats (ownname=>'SCOTT', tabname=>'TEST', cascade=>TRUE);

PL/SQL 처리가 정상적으로 완료되었습니다.

 통계정보 생성 후 인덱스 리프 블록 수, CF 수치 등을 조회

SQL> select a.TABLE_NAME,a.BLOCKS,a.NUM_ROWS,b.LEAF_BLOCKS,b.CLUSTERING_FACTOR
  2  from dba_tables a, dba_indexes b
  3  where a.table_name = b.table_name
  4  and a.table_name = 'TEST';

TABLE_NAME        BLOCKS   NUM_ROWS  LEAF_BLOCKS    CLUSTERING_FACTOR
----------------- ---------- ------------- --------------- ------------------------
TEST                         4                 12                 4                              4

 테이블의 블록 수 인덱스 리프 블록 수가 모두 4개이며(테이블 블록은 헤더 블록까지 포함됨), 테이블은 정렬되어 생성했으므로 CF도 블록 숫자와 같은 4를 기록한다. (CF가 로우 수치에 가까워질수록 불리) 여기서 Delete문을 통해서 한 블록에 해당하는 로우를 지우고 다시 한 블록에 해당하는 로우를 인서트를 수행한 이후에 통계정보를 생성하고 정보를 조회해 보자.

SQL> delete test where id = 10 or id = 11 or id = 12;

3 행이 삭제되었습니다.

SQL> insert into test values (13,'M',sysdate);

1 개의 행이 만들어졌습니다.

SQL> insert into test values (14,'N',sysdate);

1 개의 행이 만들어졌습니다.

SQL> insert into test values (15,'O',sysdate);

1 개의 행이 만들어졌습니다.

SQL> exec dbms_stats.gather_table_stats (ownname=>'SCOTT', tabname=>'TEST', cascade=>TRUE);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select a.TABLE_NAME,a.BLOCKS,a.NUM_ROWS,b.LEAF_BLOCKS,b.CLUSTERING_FACTOR
  2  from dba_tables a, dba_indexes b
  3  where a.table_name = b.table_name
  4  and a.table_name = 'TEST';

TABLE_NAME            BLOCKS     NUM_ROWS     LEAF_BLOCKS      CLUSTERING_FACTOR
--------------------- ---------- -------------- ---------------- -------------------------
TEST                               4                 12                     4                                4


최종 결과는 처음 결과와 완벽하게 동일하다는 것이다. 책의 논리대로라면 다른 건 몰라도 리프 블록 수와 CF 수치는 변해야 한다. 왜냐하면 인덱스 블록 하나에 해당하는 로우를 삭제하면 그 블록은 다시 재사용되지 않고 Flag 표시만 해두고 다시 인서트를 수행하면 새로운 블록에 로우를 담아야 하기 때문이다. 하지만 리프 블록의 수를 보라. 원래의 시나리오라면 총 5개의 블록이 있고 그 중 한 개는 텅텅빈 Flag만 표시된 블록이 있어야 하지만 결과는 4개! 즉, 지워진 블록을 재사용 한다는 것이다.

위 결과로 미루어 보아 인덱스의 Depth가 깊어지고 size가 증가하는 근본적인 원인은 DML이라고 통칭하는 것보다 Index Split이라고 콕 짚어 표현하는 것이 옳다고 생각한다.

<

TEST Scenario 2>
두 번째 테스트는 일반적으로 널리 퍼진 근원을 알 수 없는 오해인 Index rebuild를 수행하면 CF가 좋아질 것이란 착각에 대한 테스트이다. 같은 환경에서 중간 값을 마구 Insert한 이후 즉, Index Split을 발생시킨 이후의 CF 값을 통해서 그 오해를 풀어보도록 하겠다.

SQL> create table test2 (id number, name char(2000), day date);
테이블이 생성되었습니다.
SQL> create index test2_name_idx2 on test2(name);
인덱스가 생성되었습니다.
SQL> insert into test2 values (1,'A',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (3,'C',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (4,'D',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (5,'E',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (7,'G',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (9,'I',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (10,'J',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (11,'K',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (12,'L',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (14,'N',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (15,'O',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (17,'Q',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (18,'R',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (19,'S',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (21,'V',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (22,'W',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (24,'Y',sysdate);
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.

 중간 값 삽입하여 split 유도

SQL> insert into test2 values (2,'B',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (6,'F',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (8,'H',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (13,'M',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (16,'P',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (20,'T',sysdate);
1 개의 행이 만들어졌습니다.
SQL> insert into test2 values (23,'X',sysdate);
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.

 통계정보 생성 후 정보 조회

SQL> exec dbms_stats.gather_table_stats (ownname=>'SCOTT', tabname=>'TEST2', cascade=>TRUE);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select a.TABLE_NAME,a.BLOCKS,a.NUM_ROWS,b.LEAF_BLOCKS,b.CLUSTERING_FACTOR
  2  from dba_tables a, dba_indexes b
  3  where a.table_name = b.table_name
  4  and a.table_name = 'TEST2';

TABLE_NAME            BLOCKS    NUM_ROWS    LEAF_BLOCKS    CLUSTERING_FACTOR
------------------- ------------ ------------- ---------------- --------------------------
TEST2                            12                24                    12                            19 

CF 값이 rows 수에 가까워졌음을 알 수 있다. 이제 Index rebuild를 수행한 이후의 결과를 보자.

SQL> alter index test2_name_idx2 rebuild;

인덱스가 변경되었습니다.

SQL> exec dbms_stats.gather_table_stats (ownname=>'SCOTT', tabname=>'TEST2', cascade=>TRUE);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select a.TABLE_NAME,a.BLOCKS,a.NUM_ROWS,b.LEAF_BLOCKS,b.CLUSTERING_FACTOR
  2  from dba_tables a, dba_indexes b
  3  where a.table_name = b.table_name
  4  and a.table_name = 'TEST2';

TABLE_NAME               BLOCKS   NUM_ROWS     LEAF_BLOCKS   CLUSTERING_FACTOR
---------------------  ---------- -------------  ----------------  -----------------------
TEST2                              12               24                      8                            19

Index rebuild 이후에 변하는 수치는 인덱스 리프 블록 숫자일 뿐이다. 즉, Index rebuild는 CF에 아무런 효과도 없음을 결과를 통해 알 수 있다. 이것은 조금만 생각해보면 그럴 수 밖에 없는 이치이다. 비대해진 인덱스 즉, 비효율이 발생한 인덱스를 다시 compact하게 구성한다는 것은 단지 그 Size만을 줄여주는 것일 뿐 table block에는 어떠한 변화를 주는 것이 아니기 때문이다.

3번 항목은 이전에 이명진 씨가 테스트한 내용이 뒷 페이지에 있으니 그것을 참조하기 바란다.

우리는 자주 실수를 범하고 살아간다. 하지만 어떠한 경우는 자신이 실수를 했는지에 대한 검증 없이 무심코 지나치는 경우가 많은 것 같다. 그렇다고 그런 것을 너무 진지하게 고찰하고 의식하고 살면 인생이 너무 팍팍해서 재미없는 인생이 될 것 또한 뻔하다. 그러나 난 뭐 그렇다…. 내 지식 바구니에 무언가를 담을 때는 별 맛도 없고 턱 근육만 키워내는 팝콘이나 강냉이 보다는 입에 살살 녹는 초코칩 쿠키를 더 담고 싶다. 멜라민의 공포에서 해방되고 싶다면 손수 재료를 구하고 직접 구워야 하는 수고로움이 뒤따를 것이다. 

 그러나 최소한의 노력 없이 그냥 얻는 지식은 가짜이며, 그 가짜로 누군가에게 지식을 전달한다는 것은 거짓에 지나지 않으므로 TEST를 곁들인 이해가 수고롭고 귀찮더라도 내 머리에 가장 오래 남고 가치 있는 진짜배기로 남게 될 것임을 확신하며 글을 마친다.