index tree dump 뜨기

테스트용 테이블과 인덱스를 생성한다.

SQL> create table t1
         as
         select level as c1, rpad('x',10) as c2
         from dual
         connect by level <= 10000;

SQL> create index t1_idx1 on t1(c1, c2);

현재 index의 구조를 알아보기 위해 treedump를 뜬다.
tree dump를 뜨기 위하여 object_id를 구해야 한다. object id를 구하는 SQL은 다음과 같다.

SQL> select object_id, object_name
     from dba_objects
     where object_name = 'T1_IDX1';

     
 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------
     52954 T1_IDX1

SQL> alter session set events 'immediate trace name treedump level 52954';

 

user_dump_dest에 설정되어 있는 디렉토리로 이동하여 trace파일을 확인한다.

trace 파일을 확인해보면...

*** 2010-11-18 16:20:33.409
----- begin tree dump
branch: 0x40f44a 4256842 (0: nrow: 37, level: 1)
   leaf: 0x40f44b 4256843 (-1: nrow: 280 rrow: 280)
   leaf: 0x40f44c 4256844 (0: nrow: 276 rrow: 276)
   leaf: 0x40f44d 4256845 (1: nrow: 276 rrow: 276)
   leaf: 0x40f44e 4256846 (2: nrow: 276 rrow: 276)
   leaf: 0x40f44f 4256847 (3: nrow: 276 rrow: 276)
   leaf: 0x40f450 4256848 (4: nrow: 276 rrow: 276)
   leaf: 0x40f451 4256849 (5: nrow: 276 rrow: 276)
   leaf: 0x40f452 4256850 (6: nrow: 276 rrow: 276)
   leaf: 0x40f453 4256851 (7: nrow: 276 rrow: 276)
   leaf: 0x40f454 4256852 (8: nrow: 276 rrow: 276)
   leaf: 0x40f455 4256853 (9: nrow: 276 rrow: 276)
   leaf: 0x40f456 4256854 (10: nrow: 276 rrow: 276)
   leaf: 0x40f457 4256855 (11: nrow: 276 rrow: 276)
   leaf: 0x40f458 4256856 (12: nrow: 276 rrow: 276)
   leaf: 0x40f459 4256857 (13: nrow: 276 rrow: 276)
   leaf: 0x40f45a 4256858 (14: nrow: 276 rrow: 276)
   leaf: 0x40f45b 4256859 (15: nrow: 276 rrow: 276)
   leaf: 0x40f45c 4256860 (16: nrow: 276 rrow: 276)
   leaf: 0x40f45d 4256861 (17: nrow: 276 rrow: 276)
   leaf: 0x40f45e 4256862 (18: nrow: 276 rrow: 276)
   leaf: 0x40f45f 4256863 (19: nrow: 276 rrow: 276)
   leaf: 0x40f460 4256864 (20: nrow: 276 rrow: 276)
   leaf: 0x40f461 4256865 (21: nrow: 276 rrow: 276)
   leaf: 0x40f462 4256866 (22: nrow: 276 rrow: 276)
   leaf: 0x40f463 4256867 (23: nrow: 276 rrow: 276)
   leaf: 0x40f464 4256868 (24: nrow: 276 rrow: 276)
   leaf: 0x40f465 4256869 (25: nrow: 276 rrow: 276)
   leaf: 0x40f466 4256870 (26: nrow: 276 rrow: 276)
   leaf: 0x40f467 4256871 (27: nrow: 276 rrow: 276)
   leaf: 0x40f468 4256872 (28: nrow: 276 rrow: 276)
   leaf: 0x40f469 4256873 (29: nrow: 276 rrow: 276)
   leaf: 0x40f46a 4256874 (30: nrow: 276 rrow: 276)
   leaf: 0x40f46b 4256875 (31: nrow: 276 rrow: 276)
   leaf: 0x40f46c 4256876 (32: nrow: 276 rrow: 276)
   leaf: 0x40f46d 4256877 (33: nrow: 276 rrow: 276)
   leaf: 0x40f46e 4256878 (34: nrow: 276 rrow: 276)
   leaf: 0x40f46f 4256879 (35: nrow: 60 rrow: 60)
----- end tree dump

UPDATE가 발생한 후에 index가 어떻게 바뀌었는지 알아보기 위해 UPDATE문을 실행한다.

SQL> update t1
         set c2 = 'yyyyyy'
         where c1 < 5000;

SQL> commit;

update문이 발생한 후 실제로 index가 어떻게 바뀌었는지 확인해보자.
object_id가 동일하므로 이전에 dba_objects 뷰에서 구한 object id를 사용하여
tree dump 수행

SQL> alter session set events 'immediate trace name treedump level 52954';

*** 2010-11-18 16:22:57.538
----- begin tree dump
branch: 0x40f44a 4256842 (0: nrow: 55, level: 1)
   leaf: 0x40f44b 4256843 (-1: nrow: 262 rrow: 131)
   leaf: 0x40f470 4256880 (0: nrow: 298 rrow: 149)
   leaf: 0x40f44c 4256844 (1: nrow: 238 rrow: 119)
   leaf: 0x40f471 4256881 (2: nrow: 314 rrow: 157)
   leaf: 0x40f44d 4256845 (3: nrow: 254 rrow: 127)
   leaf: 0x40f472 4256882 (4: nrow: 298 rrow: 149)
   leaf: 0x40f44e 4256846 (5: nrow: 254 rrow: 127)
   leaf: 0x40f473 4256883 (6: nrow: 298 rrow: 149)
   leaf: 0x40f44f 4256847 (7: nrow: 254 rrow: 127)
   leaf: 0x40f474 4256884 (8: nrow: 298 rrow: 149)
   leaf: 0x40f450 4256848 (9: nrow: 254 rrow: 127)
   leaf: 0x40f475 4256885 (10: nrow: 298 rrow: 149)
   leaf: 0x40f451 4256849 (11: nrow: 254 rrow: 127)
   leaf: 0x40f476 4256886 (12: nrow: 298 rrow: 149)
   leaf: 0x40f452 4256850 (13: nrow: 254 rrow: 127)
   leaf: 0x40f477 4256887 (14: nrow: 298 rrow: 149)
   leaf: 0x40f453 4256851 (15: nrow: 254 rrow: 127)
   leaf: 0x40f478 4256888 (16: nrow: 298 rrow: 149)
   leaf: 0x40f454 4256852 (17: nrow: 254 rrow: 127)
   leaf: 0x40f479 4256889 (18: nrow: 298 rrow: 149)
   leaf: 0x40f455 4256853 (19: nrow: 254 rrow: 127)
   leaf: 0x40f47a 4256890 (20: nrow: 298 rrow: 149)
   leaf: 0x40f456 4256854 (21: nrow: 254 rrow: 127)
   leaf: 0x40f47b 4256891 (22: nrow: 298 rrow: 149)
   leaf: 0x40f457 4256855 (23: nrow: 254 rrow: 127)
   leaf: 0x40f47c 4256892 (24: nrow: 298 rrow: 149)
   leaf: 0x40f458 4256856 (25: nrow: 254 rrow: 127)
   leaf: 0x40f47d 4256893 (26: nrow: 298 rrow: 149)
   leaf: 0x40f459 4256857 (27: nrow: 254 rrow: 127)
   leaf: 0x40f47e 4256894 (28: nrow: 298 rrow: 149)
   leaf: 0x40f45a 4256858 (29: nrow: 254 rrow: 127)
   leaf: 0x40f47f 4256895 (30: nrow: 298 rrow: 149)
   leaf: 0x40f45b 4256859 (31: nrow: 254 rrow: 127)
   leaf: 0x40f480 4256896 (32: nrow: 298 rrow: 149)
   leaf: 0x40f45c 4256860 (33: nrow: 254 rrow: 127)
   leaf: 0x40f481 4256897 (34: nrow: 298 rrow: 149)  
   leaf: 0x40f45d 4256861 (35: nrow: 303 rrow: 276)
   leaf: 0x40f45e 4256862 (36: nrow: 276 rrow: 276)
   leaf: 0x40f45f 4256863 (37: nrow: 276 rrow: 276)
   leaf: 0x40f460 4256864 (38: nrow: 276 rrow: 276)
   leaf: 0x40f461 4256865 (39: nrow: 276 rrow: 276)
   leaf: 0x40f462 4256866 (40: nrow: 276 rrow: 276)
   leaf: 0x40f463 4256867 (41: nrow: 276 rrow: 276)
   leaf: 0x40f464 4256868 (42: nrow: 276 rrow: 276)
   leaf: 0x40f465 4256869 (43: nrow: 276 rrow: 276)
   leaf: 0x40f466 4256870 (44: nrow: 276 rrow: 276)
   leaf: 0x40f467 4256871 (45: nrow: 276 rrow: 276)
   leaf: 0x40f468 4256872 (46: nrow: 276 rrow: 276)
   leaf: 0x40f469 4256873 (47: nrow: 276 rrow: 276)
   leaf: 0x40f46a 4256874 (48: nrow: 276 rrow: 276)
   leaf: 0x40f46b 4256875 (49: nrow: 276 rrow: 276)
   leaf: 0x40f46c 4256876 (50: nrow: 276 rrow: 276)
   leaf: 0x40f46d 4256877 (51: nrow: 276 rrow: 276)
   leaf: 0x40f46e 4256878 (52: nrow: 276 rrow: 276)
   leaf: 0x40f46f 4256879 (53: nrow: 60 rrow: 60)
----- end tree dump

인덱스 블록에 split이 발생한 것을 확인할 수가 있다.