Btree Index storage internal
----------------
Btree Index 原理
----------------
1.Oracle中的Btree Index具有3大結構，root節點，branch節點，leaf節點.Root節點始終緊跟索引段頭.
當索引比較小的時候，root節點，branch節點，leaf節點都儲存在同一個block中.Branch節點主要儲存
了索引的鍵值,但是這個鍵值並不是完整的，它只是完整索引值的部分字首.同時Branch節點還儲存了指向
leaf節點的指標(DBA)，另外有個主意的是branch節點中還有個叫kdxbrlmc的指標.Leaf節點主要儲存了完
整的索引鍵值，以及相關索引鍵值的部分rowid(這個rowid去掉了data object number部分)，同時leaf
節點還儲存了2個指標(DBA)，他們分別指向上一個leaf節點以及下一個leaf節點.
2.Btree Index 是始終平衡的，也就是說 從Root節點到 Leaf 節點的任何一個路徑都是等距離的.
3.Btree Index 預設是按照索引值升序排列的，當然了我們可以在建立/重建的時候設定它降序排列.
4.Index Scan 的時候，採用的是 sequential read,並且一次只能讀一個block(INDEX FAST FULL SCAN 除外).
5.Btree Index Update的 時候，先做的是 delete,然後進行insert.
6.Btree Index 不儲存 Null值，但是如果組合索引其中一列是非Null的，那麼組合索引也會儲存Null值.
---------------------------------
Btree Index 儲存原理
---------------------------------
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
SQL> show user
User is "robinson"
SQL> create table test as select * from dba_objects;
Table created
SQL> select count(*) from test;
COUNT(*)
----------
50352
SQL> insert into test select * from dba_objects;
50352 rows inserted
SQL> commit;
Commit complete
SQL> select count(*) from test;
COUNT(*)
----------
100704
SQL> create index i_test_object_name on test(object_name);
Index created
------------------------------------------------------------------------------
我們現在來看一下索引的Blevel,什麼是Blevel,Blevel可以通過DBA_INDEXES.Blevel獲得
------------------------------------------------------------------------------
B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.
從 root block 到 leaf block的深度. 如果root block 和 leaf block在同一個塊中 那麼 Blevel=0
SQL> select index_name,blevel from dba_indexes where index_name='I_TEST_OBJECT_NAME';
INDEX_NAME BLEVEL
------------------------------ ----------
I_TEST_OBJECT_NAME 2
-----------------------------------------------------------------------------------
我們現在來看一下索引的height，索引的高度等於Blevel+1，height可以通過INDEX_STATS獲得
-----------------------------------------------------------------------------------
SQL> analyze index I_TEST_OBJECT_NAME validate structure;
Index analyzed
SQL> select name,height from index_stats where name='I_TEST_OBJECT_NAME'; ---查詢index_stats之前需要 analyze ... validate structure，否則無資料
NAME HEIGHT
------------------------------ ----------
I_TEST_OBJECT_NAME 3
--------------------------------------------------------------------------------------------
Oracle 提供了分析 Btree index 結構的命令 treedump，在進行treedump之前需要獲得索引的object_id
--------------------------------------------------------------------------------------------
SQL> select object_id from dba_objects where object_name='I_TEST_OBJECT_NAME' and owner='ROBINSON';
OBJECT_ID
----------
55178
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 55178';
Session altered
------------------------
treedump trace 檔案
------------------------
----- begin tree dump
branch: 0x1800014 25165844 (0: nrow: 2, level: 2)
branch: 0x1800710 25167632 (-1: nrow: 247, level: 1)
leaf: 0x1800015 25165845 (-1: nrow: 182 rrow: 182)
leaf: 0x1800016 25165846 (0: nrow: 182 rrow: 182)
leaf: 0x1800017 25165847 (1: nrow: 186 rrow: 186)
leaf: 0x1800018 25165848 (2: nrow: 189 rrow: 189)
leaf: 0x1800019 25165849 (3: nrow: 186 rrow: 186)
leaf: 0x180001a 25165850 (4: nrow: 190 rrow: 190)
leaf: 0x180001b 25165851 (5: nrow: 185 rrow: 185)
leaf: 0x180001c 25165852 (6: nrow: 179 rrow: 179)
leaf: 0x180001d 25165853 (7: nrow: 187 rrow: 187)
leaf: 0x180001e 25165854 (8: nrow: 181 rrow: 181)
leaf: 0x180001f 25165855 (9: nrow: 186 rrow: 186)
leaf: 0x1800020 25165856 (10: nrow: 189 rrow: 189)
leaf: 0x1800022 25165858 (11: nrow: 187 rrow: 187)
leaf: 0x1800023 25165859 (12: nrow: 180 rrow: 180)
leaf: 0x1800024 25165860 (13: nrow: 182 rrow: 182)
leaf: 0x1800025 25165861 (14: nrow: 186 rrow: 186)
leaf: 0x1800026 25165862 (15: nrow: 188 rrow: 188)
leaf: 0x1800027 25165863 (16: nrow: 187 rrow: 187)
leaf: 0x1800028 25165864 (17: nrow: 185 rrow: 185)
leaf: 0x1800029 25165865 (18: nrow: 187 rrow: 187)
leaf: 0x180002a 25165866 (19: nrow: 185 rrow: 185)
leaf: 0x180002b 25165867 (20: nrow: 184 rrow: 184)
leaf: 0x180002c 25165868 (21: nrow: 188 rrow: 188)
leaf: 0x180002d 25165869 (22: nrow: 189 rrow: 189)
leaf: 0x180002e 25165870 (23: nrow: 184 rrow: 184)
leaf: 0x180002f 25165871 (24: nrow: 192 rrow: 192)
leaf: 0x1800030 25165872 (25: nrow: 187 rrow: 187)
leaf: 0x1800032 25165874 (26: nrow: 181 rrow: 181)
leaf: 0x1800033 25165875 (27: nrow: 182 rrow: 182)
leaf: 0x1800034 25165876 (28: nrow: 192 rrow: 192)
leaf: 0x1800035 25165877 (29: nrow: 186 rrow: 186)
leaf: 0x1800036 25165878 (30: nrow: 189 rrow: 189)
leaf: 0x1800037 25165879 (31: nrow: 181 rrow: 181)
leaf: 0x1800038 25165880 (32: nrow: 184 rrow: 184)
leaf: 0x1800039 25165881 (33: nrow: 181 rrow: 181)
leaf: 0x180003a 25165882 (34: nrow: 189 rrow: 189)
leaf: 0x180003b 25165883 (35: nrow: 184 rrow: 184)
leaf: 0x180003c 25165884 (36: nrow: 184 rrow: 184)
leaf: 0x180003d 25165885 (37: nrow: 183 rrow: 183)
leaf: 0x180003e 25165886 (38: nrow: 185 rrow: 185)
leaf: 0x180003f 25165887 (39: nrow: 185 rrow: 185)
leaf: 0x1800040 25165888 (40: nrow: 189 rrow: 189)
leaf: 0x1800042 25165890 (41: nrow: 187 rrow: 187)
leaf: 0x1800043 25165891 (42: nrow: 187 rrow: 187)
leaf: 0x1800044 25165892 (43: nrow: 186 rrow: 186)
leaf: 0x1800045 25165893 (44: nrow: 186 rrow: 186)
leaf: 0x1800046 25165894 (45: nrow: 183 rrow: 183)
leaf: 0x1800047 25165895 (46: nrow: 188 rrow: 188)
leaf: 0x1800048 25165896 (47: nrow: 185 rrow: 185)
leaf: 0x1800049 25165897 (48: nrow: 187 rrow: 187)
leaf: 0x180004a 25165898 (49: nrow: 186 rrow: 186)
leaf: 0x180004b 25165899 (50: nrow: 188 rrow: 188)
leaf: 0x180004c 25165900 (51: nrow: 180 rrow: 180)
leaf: 0x180004d 25165901 (52: nrow: 185 rrow: 185)
leaf: 0x180004e 25165902 (53: nrow: 186 rrow: 186)
leaf: 0x180004f 25165903 (54: nrow: 186 rrow: 186)
leaf: 0x1800050 25165904 (55: nrow: 184 rrow: 184)
leaf: 0x1800052 25165906 (56: nrow: 188 rrow: 188)
leaf: 0x1800053 25165907 (57: nrow: 184 rrow: 184)
leaf: 0x1800054 25165908 (58: nrow: 187 rrow: 187)
leaf: 0x1800055 25165909 (59: nrow: 188 rrow: 188)
leaf: 0x1800056 25165910 (60: nrow: 187 rrow: 187)
leaf: 0x1800057 25165911 (61: nrow: 189 rrow: 189)
leaf: 0x1800058 25165912 (62: nrow: 187 rrow: 187)
leaf: 0x1800059 25165913 (63: nrow: 185 rrow: 185)
leaf: 0x180005a 25165914 (64: nrow: 183 rrow: 183)
leaf: 0x180005b 25165915 (65: nrow: 191 rrow: 191)
leaf: 0x180005c 25165916 (66: nrow: 192 rrow: 192)
leaf: 0x180005d 25165917 (67: nrow: 189 rrow: 189)
leaf: 0x180005e 25165918 (68: nrow: 191 rrow: 191)
leaf: 0x180005f 25165919 (69: nrow: 190 rrow: 190)
leaf: 0x1800060 25165920 (70: nrow: 189 rrow: 189)
leaf: 0x1800062 25165922 (71: nrow: 190 rrow: 190)
leaf: 0x1800063 25165923 (72: nrow: 185 rrow: 185)
leaf: 0x1800064 25165924 (73: nrow: 186 rrow: 186)
leaf: 0x1800065 25165925 (74: nrow: 183 rrow: 183)
leaf: 0x1800066 25165926 (75: nrow: 189 rrow: 189)
leaf: 0x1800067 25165927 (76: nrow: 189 rrow: 189)
leaf: 0x1800068 25165928 (77: nrow: 184 rrow: 184)
leaf: 0x1800069 25165929 (78: nrow: 190 rrow: 190)
leaf: 0x180006a 25165930 (79: nrow: 193 rrow: 193)
leaf: 0x180006b 25165931 (80: nrow: 180 rrow: 180)
leaf: 0x180006c 25165932 (81: nrow: 186 rrow: 186)
leaf: 0x180006d 25165933 (82: nrow: 189 rrow: 189)
leaf: 0x180006e 25165934 (83: nrow: 184 rrow: 184)
leaf: 0x180006f 25165935 (84: nrow: 184 rrow: 184)
leaf: 0x1800070 25165936 (85: nrow: 188 rrow: 188)
leaf: 0x1800072 25165938 (86: nrow: 187 rrow: 187)
leaf: 0x1800073 25165939 (87: nrow: 182 rrow: 182)
leaf: 0x1800074 25165940 (88: nrow: 188 rrow: 188)
leaf: 0x1800075 25165941 (89: nrow: 181 rrow: 181)
leaf: 0x1800076 25165942 (90: nrow: 188 rrow: 188)
leaf: 0x1800077 25165943 (91: nrow: 190 rrow: 190)
leaf: 0x1800078 25165944 (92: nrow: 183 rrow: 183)
leaf: 0x1800079 25165945 (93: nrow: 192 rrow: 192)
leaf: 0x180007a 25165946 (94: nrow: 187 rrow: 187)
leaf: 0x180007b 25165947 (95: nrow: 188 rrow: 188)
leaf: 0x180007c 25165948 (96: nrow: 188 rrow: 188)
leaf: 0x180007d 25165949 (97: nrow: 188 rrow: 188)
leaf: 0x180007e 25165950 (98: nrow: 192 rrow: 192)
leaf: 0x180007f 25165951 (99: nrow: 186 rrow: 186)
leaf: 0x1800080 25165952 (100: nrow: 185 rrow: 185)
leaf: 0x1800082 25165954 (101: nrow: 183 rrow: 183)
leaf: 0x1800083 25165955 (102: nrow: 187 rrow: 187)
leaf: 0x1800084 25165956 (103: nrow: 191 rrow: 191)
leaf: 0x1800085 25165957 (104: nrow: 184 rrow: 184)
leaf: 0x1800086 25165958 (105: nrow: 183 rrow: 183)
leaf: 0x1800087 25165959 (106: nrow: 180 rrow: 180)
leaf: 0x1800088 25165960 (107: nrow: 187 rrow: 187)
leaf: 0x1800609 25167369 (108: nrow: 178 rrow: 178)
leaf: 0x180060a 25167370 (109: nrow: 189 rrow: 189)
leaf: 0x180060b 25167371 (110: nrow: 188 rrow: 188)
leaf: 0x180060c 25167372 (111: nrow: 185 rrow: 185)
leaf: 0x180060d 25167373 (112: nrow: 185 rrow: 185)
leaf: 0x180060e 25167374 (113: nrow: 182 rrow: 182)
leaf: 0x180060f 25167375 (114: nrow: 184 rrow: 184)
leaf: 0x1800610 25167376 (115: nrow: 187 rrow: 187)
leaf: 0x180068b 25167499 (116: nrow: 186 rrow: 186)
leaf: 0x180068c 25167500 (117: nrow: 183 rrow: 183)
leaf: 0x180068d 25167501 (118: nrow: 184 rrow: 184)
leaf: 0x180068e 25167502 (119: nrow: 187 rrow: 187)
leaf: 0x180068f 25167503 (120: nrow: 185 rrow: 185)
leaf: 0x1800690 25167504 (121: nrow: 179 rrow: 179)
leaf: 0x1800691 25167505 (122: nrow: 185 rrow: 185)
leaf: 0x1800692 25167506 (123: nrow: 190 rrow: 190)
leaf: 0x1800693 25167507 (124: nrow: 187 rrow: 187)
leaf: 0x1800694 25167508 (125: nrow: 190 rrow: 190)
leaf: 0x1800695 25167509 (126: nrow: 190 rrow: 190)
leaf: 0x1800696 25167510 (127: nrow: 184 rrow: 184)
leaf: 0x1800697 25167511 (128: nrow: 183 rrow: 183)
leaf: 0x1800698 25167512 (129: nrow: 183 rrow: 183)
leaf: 0x1800699 25167513 (130: nrow: 186 rrow: 186)
leaf: 0x180069a 25167514 (131: nrow: 186 rrow: 186)
leaf: 0x180069b 25167515 (132: nrow: 187 rrow: 187)
leaf: 0x180069c 25167516 (133: nrow: 182 rrow: 182)
leaf: 0x180069d 25167517 (134: nrow: 189 rrow: 189)
leaf: 0x180069e 25167518 (135: nrow: 189 rrow: 189)
leaf: 0x180069f 25167519 (136: nrow: 188 rrow: 188)
leaf: 0x18006a0 25167520 (137: nrow: 187 rrow: 187)
leaf: 0x18006a1 25167521 (138: nrow: 185 rrow: 185)
leaf: 0x18006a2 25167522 (139: nrow: 189 rrow: 189)
leaf: 0x18006a3 25167523 (140: nrow: 187 rrow: 187)
leaf: 0x18006a4 25167524 (141: nrow: 191 rrow: 191)
leaf: 0x18006a5 25167525 (142: nrow: 181 rrow: 181)
leaf: 0x18006a6 25167526 (143: nrow: 194 rrow: 194)
leaf: 0x18006a7 25167527 (144: nrow: 186 rrow: 186)
leaf: 0x18006a8 25167528 (145: nrow: 192 rrow: 192)
leaf: 0x18006a9 25167529 (146: nrow: 186 rrow: 186)
leaf: 0x18006aa 25167530 (147: nrow: 188 rrow: 188)
leaf: 0x18006ab 25167531 (148: nrow: 187 rrow: 187)
leaf: 0x18006ac 25167532 (149: nrow: 187 rrow: 187)
leaf: 0x18006ad 25167533 (150: nrow: 190 rrow: 190)
leaf: 0x18006ae 25167534 (151: nrow: 187 rrow: 187)
leaf: 0x18006af 25167535 (152: nrow: 185 rrow: 185)
leaf: 0x18006b0 25167536 (153: nrow: 184 rrow: 184)
leaf: 0x18006b1 25167537 (154: nrow: 196 rrow: 196)
leaf: 0x18006b2 25167538 (155: nrow: 185 rrow: 185)
leaf: 0x18006b3 25167539 (156: nrow: 189 rrow: 189)
leaf: 0x18006b4 25167540 (157: nrow: 189 rrow: 189)
leaf: 0x18006b5 25167541 (158: nrow: 186 rrow: 186)
leaf: 0x18006b6 25167542 (159: nrow: 184 rrow: 184)
leaf: 0x18006b7 25167543 (160: nrow: 190 rrow: 190)
leaf: 0x18006b8 25167544 (161: nrow: 186 rrow: 186)
leaf: 0x18006b9 25167545 (162: nrow: 188 rrow: 188)
leaf: 0x18006ba 25167546 (163: nrow: 184 rrow: 184)
leaf: 0x18006bb 25167547 (164: nrow: 184 rrow: 184)
leaf: 0x18006bc 25167548 (165: nrow: 183 rrow: 183)
leaf: 0x18006bd 25167549 (166: nrow: 189 rrow: 189)
leaf: 0x18006be 25167550 (167: nrow: 189 rrow: 189)
leaf: 0x18006bf 25167551 (168: nrow: 186 rrow: 186)
leaf: 0x18006c0 25167552 (169: nrow: 187 rrow: 187)
leaf: 0x18006c1 25167553 (170: nrow: 186 rrow: 186)
leaf: 0x18006c2 25167554 (171: nrow: 187 rrow: 187)
leaf: 0x18006c3 25167555 (172: nrow: 183 rrow: 183)
leaf: 0x18006c4 25167556 (173: nrow: 184 rrow: 184)
leaf: 0x18006c5 25167557 (174: nrow: 187 rrow: 187)
leaf: 0x18006c6 25167558 (175: nrow: 187 rrow: 187)
leaf: 0x18006c7 25167559 (176: nrow: 184 rrow: 184)
leaf: 0x18006c8 25167560 (177: nrow: 188 rrow: 188)
leaf: 0x18006c9 25167561 (178: nrow: 188 rrow: 188)
leaf: 0x18006ca 25167562 (179: nrow: 188 rrow: 188)
leaf: 0x18006cb 25167563 (180: nrow: 183 rrow: 183)
leaf: 0x18006cc 25167564 (181: nrow: 181 rrow: 181)
leaf: 0x18006cd 25167565 (182: nrow: 184 rrow: 184)
leaf: 0x18006ce 25167566 (183: nrow: 178 rrow: 178)
leaf: 0x18006cf 25167567 (184: nrow: 188 rrow: 188)
leaf: 0x18006d0 25167568 (185: nrow: 189 rrow: 189)
leaf: 0x18006d1 25167569 (186: nrow: 185 rrow: 185)
leaf: 0x18006d2 25167570 (187: nrow: 187 rrow: 187)
leaf: 0x18006d3 25167571 (188: nrow: 192 rrow: 192)
leaf: 0x18006d4 25167572 (189: nrow: 188 rrow: 188)
leaf: 0x18006d5 25167573 (190: nrow: 189 rrow: 189)
leaf: 0x18006d6 25167574 (191: nrow: 190 rrow: 190)
leaf: 0x18006d7 25167575 (192: nrow: 187 rrow: 187)
leaf: 0x18006d8 25167576 (193: nrow: 178 rrow: 178)
leaf: 0x18006d9 25167577 (194: nrow: 189 rrow: 189)
leaf: 0x18006da 25167578 (195: nrow: 179 rrow: 179)
leaf: 0x18006db 25167579 (196: nrow: 182 rrow: 182)
leaf: 0x18006dc 25167580 (197: nrow: 186 rrow: 186)
leaf: 0x18006dd 25167581 (198: nrow: 186 rrow: 186)
leaf: 0x18006de 25167582 (199: nrow: 191 rrow: 191)
leaf: 0x18006df 25167583 (200: nrow: 184 rrow: 184)
leaf: 0x18006e0 25167584 (201: nrow: 184 rrow: 184)
leaf: 0x18006e1 25167585 (202: nrow: 183 rrow: 183)
leaf: 0x18006e2 25167586 (203: nrow: 188 rrow: 188)
leaf: 0x18006e3 25167587 (204: nrow: 188 rrow: 188)
leaf: 0x18006e4 25167588 (205: nrow: 189 rrow: 189)
leaf: 0x18006e5 25167589 (206: nrow: 189 rrow: 189)
leaf: 0x18006e6 25167590 (207: nrow: 193 rrow: 193)
leaf: 0x18006e7 25167591 (208: nrow: 183 rrow: 183)
leaf: 0x18006e8 25167592 (209: nrow: 185 rrow: 185)
leaf: 0x18006e9 25167593 (210: nrow: 184 rrow: 184)
leaf: 0x18006ea 25167594 (211: nrow: 186 rrow: 186)
leaf: 0x18006eb 25167595 (212: nrow: 184 rrow: 184)
leaf: 0x18006ec 25167596 (213: nrow: 187 rrow: 187)
leaf: 0x18006ed 25167597 (214: nrow: 188 rrow: 188)
leaf: 0x18006ee 25167598 (215: nrow: 186 rrow: 186)
leaf: 0x18006ef 25167599 (216: nrow: 193 rrow: 193)
leaf: 0x18006f0 25167600 (217: nrow: 185 rrow: 185)
leaf: 0x18006f1 25167601 (218: nrow: 184 rrow: 184)
leaf: 0x18006f2 25167602 (219: nrow: 189 rrow: 189)
leaf: 0x18006f3 25167603 (220: nrow: 184 rrow: 184)
leaf: 0x18006f4 25167604 (221: nrow: 186 rrow: 186)
leaf: 0x18006f5 25167605 (222: nrow: 187 rrow: 187)
leaf: 0x18006f6 25167606 (223: nrow: 188 rrow: 188)
leaf: 0x18006f7 25167607 (224: nrow: 182 rrow: 182)
leaf: 0x18006f8 25167608 (225: nrow: 183 rrow: 183)
leaf: 0x18006f9 25167609 (226: nrow: 186 rrow: 186)
leaf: 0x18006fa 25167610 (227: nrow: 181 rrow: 181)
leaf: 0x18006fb 25167611 (228: nrow: 184 rrow: 184)
leaf: 0x18006fc 25167612 (229: nrow: 185 rrow: 185)
leaf: 0x18006fd 25167613 (230: nrow: 187 rrow: 187)
leaf: 0x18006fe 25167614 (231: nrow: 183 rrow: 183)
leaf: 0x18006ff 25167615 (232: nrow: 182 rrow: 182)
leaf: 0x1800700 25167616 (233: nrow: 187 rrow: 187)
leaf: 0x1800701 25167617 (234: nrow: 184 rrow: 184)
leaf: 0x1800702 25167618 (235: nrow: 186 rrow: 186)
leaf: 0x1800703 25167619 (236: nrow: 187 rrow: 187)
leaf: 0x1800704 25167620 (237: nrow: 189 rrow: 189)
leaf: 0x1800705 25167621 (238: nrow: 186 rrow: 186)
leaf: 0x1800706 25167622 (239: nrow: 181 rrow: 181)
leaf: 0x1800707 25167623 (240: nrow: 186 rrow: 186)
leaf: 0x1800708 25167624 (241: nrow: 190 rrow: 190)
leaf: 0x180070b 25167627 (242: nrow: 190 rrow: 190)
leaf: 0x180070c 25167628 (243: nrow: 222 rrow: 222)
leaf: 0x180070d 25167629 (244: nrow: 200 rrow: 200)
leaf: 0x180070e 25167630 (245: nrow: 230 rrow: 230)
branch: 0x180080c 25167884 (0: nrow: 248, level: 1)
leaf: 0x180070f 25167631 (-1: nrow: 229 rrow: 229)
leaf: 0x1800711 25167633 (0: nrow: 222 rrow: 222)
leaf: 0x1800712 25167634 (1: nrow: 226 rrow: 226)
leaf: 0x1800713 25167635 (2: nrow: 221 rrow: 221)
leaf: 0x1800714 25167636 (3: nrow: 221 rrow: 221)
leaf: 0x1800715 25167637 (4: nrow: 249 rrow: 249)
leaf: 0x1800716 25167638 (5: nrow: 233 rrow: 233)
leaf: 0x1800717 25167639 (6: nrow: 212 rrow: 212)
leaf: 0x1800718 25167640 (7: nrow: 256 rrow: 256)
leaf: 0x1800719 25167641 (8: nrow: 274 rrow: 274)
leaf: 0x180071a 25167642 (9: nrow: 201 rrow: 201)
leaf: 0x180071b 25167643 (10: nrow: 199 rrow: 199)
leaf: 0x180071c 25167644 (11: nrow: 199 rrow: 199)
leaf: 0x180071d 25167645 (12: nrow: 249 rrow: 249)
leaf: 0x180071e 25167646 (13: nrow: 286 rrow: 286)
leaf: 0x180071f 25167647 (14: nrow: 239 rrow: 239)
leaf: 0x1800720 25167648 (15: nrow: 236 rrow: 236)
leaf: 0x1800721 25167649 (16: nrow: 222 rrow: 222)
leaf: 0x1800722 25167650 (17: nrow: 213 rrow: 213)
leaf: 0x1800723 25167651 (18: nrow: 227 rrow: 227)
leaf: 0x1800724 25167652 (19: nrow: 204 rrow: 204)
leaf: 0x1800725 25167653 (20: nrow: 226 rrow: 226)
leaf: 0x1800726 25167654 (21: nrow: 231 rrow: 231)
leaf: 0x1800727 25167655 (22: nrow: 211 rrow: 211)
leaf: 0x1800728 25167656 (23: nrow: 228 rrow: 228)
leaf: 0x1800729 25167657 (24: nrow: 235 rrow: 235)
leaf: 0x180072a 25167658 (25: nrow: 222 rrow: 222)
leaf: 0x180072b 25167659 (26: nrow: 224 rrow: 224)
leaf: 0x180072c 25167660 (27: nrow: 227 rrow: 227)
leaf: 0x180072d 25167661 (28: nrow: 222 rrow: 222)
leaf: 0x180072e 25167662 (29: nrow: 260 rrow: 260)
leaf: 0x180072f 25167663 (30: nrow: 239 rrow: 239)
leaf: 0x1800730 25167664 (31: nrow: 244 rrow: 244)
leaf: 0x1800731 25167665 (32: nrow: 242 rrow: 242)
leaf: 0x1800732 25167666 (33: nrow: 256 rrow: 256)
leaf: 0x1800733 25167667 (34: nrow: 259 rrow: 259)
leaf: 0x1800734 25167668 (35: nrow: 242 rrow: 242)
leaf: 0x1800735 25167669 (36: nrow: 233 rrow: 233)
leaf: 0x1800736 25167670 (37: nrow: 249 rrow: 249)
leaf: 0x1800737 25167671 (38: nrow: 255 rrow: 255)
leaf: 0x1800738 25167672 (39: nrow: 286 rrow: 286)
leaf: 0x1800739 25167673 (40: nrow: 311 rrow: 311)
leaf: 0x180073a 25167674 (41: nrow: 277 rrow: 277)
leaf: 0x180073b 25167675 (42: nrow: 338 rrow: 338)
leaf: 0x180073c 25167676 (43: nrow: 277 rrow: 277)
leaf: 0x180073d 25167677 (44: nrow: 266 rrow: 266)
leaf: 0x180073e 25167678 (45: nrow: 336 rrow: 336)
leaf: 0x180073f 25167679 (46: nrow: 358 rrow: 358)
leaf: 0x1800740 25167680 (47: nrow: 286 rrow: 286)
leaf: 0x1800741 25167681 (48: nrow: 212 rrow: 212)
leaf: 0x1800742 25167682 (49: nrow: 199 rrow: 199)
leaf: 0x1800743 25167683 (50: nrow: 258 rrow: 258)
leaf: 0x1800744 25167684 (51: nrow: 246 rrow: 246)
leaf: 0x1800745 25167685 (52: nrow: 245 rrow: 245)
leaf: 0x1800746 25167686 (53: nrow: 249 rrow: 249)
leaf: 0x1800747 25167687 (54: nrow: 239 rrow: 239)
leaf: 0x1800748 25167688 (55: nrow: 236 rrow: 236)
leaf: 0x1800749 25167689 (56: nrow: 295 rrow: 295)
leaf: 0x180074a 25167690 (57: nrow: 238 rrow: 238)
leaf: 0x180074b 25167691 (58: nrow: 234 rrow: 234)
leaf: 0x180074c 25167692 (59: nrow: 283 rrow: 283)
leaf: 0x180074d 25167693 (60: nrow: 262 rrow: 262)
leaf: 0x180074e 25167694 (61: nrow: 266 rrow: 266)
leaf: 0x180074f 25167695 (62: nrow: 267 rrow: 267)
leaf: 0x1800750 25167696 (63: nrow: 242 rrow: 242)
leaf: 0x1800751 25167697 (64: nrow: 244 rrow: 244)
leaf: 0x1800752 25167698 (65: nrow: 252 rrow: 252)
leaf: 0x1800753 25167699 (66: nrow: 238 rrow: 238)
leaf: 0x1800754 25167700 (67: nrow: 241 rrow: 241)
leaf: 0x1800755 25167701 (68: nrow: 255 rrow: 255)
leaf: 0x1800756 25167702 (69: nrow: 269 rrow: 269)
leaf: 0x1800757 25167703 (70: nrow: 260 rrow: 260)
leaf: 0x1800758 25167704 (71: nrow: 262 rrow: 262)
leaf: 0x1800759 25167705 (72: nrow: 218 rrow: 218)
leaf: 0x180075a 25167706 (73: nrow: 253 rrow: 253)
leaf: 0x180075b 25167707 (74: nrow: 259 rrow: 259)
leaf: 0x180075c 25167708 (75: nrow: 222 rrow: 222)
leaf: 0x180075d 25167709 (76: nrow: 227 rrow: 227)
leaf: 0x180075e 25167710 (77: nrow: 209 rrow: 209)
leaf: 0x180075f 25167711 (78: nrow: 216 rrow: 216)
leaf: 0x1800760 25167712 (79: nrow: 219 rrow: 219)
leaf: 0x1800761 25167713 (80: nrow: 220 rrow: 220)
leaf: 0x1800762 25167714 (81: nrow: 219 rrow: 219)
leaf: 0x1800763 25167715 (82: nrow: 222 rrow: 222)
leaf: 0x1800764 25167716 (83: nrow: 223 rrow: 223)
leaf: 0x1800765 25167717 (84: nrow: 206 rrow: 206)
leaf: 0x1800766 25167718 (85: nrow: 255 rrow: 255)
leaf: 0x1800767 25167719 (86: nrow: 254 rrow: 254)
leaf: 0x1800768 25167720 (87: nrow: 248 rrow: 248)
leaf: 0x1800769 25167721 (88: nrow: 246 rrow: 246)
leaf: 0x180076a 25167722 (89: nrow: 295 rrow: 295)
leaf: 0x180076b 25167723 (90: nrow: 268 rrow: 268)
leaf: 0x180076c 25167724 (91: nrow: 281 rrow: 281)
leaf: 0x180076d 25167725 (92: nrow: 239 rrow: 239)
leaf: 0x180076e 25167726 (93: nrow: 209 rrow: 209)
leaf: 0x180076f 25167727 (94: nrow: 306 rrow: 306)
leaf: 0x1800770 25167728 (95: nrow: 269 rrow: 269)
leaf: 0x1800771 25167729 (96: nrow: 240 rrow: 240)
leaf: 0x1800772 25167730 (97: nrow: 254 rrow: 254)
leaf: 0x1800773 25167731 (98: nrow: 266 rrow: 266)
leaf: 0x1800774 25167732 (99: nrow: 259 rrow: 259)
leaf: 0x1800775 25167733 (100: nrow: 269 rrow: 269)
leaf: 0x1800776 25167734 (101: nrow: 249 rrow: 249)
leaf: 0x1800777 25167735 (102: nrow: 215 rrow: 215)
leaf: 0x1800778 25167736 (103: nrow: 230 rrow: 230)
leaf: 0x1800779 25167737 (104: nrow: 252 rrow: 252)
leaf: 0x180077a 25167738 (105: nrow: 296 rrow: 296)
leaf: 0x180077b 25167739 (106: nrow: 226 rrow: 226)
leaf: 0x180077c 25167740 (107: nrow: 194 rrow: 194)
leaf: 0x180077d 25167741 (108: nrow: 194 rrow: 194)
leaf: 0x180077e 25167742 (109: nrow: 194 rrow: 194)
leaf: 0x180077f 25167743 (110: nrow: 194 rrow: 194)
leaf: 0x1800780 25167744 (111: nrow: 194 rrow: 194)
leaf: 0x1800781 25167745 (112: nrow: 217 rrow: 217)
leaf: 0x1800782 25167746 (113: nrow: 231 rrow: 231)
leaf: 0x1800783 25167747 (114: nrow: 237 rrow: 237)
leaf: 0x1800784 25167748 (115: nrow: 276 rrow: 276)
leaf: 0x1800785 25167749 (116: nrow: 232 rrow: 232)
leaf: 0x1800786 25167750 (117: nrow: 220 rrow: 220)
leaf: 0x1800787 25167751 (118: nrow: 239 rrow: 239)
leaf: 0x1800788 25167752 (119: nrow: 222 rrow: 222)
leaf: 0x180078b 25167755 (120: nrow: 220 rrow: 220)
leaf: 0x180078c 25167756 (121: nrow: 238 rrow: 238)
leaf: 0x180078d 25167757 (122: nrow: 286 rrow: 286)
leaf: 0x180078e 25167758 (123: nrow: 250 rrow: 250)
leaf: 0x180078f 25167759 (124: nrow: 256 rrow: 256)
leaf: 0x1800790 25167760 (125: nrow: 261 rrow: 261)
leaf: 0x1800791 25167761 (126: nrow: 249 rrow: 249)
leaf: 0x1800792 25167762 (127: nrow: 246 rrow: 246)
leaf: 0x1800793 25167763 (128: nrow: 243 rrow: 243)
leaf: 0x1800794 25167764 (129: nrow: 227 rrow: 227)
leaf: 0x1800795 25167765 (130: nrow: 216 rrow: 216)
leaf: 0x1800796 25167766 (131: nrow: 235 rrow: 235)
leaf: 0x1800797 25167767 (132: nrow: 229 rrow: 229)
leaf: 0x1800798 25167768 (133: nrow: 230 rrow: 230)
leaf: 0x1800799 25167769 (134: nrow: 224 rrow: 224)
leaf: 0x180079a 25167770 (135: nrow: 219 rrow: 219)
leaf: 0x180079b 25167771 (136: nrow: 268 rrow: 268)
leaf: 0x180079c 25167772 (137: nrow: 242 rrow: 242)
leaf: 0x180079d 25167773 (138: nrow: 203 rrow: 203)
leaf: 0x180079e 25167774 (139: nrow: 200 rrow: 200)
leaf: 0x180079f 25167775 (140: nrow: 184 rrow: 184)
leaf: 0x18007a0 25167776 (141: nrow: 177 rrow: 177)
leaf: 0x18007a1 25167777 (142: nrow: 182 rrow: 182)
leaf: 0x18007a2 25167778 (143: nrow: 214 rrow: 214)
leaf: 0x18007a3 25167779 (144: nrow: 217 rrow: 217)
leaf: 0x18007a4 25167780 (145: nrow: 222 rrow: 222)
leaf: 0x18007a5 25167781 (146: nrow: 189 rrow: 189)
leaf: 0x18007a6 25167782 (147: nrow: 190 rrow: 190)
leaf: 0x18007a7 25167783 (148: nrow: 185 rrow: 185)
leaf: 0x18007a8 25167784 (149: nrow: 197 rrow: 197)
leaf: 0x18007a9 25167785 (150: nrow: 203 rrow: 203)
leaf: 0x18007aa 25167786 (151: nrow: 197 rrow: 197)
leaf: 0x18007ab 25167787 (152: nrow: 210 rrow: 210)
leaf: 0x18007ac 25167788 (153: nrow: 211 rrow: 211)
leaf: 0x18007ad 25167789 (154: nrow: 195 rrow: 195)
leaf: 0x18007ae 25167790 (155: nrow: 209 rrow: 209)
leaf: 0x18007af 25167791 (156: nrow: 192 rrow: 192)
leaf: 0x18007b0 25167792 (157: nrow: 192 rrow: 192)
leaf: 0x18007b1 25167793 (158: nrow: 193 rrow: 193)
leaf: 0x18007b2 25167794 (159: nrow: 208 rrow: 208)
leaf: 0x18007b3 25167795 (160: nrow: 200 rrow: 200)
leaf: 0x18007b4 25167796 (161: nrow: 206 rrow: 206)
leaf: 0x18007b5 25167797 (162: nrow: 216 rrow: 216)
leaf: 0x18007b6 25167798 (163: nrow: 186 rrow: 186)
leaf: 0x18007b7 25167799 (164: nrow: 183 rrow: 183)
leaf: 0x18007b8 25167800 (165: nrow: 202 rrow: 202)
leaf: 0x18007b9 25167801 (166: nrow: 200 rrow: 200)
leaf: 0x18007ba 25167802 (167: nrow: 199 rrow: 199)
leaf: 0x18007bb 25167803 (168: nrow: 184 rrow: 184)
leaf: 0x18007bc 25167804 (169: nrow: 188 rrow: 188)
leaf: 0x18007bd 25167805 (170: nrow: 188 rrow: 188)
leaf: 0x18007be 25167806 (171: nrow: 182 rrow: 182)
leaf: 0x18007bf 25167807 (172: nrow: 192 rrow: 192)
leaf: 0x18007c0 25167808 (173: nrow: 198 rrow: 198)
leaf: 0x18007c1 25167809 (174: nrow: 210 rrow: 210)
leaf: 0x18007c2 25167810 (175: nrow: 195 rrow: 195)
leaf: 0x18007c3 25167811 (176: nrow: 186 rrow: 186)
leaf: 0x18007c4 25167812 (177: nrow: 183 rrow: 183)
leaf: 0x18007c5 25167813 (178: nrow: 183 rrow: 183)
leaf: 0x18007c6 25167814 (179: nrow: 209 rrow: 209)
leaf: 0x18007c7 25167815 (180: nrow: 197 rrow: 197)
leaf: 0x18007c8 25167816 (181: nrow: 201 rrow: 201)
leaf: 0x18007c9 25167817 (182: nrow: 184 rrow: 184)
leaf: 0x18007ca 25167818 (183: nrow: 184 rrow: 184)
leaf: 0x18007cb 25167819 (184: nrow: 184 rrow: 184)
leaf: 0x18007cc 25167820 (185: nrow: 180 rrow: 180)
leaf: 0x18007cd 25167821 (186: nrow: 178 rrow: 178)
leaf: 0x18007ce 25167822 (187: nrow: 179 rrow: 179)
leaf: 0x18007cf 25167823 (188: nrow: 179 rrow: 179)
leaf: 0x18007d0 25167824 (189: nrow: 179 rrow: 179)
leaf: 0x18007d1 25167825 (190: nrow: 180 rrow: 180)
leaf: 0x18007d2 25167826 (191: nrow: 183 rrow: 183)
leaf: 0x18007d3 25167827 (192: nrow: 178 rrow: 178)
leaf: 0x18007d4 25167828 (193: nrow: 179 rrow: 179)
leaf: 0x18007d5 25167829 (194: nrow: 186 rrow: 186)
leaf: 0x18007d6 25167830 (195: nrow: 191 rrow: 191)
leaf: 0x18007d7 25167831 (196: nrow: 203 rrow: 203)
leaf: 0x18007d8 25167832 (197: nrow: 190 rrow: 190)
leaf: 0x18007d9 25167833 (198: nrow: 186 rrow: 186)
leaf: 0x18007da 25167834 (199: nrow: 185 rrow: 185)
leaf: 0x18007db 25167835 (200: nrow: 183 rrow: 183)
leaf: 0x18007dc 25167836 (201: nrow: 198 rrow: 198)
leaf: 0x18007dd 25167837 (202: nrow: 208 rrow: 208)
leaf: 0x18007de 25167838 (203: nrow: 184 rrow: 184)
leaf: 0x18007df 25167839 (204: nrow: 183 rrow: 183)
leaf: 0x18007e0 25167840 (205: nrow: 186 rrow: 186)
leaf: 0x18007e1 25167841 (206: nrow: 189 rrow: 189)
leaf: 0x18007e2 25167842 (207: nrow: 181 rrow: 181)
leaf: 0x18007e3 25167843 (208: nrow: 181 rrow: 181)
leaf: 0x18007e4 25167844 (209: nrow: 193 rrow: 193)
leaf: 0x18007e5 25167845 (210: nrow: 197 rrow: 197)
leaf: 0x18007e6 25167846 (211: nrow: 186 rrow: 186)
leaf: 0x18007e7 25167847 (212: nrow: 189 rrow: 189)
leaf: 0x18007e8 25167848 (213: nrow: 201 rrow: 201)
leaf: 0x18007e9 25167849 (214: nrow: 201 rrow: 201)
leaf: 0x18007ea 25167850 (215: nrow: 183 rrow: 183)
leaf: 0x18007eb 25167851 (216: nrow: 196 rrow: 196)
leaf: 0x18007ec 25167852 (217: nrow: 178 rrow: 178)
leaf: 0x18007ed 25167853 (218: nrow: 183 rrow: 183)
leaf: 0x18007ee 25167854 (219: nrow: 180 rrow: 180)
leaf: 0x18007ef 25167855 (220: nrow: 196 rrow: 196)
leaf: 0x18007f0 25167856 (221: nrow: 197 rrow: 197)
leaf: 0x18007f1 25167857 (222: nrow: 197 rrow: 197)
leaf: 0x18007f2 25167858 (223: nrow: 194 rrow: 194)
leaf: 0x18007f3 25167859 (224: nrow: 190 rrow: 190)
leaf: 0x18007f4 25167860 (225: nrow: 193 rrow: 193)
leaf: 0x18007f5 25167861 (226: nrow: 207 rrow: 207)
leaf: 0x18007f6 25167862 (227: nrow: 202 rrow: 202)
leaf: 0x18007f7 25167863 (228: nrow: 194 rrow: 194)
leaf: 0x18007f8 25167864 (229: nrow: 207 rrow: 207)
leaf: 0x18007f9 25167865 (230: nrow: 204 rrow: 204)
leaf: 0x18007fa 25167866 (231: nrow: 194 rrow: 194)
leaf: 0x18007fb 25167867 (232: nrow: 185 rrow: 185)
leaf: 0x18007fc 25167868 (233: nrow: 208 rrow: 208)
leaf: 0x18007fd 25167869 (234: nrow: 218 rrow: 218)
leaf: 0x18007fe 25167870 (235: nrow: 194 rrow: 194)
leaf: 0x18007ff 25167871 (236: nrow: 192 rrow: 192)
leaf: 0x1800800 25167872 (237: nrow: 200 rrow: 200)
leaf: 0x1800801 25167873 (238: nrow: 191 rrow: 191)
leaf: 0x1800802 25167874 (239: nrow: 186 rrow: 186)
leaf: 0x1800803 25167875 (240: nrow: 181 rrow: 181)
leaf: 0x1800804 25167876 (241: nrow: 182 rrow: 182)
leaf: 0x1800805 25167877 (242: nrow: 182 rrow: 182)
leaf: 0x1800806 25167878 (243: nrow: 190 rrow: 190)
leaf: 0x1800807 25167879 (244: nrow: 191 rrow: 191)
leaf: 0x1800808 25167880 (245: nrow: 180 rrow: 180)
leaf: 0x180080b 25167883 (246: nrow: 55 rrow: 55)
----- end tree dump
------------------
解釋 treedump 輸出
------------------
branch 表示的是 branch block ，它後面跟了一個十六進位制表示的DBA(data block address)，以及用10進製表示的DBA
DBA 之後表示在同一層次的相對位置(root 從0開始,branch 以及leaf從 -1開始)
nrow 表示塊中包含了多少條目(包括delete的條目)
rrow 表示塊中包含的實際條目(不包括delete的條目)
level 表示從該block到leaf的深度(leaf沒有 level)
branch: 0x1800014 25165844 (0: nrow: 2, level: 2)
這個 branch block 的 level 為2，也就是說 從這個branch block 到 leaf block 的深度為2,根據前面的查詢，這個索引的Blevel為2,所以這個
branch其實是 root block. 其實根據 nrow:2 也可以看出來它是root block，因為nrow:2 說明它只包含了2個條目，那麼這2個條目其實就是dump
檔案中的其他2個 branch block 的條目
現在我來驗證一下 branch: 0x1800014 25165844 (0: nrow: 2, level: 2) 是不是 root block , 我查詢這個 branch 的 DBA
SQL> select dbms_utility.data_block_address_file('25165844') FILE_ID,
2 dbms_utility.data_block_address_block('25165844') BLOCK_ID from dual;
FILE_ID BLOCK_ID
---------- ----------
6 20
Btree 索引的 root block總是segment header+1,所以我查詢該索引的段頭
SQL> select header_file,header_block from dba_segments where segment_name='I_TEST_OBJECT_NAME';
HEADER_FILE HEADER_BLOCK
----------- ------------
6 19
那麼現在已經證明了 branch: 0x1800014 25165844 (0: nrow: 2, level: 2) 是root block, 其實 treedump第一個 branch block 就是 root block
branch: 0x1800710 25167632 (-1: nrow: 247, level: 1)
這個branch 的 DBA為0x1800710(十六進位制)，25167632(十進位制) -1 表示它是與它在同一個深度的 branch block中的第一個 branch block
nrow: 247表示它有247個 leaf block. level: 1 表示 這個 branch block 到 leaf block的深度為1
leaf: 0x1800015 25165845 (-1: nrow: 182 rrow: 182)
leaf 表示它是一個 leaf block 0x1800015 25165845 分別是 這個 leaf block 的 十六進位制/十進位制的 DBA -1 表示它是 leaf block 中的第一個 block
nrow: 182 表示它一共有182條記錄 rrow: 182 表示它實際有182條記錄
-----------------
branch block dump
-----------------
這裡選擇dump branch: 0x1800710 25167632 (-1: nrow: 247, level: 1)
SQL> select dbms_utility.data_block_address_file('25167632') FILE_ID,
2 dbms_utility.data_block_address_block('25167632') BLOCK_ID from dual;
FILE_ID BLOCK_ID
---------- ----------
6 1808
SQL> alter system dump datafile 6 block 1808;
System altered.
部分 DUMP 檔案
Block header dump: 0x01800710
Object id on Block? Y
seg/obj: 0xd78a csc: 0x00.17bbef0 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1800709 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.017bbef0
Branch block dump
=================
header address 156329548=0x951664c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 246
kdxcofbo 520=0x208
kdxcofeo 549=0x225
kdxcoavs 29
kdxbrlmc 25165845=0x1800015
kdxbrsno 0
kdxbrbksz 8060
kdxbr2urrc 15
row#0[8027] dba: 25165846=0x1800016
col 0; len 24; (24):
2f 31 30 64 65 32 32 63 36 5f 43 6c 61 73 73 54 79 70 65 49 6d 70 6c 32
col 1; len 3; (3): 01 80 03
row#1[7988] dba: 25165847=0x1800017
col 0; len 30; (30):
2f 31 31 64 35 30 39 31 32 5f 44 61 74 65 46 6f 72 6d 61 74 5a 6f 6e 65 44
61 74 61 5f 7a
col 1; len 3; (3): 01 80 04
row#2[7977] dba: 25165848=0x1800018
col 0; len 5; (5): 2f 31 32 61 32
col 1; TERM
..................................省略..................................
row#245[549] dba: 25167630=0x180070e
col 0; len 10; (10): 41 4c 4c 24 4f 4c 41 50 5f 44
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 7 file#: 6 minblk 1808 maxblk 1808
----------------
解釋部分dump輸出
----------------
kdxcolev 1 --該block到leaf block的深度(leaf block 為0).這裡branch block 的level 為1 與前面查詢相吻合
KDXCOLEV Flags = - - -
kdxcolok 0 --表示是否有事務lock了這個branch block，如果有 有多少事務
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2 --索引值條目. 這裡表示有2個條目
kdxcosdc 0 --這個block的結構被更改次數.這裡0表示沒有更改
kdxconro 246 --索引條目(不包含kdxbrlmc 指標)
kdxcofbo 520=0x208 --空閒空間的起始偏移量
kdxcofeo 549=0x225 --空閒空間的末尾偏移量
kdxcoavs 29 --block中的空閒空間=kdxcofeo-kdxcofbo
kdxbrlmc 25165845=0x1800015 --如果index value小於row#0，指向該 block 的地址
kdxbrsno 0 --最後被更改的索引條目
kdxbrbksz 8060 --塊中的可用空間
kdxbr2urrc 15
row#2[7977] dba: 25165848=0x1800018 --row# 表示索引條目數，從0開始，緊接著就是十進位制和十六進位制的DBA，該DBA指向 leaf block
col 0; len 5; (5): 2f 31 32 61 32 --列的行號，從0開始，緊接著的就是列的長度以及列的值，那麼這個值稱之為separator key，
這個separator key 可以區分真實的索引值，所以從這裡我們也知道 branch block不會儲存完整的索引值，只要能區分就行
col 1; TERM
---------------
leaf block DUMP
---------------
這裡選擇 dump row#2[7977] dba: 25165848=0x1800018 ，因為它包含的索引鍵很少
dump 之前先看一下 row#2[7977] 儲存了什麼索引鍵
2f 表示 /
31 表示 1
32 表示 2
61 表示 a
具體轉換過程如下
SQL> declare n varchar2(2000);
2 begin
3 dbms_stats.convert_raw_value('2f',n);
4 dbms_output.put_line(n);
5 end;
6 /
/
PL/SQL procedure successfully completed.
SQL> select dbms_utility.data_block_address_file('25165848') FILE_ID,
2 dbms_utility.data_block_address_block('25165848') BLOCK_ID from dual;
FILE_ID BLOCK_ID
---------- ----------
6 24
SQL> alter system dump datafile 6 block 24;
System altered.
部分DUMP檔案
Block header dump: 0x01800018
Object id on Block? Y
seg/obj: 0xd78a csc: 0x00.17bbef0 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.017bbef0
Leaf block dump
===============
header address 146564708=0x8bc6664
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 189
kdxcofbo 414=0x19e
kdxcofeo 1248=0x4e0
kdxcoavs 834
kdxlespl 0
kdxlende 0
kdxlenxt 25165849=0x1800019
kdxleprv 25165847=0x1800017
kdxledsz 0
kdxlebksz 8036
row#0[7996] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 00 e2 00 16
row#1[7956] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 00 e2 00 17
row#2[7916] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 03 b5 00 25
row#3[7876] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 03 b5 00 26
row#4[7836] flag: ------, lock: 0, len=40
..................................省略..................................
row#188[1248] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 33 38 35 32 32 37 66 5f 4d 61 70 52 65 67 69 6f 6e 43 6f 6e 74 61 69
6e 6d 65 6e 74
col 1; len 6; (6): 01 80 01 6a 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 7 file#: 6 minblk 24 maxblk 24
----------------
解釋部分dump輸出
----------------
kdxcolev 0 --該block到leaf block的深度(leaf block 為0).
KDXCOLEV Flags = - - -
kdxcolok 0 --表示是否有事務lock了這個branch block，如果有 有多少事務
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2 --索引值條目. 這裡表示有2個條目
kdxcosdc 0 --這個block的結構被更改次數.這裡0表示沒有更改
kdxconro 189 --索引條目 ,這裡有189個索引條目 它等於 row#188-row#0+1
kdxcofbo 414=0x19e --空閒空間的起始偏移量
kdxcofeo 1248=0x4e0 --空閒空間的末尾偏移量
kdxcoavs 834 --block中的空閒空間=kdxcofeo-kdxcofbo
kdxlespl 0 --block split的時候沒有commit的記錄的大小(byte)
kdxlende 0 --被刪除的條目
kdxlenxt 25165849=0x1800019 --指向下一個 leaf block的指標(DBA)
kdxleprv 25165847=0x1800017 --指向上一個 leaf block的指標(DBA)
kdxledsz 0 --被刪除的空間
kdxlebksz 8036 --可用空間
row#0[7996] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 00 e2 00 16
row# 表示索引值的條目，從0開始 lock: 0 表示ITL中的鎖資訊 0表示沒有被鎖 len=40 表示索引值長度
col 表示列號，從0開始 那麼接下來就是索引的鍵值 以及 rowid中後12位值
下面我們來看一下row#0儲存的值是什麼
SQL> declare n varchar2(2000);
2 begin
3 dbms_stats.convert_raw_value('2f31326132373365365f496e7465726e6174696f6e616c466f726d617474',n);
4 dbms_output.put_line(n);
5 end;
6 /
/12a273e6_InternationalFormatt
PL/SQL procedure successfully completed.
ROWID一共用18位表示
最前6位表示 data object number
之後後3位表示 datafile number
之後後6位表示 datablock number
最後3位表示 row number
所以這裡的rowid其實只是除去了 data object number的一部分而已
SQL> select to_number('18000e2','xxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('18000E2','XXXXXXXXX
------------------------------
25166050
SQL> select dbms_utility.data_block_address_file('25166050') FILE_ID,dbms_utility.data_block_address_block('25166050') BLOCK_ID from dual;
FILE_ID BLOCK_ID
---------- ----------
6 226
SQL> select dbms_rowid.rowid_relative_fno(rowid)file_id, dbms_rowid.rowid_block_number(rowid)block_id,dbms_rowid.rowid_row_number(rowid) row#
2 from test where object_name='/12a273e6_InternationalFormatt';
FILE_ID BLOCK_ID ROW#
---------- ---------- ----------
6 226 22
6 226 23
6 949 37
6 949 38
根據這個還原的值我來查詢一下
SQL> select owner,object_name,rowid from test where object_name='/12a273e6_InternationalFormatt';
OWNER OBJECT_NAME ROWID
------------------------------ ------------------------------ ------------------
SYS /12a273e6_InternationalFormatt AAANeJAAGAAAADiAAW
PUBLIC /12a273e6_InternationalFormatt AAANeJAAGAAAADiAAX
SYS /12a273e6_InternationalFormatt AAANeJAAGAAAAO1AAl
PUBLIC /12a273e6_InternationalFormatt AAANeJAAGAAAAO1AAm
SQL> select owner,object_name,dump(rowid,16) from test where object_name='/12a273e6_InternationalFormatt';
OWNER OBJECT_NAME DUMP(ROWID,16)
---------- ----------------------------------- --------------------------------------------------------------------------------
SYS /12a273e6_InternationalFormatt Typ=69 Len=10: 0,0,d7,89,1,80,0,e2,0,16
PUBLIC /12a273e6_InternationalFormatt Typ=69 Len=10: 0,0,d7,89,1,80,0,e2,0,17
SYS /12a273e6_InternationalFormatt Typ=69 Len=10: 0,0,d7,89,1,80,3,b5,0,25
PUBLIC /12a273e6_InternationalFormatt Typ=69 Len=10: 0,0,d7,89,1,80,3,b5,0,26
回憶一下 branch block
row#2[7977] dba: 25165848=0x1800018
col 0; len 5; (5): 2f 31 32 61 32 -------它正是真正的索引鍵的字首
col 1; TERM
row#0[7996] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 00 e2 00 16
之所以Oracle在 Branch block中只記錄 索引鍵值的字首，而不是所有值，是因為這樣可以節約空間，從而能夠儲存更多的 索引條目
同時，我們也能理解了為什麼 查詢使用 like '%xxx' 這種方法不會走Btree 索引，因為Branch block 儲存的是字首.
Btree Index 原理
----------------
1.Oracle中的Btree Index具有3大結構，root節點，branch節點，leaf節點.Root節點始終緊跟索引段頭.
當索引比較小的時候，root節點，branch節點，leaf節點都儲存在同一個block中.Branch節點主要儲存
了索引的鍵值,但是這個鍵值並不是完整的，它只是完整索引值的部分字首.同時Branch節點還儲存了指向
leaf節點的指標(DBA)，另外有個主意的是branch節點中還有個叫kdxbrlmc的指標.Leaf節點主要儲存了完
整的索引鍵值，以及相關索引鍵值的部分rowid(這個rowid去掉了data object number部分)，同時leaf
節點還儲存了2個指標(DBA)，他們分別指向上一個leaf節點以及下一個leaf節點.
2.Btree Index 是始終平衡的，也就是說 從Root節點到 Leaf 節點的任何一個路徑都是等距離的.
3.Btree Index 預設是按照索引值升序排列的，當然了我們可以在建立/重建的時候設定它降序排列.
4.Index Scan 的時候，採用的是 sequential read,並且一次只能讀一個block(INDEX FAST FULL SCAN 除外).
5.Btree Index Update的 時候，先做的是 delete,然後進行insert.
6.Btree Index 不儲存 Null值，但是如果組合索引其中一列是非Null的，那麼組合索引也會儲存Null值.
---------------------------------
Btree Index 儲存原理
---------------------------------
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
SQL> show user
User is "robinson"
SQL> create table test as select * from dba_objects;
Table created
SQL> select count(*) from test;
COUNT(*)
----------
50352
SQL> insert into test select * from dba_objects;
50352 rows inserted
SQL> commit;
Commit complete
SQL> select count(*) from test;
COUNT(*)
----------
100704
SQL> create index i_test_object_name on test(object_name);
Index created
------------------------------------------------------------------------------
我們現在來看一下索引的Blevel,什麼是Blevel,Blevel可以通過DBA_INDEXES.Blevel獲得
------------------------------------------------------------------------------
B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.
從 root block 到 leaf block的深度. 如果root block 和 leaf block在同一個塊中 那麼 Blevel=0
SQL> select index_name,blevel from dba_indexes where index_name='I_TEST_OBJECT_NAME';
INDEX_NAME BLEVEL
------------------------------ ----------
I_TEST_OBJECT_NAME 2
-----------------------------------------------------------------------------------
我們現在來看一下索引的height，索引的高度等於Blevel+1，height可以通過INDEX_STATS獲得
-----------------------------------------------------------------------------------
SQL> analyze index I_TEST_OBJECT_NAME validate structure;
Index analyzed
SQL> select name,height from index_stats where name='I_TEST_OBJECT_NAME'; ---查詢index_stats之前需要 analyze ... validate structure，否則無資料
NAME HEIGHT
------------------------------ ----------
I_TEST_OBJECT_NAME 3
--------------------------------------------------------------------------------------------
Oracle 提供了分析 Btree index 結構的命令 treedump，在進行treedump之前需要獲得索引的object_id
--------------------------------------------------------------------------------------------
SQL> select object_id from dba_objects where object_name='I_TEST_OBJECT_NAME' and owner='ROBINSON';
OBJECT_ID
----------
55178
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 55178';
Session altered
------------------------
treedump trace 檔案
------------------------
----- begin tree dump
branch: 0x1800014 25165844 (0: nrow: 2, level: 2)
branch: 0x1800710 25167632 (-1: nrow: 247, level: 1)
leaf: 0x1800015 25165845 (-1: nrow: 182 rrow: 182)
leaf: 0x1800016 25165846 (0: nrow: 182 rrow: 182)
leaf: 0x1800017 25165847 (1: nrow: 186 rrow: 186)
leaf: 0x1800018 25165848 (2: nrow: 189 rrow: 189)
leaf: 0x1800019 25165849 (3: nrow: 186 rrow: 186)
leaf: 0x180001a 25165850 (4: nrow: 190 rrow: 190)
leaf: 0x180001b 25165851 (5: nrow: 185 rrow: 185)
leaf: 0x180001c 25165852 (6: nrow: 179 rrow: 179)
leaf: 0x180001d 25165853 (7: nrow: 187 rrow: 187)
leaf: 0x180001e 25165854 (8: nrow: 181 rrow: 181)
leaf: 0x180001f 25165855 (9: nrow: 186 rrow: 186)
leaf: 0x1800020 25165856 (10: nrow: 189 rrow: 189)
leaf: 0x1800022 25165858 (11: nrow: 187 rrow: 187)
leaf: 0x1800023 25165859 (12: nrow: 180 rrow: 180)
leaf: 0x1800024 25165860 (13: nrow: 182 rrow: 182)
leaf: 0x1800025 25165861 (14: nrow: 186 rrow: 186)
leaf: 0x1800026 25165862 (15: nrow: 188 rrow: 188)
leaf: 0x1800027 25165863 (16: nrow: 187 rrow: 187)
leaf: 0x1800028 25165864 (17: nrow: 185 rrow: 185)
leaf: 0x1800029 25165865 (18: nrow: 187 rrow: 187)
leaf: 0x180002a 25165866 (19: nrow: 185 rrow: 185)
leaf: 0x180002b 25165867 (20: nrow: 184 rrow: 184)
leaf: 0x180002c 25165868 (21: nrow: 188 rrow: 188)
leaf: 0x180002d 25165869 (22: nrow: 189 rrow: 189)
leaf: 0x180002e 25165870 (23: nrow: 184 rrow: 184)
leaf: 0x180002f 25165871 (24: nrow: 192 rrow: 192)
leaf: 0x1800030 25165872 (25: nrow: 187 rrow: 187)
leaf: 0x1800032 25165874 (26: nrow: 181 rrow: 181)
leaf: 0x1800033 25165875 (27: nrow: 182 rrow: 182)
leaf: 0x1800034 25165876 (28: nrow: 192 rrow: 192)
leaf: 0x1800035 25165877 (29: nrow: 186 rrow: 186)
leaf: 0x1800036 25165878 (30: nrow: 189 rrow: 189)
leaf: 0x1800037 25165879 (31: nrow: 181 rrow: 181)
leaf: 0x1800038 25165880 (32: nrow: 184 rrow: 184)
leaf: 0x1800039 25165881 (33: nrow: 181 rrow: 181)
leaf: 0x180003a 25165882 (34: nrow: 189 rrow: 189)
leaf: 0x180003b 25165883 (35: nrow: 184 rrow: 184)
leaf: 0x180003c 25165884 (36: nrow: 184 rrow: 184)
leaf: 0x180003d 25165885 (37: nrow: 183 rrow: 183)
leaf: 0x180003e 25165886 (38: nrow: 185 rrow: 185)
leaf: 0x180003f 25165887 (39: nrow: 185 rrow: 185)
leaf: 0x1800040 25165888 (40: nrow: 189 rrow: 189)
leaf: 0x1800042 25165890 (41: nrow: 187 rrow: 187)
leaf: 0x1800043 25165891 (42: nrow: 187 rrow: 187)
leaf: 0x1800044 25165892 (43: nrow: 186 rrow: 186)
leaf: 0x1800045 25165893 (44: nrow: 186 rrow: 186)
leaf: 0x1800046 25165894 (45: nrow: 183 rrow: 183)
leaf: 0x1800047 25165895 (46: nrow: 188 rrow: 188)
leaf: 0x1800048 25165896 (47: nrow: 185 rrow: 185)
leaf: 0x1800049 25165897 (48: nrow: 187 rrow: 187)
leaf: 0x180004a 25165898 (49: nrow: 186 rrow: 186)
leaf: 0x180004b 25165899 (50: nrow: 188 rrow: 188)
leaf: 0x180004c 25165900 (51: nrow: 180 rrow: 180)
leaf: 0x180004d 25165901 (52: nrow: 185 rrow: 185)
leaf: 0x180004e 25165902 (53: nrow: 186 rrow: 186)
leaf: 0x180004f 25165903 (54: nrow: 186 rrow: 186)
leaf: 0x1800050 25165904 (55: nrow: 184 rrow: 184)
leaf: 0x1800052 25165906 (56: nrow: 188 rrow: 188)
leaf: 0x1800053 25165907 (57: nrow: 184 rrow: 184)
leaf: 0x1800054 25165908 (58: nrow: 187 rrow: 187)
leaf: 0x1800055 25165909 (59: nrow: 188 rrow: 188)
leaf: 0x1800056 25165910 (60: nrow: 187 rrow: 187)
leaf: 0x1800057 25165911 (61: nrow: 189 rrow: 189)
leaf: 0x1800058 25165912 (62: nrow: 187 rrow: 187)
leaf: 0x1800059 25165913 (63: nrow: 185 rrow: 185)
leaf: 0x180005a 25165914 (64: nrow: 183 rrow: 183)
leaf: 0x180005b 25165915 (65: nrow: 191 rrow: 191)
leaf: 0x180005c 25165916 (66: nrow: 192 rrow: 192)
leaf: 0x180005d 25165917 (67: nrow: 189 rrow: 189)
leaf: 0x180005e 25165918 (68: nrow: 191 rrow: 191)
leaf: 0x180005f 25165919 (69: nrow: 190 rrow: 190)
leaf: 0x1800060 25165920 (70: nrow: 189 rrow: 189)
leaf: 0x1800062 25165922 (71: nrow: 190 rrow: 190)
leaf: 0x1800063 25165923 (72: nrow: 185 rrow: 185)
leaf: 0x1800064 25165924 (73: nrow: 186 rrow: 186)
leaf: 0x1800065 25165925 (74: nrow: 183 rrow: 183)
leaf: 0x1800066 25165926 (75: nrow: 189 rrow: 189)
leaf: 0x1800067 25165927 (76: nrow: 189 rrow: 189)
leaf: 0x1800068 25165928 (77: nrow: 184 rrow: 184)
leaf: 0x1800069 25165929 (78: nrow: 190 rrow: 190)
leaf: 0x180006a 25165930 (79: nrow: 193 rrow: 193)
leaf: 0x180006b 25165931 (80: nrow: 180 rrow: 180)
leaf: 0x180006c 25165932 (81: nrow: 186 rrow: 186)
leaf: 0x180006d 25165933 (82: nrow: 189 rrow: 189)
leaf: 0x180006e 25165934 (83: nrow: 184 rrow: 184)
leaf: 0x180006f 25165935 (84: nrow: 184 rrow: 184)
leaf: 0x1800070 25165936 (85: nrow: 188 rrow: 188)
leaf: 0x1800072 25165938 (86: nrow: 187 rrow: 187)
leaf: 0x1800073 25165939 (87: nrow: 182 rrow: 182)
leaf: 0x1800074 25165940 (88: nrow: 188 rrow: 188)
leaf: 0x1800075 25165941 (89: nrow: 181 rrow: 181)
leaf: 0x1800076 25165942 (90: nrow: 188 rrow: 188)
leaf: 0x1800077 25165943 (91: nrow: 190 rrow: 190)
leaf: 0x1800078 25165944 (92: nrow: 183 rrow: 183)
leaf: 0x1800079 25165945 (93: nrow: 192 rrow: 192)
leaf: 0x180007a 25165946 (94: nrow: 187 rrow: 187)
leaf: 0x180007b 25165947 (95: nrow: 188 rrow: 188)
leaf: 0x180007c 25165948 (96: nrow: 188 rrow: 188)
leaf: 0x180007d 25165949 (97: nrow: 188 rrow: 188)
leaf: 0x180007e 25165950 (98: nrow: 192 rrow: 192)
leaf: 0x180007f 25165951 (99: nrow: 186 rrow: 186)
leaf: 0x1800080 25165952 (100: nrow: 185 rrow: 185)
leaf: 0x1800082 25165954 (101: nrow: 183 rrow: 183)
leaf: 0x1800083 25165955 (102: nrow: 187 rrow: 187)
leaf: 0x1800084 25165956 (103: nrow: 191 rrow: 191)
leaf: 0x1800085 25165957 (104: nrow: 184 rrow: 184)
leaf: 0x1800086 25165958 (105: nrow: 183 rrow: 183)
leaf: 0x1800087 25165959 (106: nrow: 180 rrow: 180)
leaf: 0x1800088 25165960 (107: nrow: 187 rrow: 187)
leaf: 0x1800609 25167369 (108: nrow: 178 rrow: 178)
leaf: 0x180060a 25167370 (109: nrow: 189 rrow: 189)
leaf: 0x180060b 25167371 (110: nrow: 188 rrow: 188)
leaf: 0x180060c 25167372 (111: nrow: 185 rrow: 185)
leaf: 0x180060d 25167373 (112: nrow: 185 rrow: 185)
leaf: 0x180060e 25167374 (113: nrow: 182 rrow: 182)
leaf: 0x180060f 25167375 (114: nrow: 184 rrow: 184)
leaf: 0x1800610 25167376 (115: nrow: 187 rrow: 187)
leaf: 0x180068b 25167499 (116: nrow: 186 rrow: 186)
leaf: 0x180068c 25167500 (117: nrow: 183 rrow: 183)
leaf: 0x180068d 25167501 (118: nrow: 184 rrow: 184)
leaf: 0x180068e 25167502 (119: nrow: 187 rrow: 187)
leaf: 0x180068f 25167503 (120: nrow: 185 rrow: 185)
leaf: 0x1800690 25167504 (121: nrow: 179 rrow: 179)
leaf: 0x1800691 25167505 (122: nrow: 185 rrow: 185)
leaf: 0x1800692 25167506 (123: nrow: 190 rrow: 190)
leaf: 0x1800693 25167507 (124: nrow: 187 rrow: 187)
leaf: 0x1800694 25167508 (125: nrow: 190 rrow: 190)
leaf: 0x1800695 25167509 (126: nrow: 190 rrow: 190)
leaf: 0x1800696 25167510 (127: nrow: 184 rrow: 184)
leaf: 0x1800697 25167511 (128: nrow: 183 rrow: 183)
leaf: 0x1800698 25167512 (129: nrow: 183 rrow: 183)
leaf: 0x1800699 25167513 (130: nrow: 186 rrow: 186)
leaf: 0x180069a 25167514 (131: nrow: 186 rrow: 186)
leaf: 0x180069b 25167515 (132: nrow: 187 rrow: 187)
leaf: 0x180069c 25167516 (133: nrow: 182 rrow: 182)
leaf: 0x180069d 25167517 (134: nrow: 189 rrow: 189)
leaf: 0x180069e 25167518 (135: nrow: 189 rrow: 189)
leaf: 0x180069f 25167519 (136: nrow: 188 rrow: 188)
leaf: 0x18006a0 25167520 (137: nrow: 187 rrow: 187)
leaf: 0x18006a1 25167521 (138: nrow: 185 rrow: 185)
leaf: 0x18006a2 25167522 (139: nrow: 189 rrow: 189)
leaf: 0x18006a3 25167523 (140: nrow: 187 rrow: 187)
leaf: 0x18006a4 25167524 (141: nrow: 191 rrow: 191)
leaf: 0x18006a5 25167525 (142: nrow: 181 rrow: 181)
leaf: 0x18006a6 25167526 (143: nrow: 194 rrow: 194)
leaf: 0x18006a7 25167527 (144: nrow: 186 rrow: 186)
leaf: 0x18006a8 25167528 (145: nrow: 192 rrow: 192)
leaf: 0x18006a9 25167529 (146: nrow: 186 rrow: 186)
leaf: 0x18006aa 25167530 (147: nrow: 188 rrow: 188)
leaf: 0x18006ab 25167531 (148: nrow: 187 rrow: 187)
leaf: 0x18006ac 25167532 (149: nrow: 187 rrow: 187)
leaf: 0x18006ad 25167533 (150: nrow: 190 rrow: 190)
leaf: 0x18006ae 25167534 (151: nrow: 187 rrow: 187)
leaf: 0x18006af 25167535 (152: nrow: 185 rrow: 185)
leaf: 0x18006b0 25167536 (153: nrow: 184 rrow: 184)
leaf: 0x18006b1 25167537 (154: nrow: 196 rrow: 196)
leaf: 0x18006b2 25167538 (155: nrow: 185 rrow: 185)
leaf: 0x18006b3 25167539 (156: nrow: 189 rrow: 189)
leaf: 0x18006b4 25167540 (157: nrow: 189 rrow: 189)
leaf: 0x18006b5 25167541 (158: nrow: 186 rrow: 186)
leaf: 0x18006b6 25167542 (159: nrow: 184 rrow: 184)
leaf: 0x18006b7 25167543 (160: nrow: 190 rrow: 190)
leaf: 0x18006b8 25167544 (161: nrow: 186 rrow: 186)
leaf: 0x18006b9 25167545 (162: nrow: 188 rrow: 188)
leaf: 0x18006ba 25167546 (163: nrow: 184 rrow: 184)
leaf: 0x18006bb 25167547 (164: nrow: 184 rrow: 184)
leaf: 0x18006bc 25167548 (165: nrow: 183 rrow: 183)
leaf: 0x18006bd 25167549 (166: nrow: 189 rrow: 189)
leaf: 0x18006be 25167550 (167: nrow: 189 rrow: 189)
leaf: 0x18006bf 25167551 (168: nrow: 186 rrow: 186)
leaf: 0x18006c0 25167552 (169: nrow: 187 rrow: 187)
leaf: 0x18006c1 25167553 (170: nrow: 186 rrow: 186)
leaf: 0x18006c2 25167554 (171: nrow: 187 rrow: 187)
leaf: 0x18006c3 25167555 (172: nrow: 183 rrow: 183)
leaf: 0x18006c4 25167556 (173: nrow: 184 rrow: 184)
leaf: 0x18006c5 25167557 (174: nrow: 187 rrow: 187)
leaf: 0x18006c6 25167558 (175: nrow: 187 rrow: 187)
leaf: 0x18006c7 25167559 (176: nrow: 184 rrow: 184)
leaf: 0x18006c8 25167560 (177: nrow: 188 rrow: 188)
leaf: 0x18006c9 25167561 (178: nrow: 188 rrow: 188)
leaf: 0x18006ca 25167562 (179: nrow: 188 rrow: 188)
leaf: 0x18006cb 25167563 (180: nrow: 183 rrow: 183)
leaf: 0x18006cc 25167564 (181: nrow: 181 rrow: 181)
leaf: 0x18006cd 25167565 (182: nrow: 184 rrow: 184)
leaf: 0x18006ce 25167566 (183: nrow: 178 rrow: 178)
leaf: 0x18006cf 25167567 (184: nrow: 188 rrow: 188)
leaf: 0x18006d0 25167568 (185: nrow: 189 rrow: 189)
leaf: 0x18006d1 25167569 (186: nrow: 185 rrow: 185)
leaf: 0x18006d2 25167570 (187: nrow: 187 rrow: 187)
leaf: 0x18006d3 25167571 (188: nrow: 192 rrow: 192)
leaf: 0x18006d4 25167572 (189: nrow: 188 rrow: 188)
leaf: 0x18006d5 25167573 (190: nrow: 189 rrow: 189)
leaf: 0x18006d6 25167574 (191: nrow: 190 rrow: 190)
leaf: 0x18006d7 25167575 (192: nrow: 187 rrow: 187)
leaf: 0x18006d8 25167576 (193: nrow: 178 rrow: 178)
leaf: 0x18006d9 25167577 (194: nrow: 189 rrow: 189)
leaf: 0x18006da 25167578 (195: nrow: 179 rrow: 179)
leaf: 0x18006db 25167579 (196: nrow: 182 rrow: 182)
leaf: 0x18006dc 25167580 (197: nrow: 186 rrow: 186)
leaf: 0x18006dd 25167581 (198: nrow: 186 rrow: 186)
leaf: 0x18006de 25167582 (199: nrow: 191 rrow: 191)
leaf: 0x18006df 25167583 (200: nrow: 184 rrow: 184)
leaf: 0x18006e0 25167584 (201: nrow: 184 rrow: 184)
leaf: 0x18006e1 25167585 (202: nrow: 183 rrow: 183)
leaf: 0x18006e2 25167586 (203: nrow: 188 rrow: 188)
leaf: 0x18006e3 25167587 (204: nrow: 188 rrow: 188)
leaf: 0x18006e4 25167588 (205: nrow: 189 rrow: 189)
leaf: 0x18006e5 25167589 (206: nrow: 189 rrow: 189)
leaf: 0x18006e6 25167590 (207: nrow: 193 rrow: 193)
leaf: 0x18006e7 25167591 (208: nrow: 183 rrow: 183)
leaf: 0x18006e8 25167592 (209: nrow: 185 rrow: 185)
leaf: 0x18006e9 25167593 (210: nrow: 184 rrow: 184)
leaf: 0x18006ea 25167594 (211: nrow: 186 rrow: 186)
leaf: 0x18006eb 25167595 (212: nrow: 184 rrow: 184)
leaf: 0x18006ec 25167596 (213: nrow: 187 rrow: 187)
leaf: 0x18006ed 25167597 (214: nrow: 188 rrow: 188)
leaf: 0x18006ee 25167598 (215: nrow: 186 rrow: 186)
leaf: 0x18006ef 25167599 (216: nrow: 193 rrow: 193)
leaf: 0x18006f0 25167600 (217: nrow: 185 rrow: 185)
leaf: 0x18006f1 25167601 (218: nrow: 184 rrow: 184)
leaf: 0x18006f2 25167602 (219: nrow: 189 rrow: 189)
leaf: 0x18006f3 25167603 (220: nrow: 184 rrow: 184)
leaf: 0x18006f4 25167604 (221: nrow: 186 rrow: 186)
leaf: 0x18006f5 25167605 (222: nrow: 187 rrow: 187)
leaf: 0x18006f6 25167606 (223: nrow: 188 rrow: 188)
leaf: 0x18006f7 25167607 (224: nrow: 182 rrow: 182)
leaf: 0x18006f8 25167608 (225: nrow: 183 rrow: 183)
leaf: 0x18006f9 25167609 (226: nrow: 186 rrow: 186)
leaf: 0x18006fa 25167610 (227: nrow: 181 rrow: 181)
leaf: 0x18006fb 25167611 (228: nrow: 184 rrow: 184)
leaf: 0x18006fc 25167612 (229: nrow: 185 rrow: 185)
leaf: 0x18006fd 25167613 (230: nrow: 187 rrow: 187)
leaf: 0x18006fe 25167614 (231: nrow: 183 rrow: 183)
leaf: 0x18006ff 25167615 (232: nrow: 182 rrow: 182)
leaf: 0x1800700 25167616 (233: nrow: 187 rrow: 187)
leaf: 0x1800701 25167617 (234: nrow: 184 rrow: 184)
leaf: 0x1800702 25167618 (235: nrow: 186 rrow: 186)
leaf: 0x1800703 25167619 (236: nrow: 187 rrow: 187)
leaf: 0x1800704 25167620 (237: nrow: 189 rrow: 189)
leaf: 0x1800705 25167621 (238: nrow: 186 rrow: 186)
leaf: 0x1800706 25167622 (239: nrow: 181 rrow: 181)
leaf: 0x1800707 25167623 (240: nrow: 186 rrow: 186)
leaf: 0x1800708 25167624 (241: nrow: 190 rrow: 190)
leaf: 0x180070b 25167627 (242: nrow: 190 rrow: 190)
leaf: 0x180070c 25167628 (243: nrow: 222 rrow: 222)
leaf: 0x180070d 25167629 (244: nrow: 200 rrow: 200)
leaf: 0x180070e 25167630 (245: nrow: 230 rrow: 230)
branch: 0x180080c 25167884 (0: nrow: 248, level: 1)
leaf: 0x180070f 25167631 (-1: nrow: 229 rrow: 229)
leaf: 0x1800711 25167633 (0: nrow: 222 rrow: 222)
leaf: 0x1800712 25167634 (1: nrow: 226 rrow: 226)
leaf: 0x1800713 25167635 (2: nrow: 221 rrow: 221)
leaf: 0x1800714 25167636 (3: nrow: 221 rrow: 221)
leaf: 0x1800715 25167637 (4: nrow: 249 rrow: 249)
leaf: 0x1800716 25167638 (5: nrow: 233 rrow: 233)
leaf: 0x1800717 25167639 (6: nrow: 212 rrow: 212)
leaf: 0x1800718 25167640 (7: nrow: 256 rrow: 256)
leaf: 0x1800719 25167641 (8: nrow: 274 rrow: 274)
leaf: 0x180071a 25167642 (9: nrow: 201 rrow: 201)
leaf: 0x180071b 25167643 (10: nrow: 199 rrow: 199)
leaf: 0x180071c 25167644 (11: nrow: 199 rrow: 199)
leaf: 0x180071d 25167645 (12: nrow: 249 rrow: 249)
leaf: 0x180071e 25167646 (13: nrow: 286 rrow: 286)
leaf: 0x180071f 25167647 (14: nrow: 239 rrow: 239)
leaf: 0x1800720 25167648 (15: nrow: 236 rrow: 236)
leaf: 0x1800721 25167649 (16: nrow: 222 rrow: 222)
leaf: 0x1800722 25167650 (17: nrow: 213 rrow: 213)
leaf: 0x1800723 25167651 (18: nrow: 227 rrow: 227)
leaf: 0x1800724 25167652 (19: nrow: 204 rrow: 204)
leaf: 0x1800725 25167653 (20: nrow: 226 rrow: 226)
leaf: 0x1800726 25167654 (21: nrow: 231 rrow: 231)
leaf: 0x1800727 25167655 (22: nrow: 211 rrow: 211)
leaf: 0x1800728 25167656 (23: nrow: 228 rrow: 228)
leaf: 0x1800729 25167657 (24: nrow: 235 rrow: 235)
leaf: 0x180072a 25167658 (25: nrow: 222 rrow: 222)
leaf: 0x180072b 25167659 (26: nrow: 224 rrow: 224)
leaf: 0x180072c 25167660 (27: nrow: 227 rrow: 227)
leaf: 0x180072d 25167661 (28: nrow: 222 rrow: 222)
leaf: 0x180072e 25167662 (29: nrow: 260 rrow: 260)
leaf: 0x180072f 25167663 (30: nrow: 239 rrow: 239)
leaf: 0x1800730 25167664 (31: nrow: 244 rrow: 244)
leaf: 0x1800731 25167665 (32: nrow: 242 rrow: 242)
leaf: 0x1800732 25167666 (33: nrow: 256 rrow: 256)
leaf: 0x1800733 25167667 (34: nrow: 259 rrow: 259)
leaf: 0x1800734 25167668 (35: nrow: 242 rrow: 242)
leaf: 0x1800735 25167669 (36: nrow: 233 rrow: 233)
leaf: 0x1800736 25167670 (37: nrow: 249 rrow: 249)
leaf: 0x1800737 25167671 (38: nrow: 255 rrow: 255)
leaf: 0x1800738 25167672 (39: nrow: 286 rrow: 286)
leaf: 0x1800739 25167673 (40: nrow: 311 rrow: 311)
leaf: 0x180073a 25167674 (41: nrow: 277 rrow: 277)
leaf: 0x180073b 25167675 (42: nrow: 338 rrow: 338)
leaf: 0x180073c 25167676 (43: nrow: 277 rrow: 277)
leaf: 0x180073d 25167677 (44: nrow: 266 rrow: 266)
leaf: 0x180073e 25167678 (45: nrow: 336 rrow: 336)
leaf: 0x180073f 25167679 (46: nrow: 358 rrow: 358)
leaf: 0x1800740 25167680 (47: nrow: 286 rrow: 286)
leaf: 0x1800741 25167681 (48: nrow: 212 rrow: 212)
leaf: 0x1800742 25167682 (49: nrow: 199 rrow: 199)
leaf: 0x1800743 25167683 (50: nrow: 258 rrow: 258)
leaf: 0x1800744 25167684 (51: nrow: 246 rrow: 246)
leaf: 0x1800745 25167685 (52: nrow: 245 rrow: 245)
leaf: 0x1800746 25167686 (53: nrow: 249 rrow: 249)
leaf: 0x1800747 25167687 (54: nrow: 239 rrow: 239)
leaf: 0x1800748 25167688 (55: nrow: 236 rrow: 236)
leaf: 0x1800749 25167689 (56: nrow: 295 rrow: 295)
leaf: 0x180074a 25167690 (57: nrow: 238 rrow: 238)
leaf: 0x180074b 25167691 (58: nrow: 234 rrow: 234)
leaf: 0x180074c 25167692 (59: nrow: 283 rrow: 283)
leaf: 0x180074d 25167693 (60: nrow: 262 rrow: 262)
leaf: 0x180074e 25167694 (61: nrow: 266 rrow: 266)
leaf: 0x180074f 25167695 (62: nrow: 267 rrow: 267)
leaf: 0x1800750 25167696 (63: nrow: 242 rrow: 242)
leaf: 0x1800751 25167697 (64: nrow: 244 rrow: 244)
leaf: 0x1800752 25167698 (65: nrow: 252 rrow: 252)
leaf: 0x1800753 25167699 (66: nrow: 238 rrow: 238)
leaf: 0x1800754 25167700 (67: nrow: 241 rrow: 241)
leaf: 0x1800755 25167701 (68: nrow: 255 rrow: 255)
leaf: 0x1800756 25167702 (69: nrow: 269 rrow: 269)
leaf: 0x1800757 25167703 (70: nrow: 260 rrow: 260)
leaf: 0x1800758 25167704 (71: nrow: 262 rrow: 262)
leaf: 0x1800759 25167705 (72: nrow: 218 rrow: 218)
leaf: 0x180075a 25167706 (73: nrow: 253 rrow: 253)
leaf: 0x180075b 25167707 (74: nrow: 259 rrow: 259)
leaf: 0x180075c 25167708 (75: nrow: 222 rrow: 222)
leaf: 0x180075d 25167709 (76: nrow: 227 rrow: 227)
leaf: 0x180075e 25167710 (77: nrow: 209 rrow: 209)
leaf: 0x180075f 25167711 (78: nrow: 216 rrow: 216)
leaf: 0x1800760 25167712 (79: nrow: 219 rrow: 219)
leaf: 0x1800761 25167713 (80: nrow: 220 rrow: 220)
leaf: 0x1800762 25167714 (81: nrow: 219 rrow: 219)
leaf: 0x1800763 25167715 (82: nrow: 222 rrow: 222)
leaf: 0x1800764 25167716 (83: nrow: 223 rrow: 223)
leaf: 0x1800765 25167717 (84: nrow: 206 rrow: 206)
leaf: 0x1800766 25167718 (85: nrow: 255 rrow: 255)
leaf: 0x1800767 25167719 (86: nrow: 254 rrow: 254)
leaf: 0x1800768 25167720 (87: nrow: 248 rrow: 248)
leaf: 0x1800769 25167721 (88: nrow: 246 rrow: 246)
leaf: 0x180076a 25167722 (89: nrow: 295 rrow: 295)
leaf: 0x180076b 25167723 (90: nrow: 268 rrow: 268)
leaf: 0x180076c 25167724 (91: nrow: 281 rrow: 281)
leaf: 0x180076d 25167725 (92: nrow: 239 rrow: 239)
leaf: 0x180076e 25167726 (93: nrow: 209 rrow: 209)
leaf: 0x180076f 25167727 (94: nrow: 306 rrow: 306)
leaf: 0x1800770 25167728 (95: nrow: 269 rrow: 269)
leaf: 0x1800771 25167729 (96: nrow: 240 rrow: 240)
leaf: 0x1800772 25167730 (97: nrow: 254 rrow: 254)
leaf: 0x1800773 25167731 (98: nrow: 266 rrow: 266)
leaf: 0x1800774 25167732 (99: nrow: 259 rrow: 259)
leaf: 0x1800775 25167733 (100: nrow: 269 rrow: 269)
leaf: 0x1800776 25167734 (101: nrow: 249 rrow: 249)
leaf: 0x1800777 25167735 (102: nrow: 215 rrow: 215)
leaf: 0x1800778 25167736 (103: nrow: 230 rrow: 230)
leaf: 0x1800779 25167737 (104: nrow: 252 rrow: 252)
leaf: 0x180077a 25167738 (105: nrow: 296 rrow: 296)
leaf: 0x180077b 25167739 (106: nrow: 226 rrow: 226)
leaf: 0x180077c 25167740 (107: nrow: 194 rrow: 194)
leaf: 0x180077d 25167741 (108: nrow: 194 rrow: 194)
leaf: 0x180077e 25167742 (109: nrow: 194 rrow: 194)
leaf: 0x180077f 25167743 (110: nrow: 194 rrow: 194)
leaf: 0x1800780 25167744 (111: nrow: 194 rrow: 194)
leaf: 0x1800781 25167745 (112: nrow: 217 rrow: 217)
leaf: 0x1800782 25167746 (113: nrow: 231 rrow: 231)
leaf: 0x1800783 25167747 (114: nrow: 237 rrow: 237)
leaf: 0x1800784 25167748 (115: nrow: 276 rrow: 276)
leaf: 0x1800785 25167749 (116: nrow: 232 rrow: 232)
leaf: 0x1800786 25167750 (117: nrow: 220 rrow: 220)
leaf: 0x1800787 25167751 (118: nrow: 239 rrow: 239)
leaf: 0x1800788 25167752 (119: nrow: 222 rrow: 222)
leaf: 0x180078b 25167755 (120: nrow: 220 rrow: 220)
leaf: 0x180078c 25167756 (121: nrow: 238 rrow: 238)
leaf: 0x180078d 25167757 (122: nrow: 286 rrow: 286)
leaf: 0x180078e 25167758 (123: nrow: 250 rrow: 250)
leaf: 0x180078f 25167759 (124: nrow: 256 rrow: 256)
leaf: 0x1800790 25167760 (125: nrow: 261 rrow: 261)
leaf: 0x1800791 25167761 (126: nrow: 249 rrow: 249)
leaf: 0x1800792 25167762 (127: nrow: 246 rrow: 246)
leaf: 0x1800793 25167763 (128: nrow: 243 rrow: 243)
leaf: 0x1800794 25167764 (129: nrow: 227 rrow: 227)
leaf: 0x1800795 25167765 (130: nrow: 216 rrow: 216)
leaf: 0x1800796 25167766 (131: nrow: 235 rrow: 235)
leaf: 0x1800797 25167767 (132: nrow: 229 rrow: 229)
leaf: 0x1800798 25167768 (133: nrow: 230 rrow: 230)
leaf: 0x1800799 25167769 (134: nrow: 224 rrow: 224)
leaf: 0x180079a 25167770 (135: nrow: 219 rrow: 219)
leaf: 0x180079b 25167771 (136: nrow: 268 rrow: 268)
leaf: 0x180079c 25167772 (137: nrow: 242 rrow: 242)
leaf: 0x180079d 25167773 (138: nrow: 203 rrow: 203)
leaf: 0x180079e 25167774 (139: nrow: 200 rrow: 200)
leaf: 0x180079f 25167775 (140: nrow: 184 rrow: 184)
leaf: 0x18007a0 25167776 (141: nrow: 177 rrow: 177)
leaf: 0x18007a1 25167777 (142: nrow: 182 rrow: 182)
leaf: 0x18007a2 25167778 (143: nrow: 214 rrow: 214)
leaf: 0x18007a3 25167779 (144: nrow: 217 rrow: 217)
leaf: 0x18007a4 25167780 (145: nrow: 222 rrow: 222)
leaf: 0x18007a5 25167781 (146: nrow: 189 rrow: 189)
leaf: 0x18007a6 25167782 (147: nrow: 190 rrow: 190)
leaf: 0x18007a7 25167783 (148: nrow: 185 rrow: 185)
leaf: 0x18007a8 25167784 (149: nrow: 197 rrow: 197)
leaf: 0x18007a9 25167785 (150: nrow: 203 rrow: 203)
leaf: 0x18007aa 25167786 (151: nrow: 197 rrow: 197)
leaf: 0x18007ab 25167787 (152: nrow: 210 rrow: 210)
leaf: 0x18007ac 25167788 (153: nrow: 211 rrow: 211)
leaf: 0x18007ad 25167789 (154: nrow: 195 rrow: 195)
leaf: 0x18007ae 25167790 (155: nrow: 209 rrow: 209)
leaf: 0x18007af 25167791 (156: nrow: 192 rrow: 192)
leaf: 0x18007b0 25167792 (157: nrow: 192 rrow: 192)
leaf: 0x18007b1 25167793 (158: nrow: 193 rrow: 193)
leaf: 0x18007b2 25167794 (159: nrow: 208 rrow: 208)
leaf: 0x18007b3 25167795 (160: nrow: 200 rrow: 200)
leaf: 0x18007b4 25167796 (161: nrow: 206 rrow: 206)
leaf: 0x18007b5 25167797 (162: nrow: 216 rrow: 216)
leaf: 0x18007b6 25167798 (163: nrow: 186 rrow: 186)
leaf: 0x18007b7 25167799 (164: nrow: 183 rrow: 183)
leaf: 0x18007b8 25167800 (165: nrow: 202 rrow: 202)
leaf: 0x18007b9 25167801 (166: nrow: 200 rrow: 200)
leaf: 0x18007ba 25167802 (167: nrow: 199 rrow: 199)
leaf: 0x18007bb 25167803 (168: nrow: 184 rrow: 184)
leaf: 0x18007bc 25167804 (169: nrow: 188 rrow: 188)
leaf: 0x18007bd 25167805 (170: nrow: 188 rrow: 188)
leaf: 0x18007be 25167806 (171: nrow: 182 rrow: 182)
leaf: 0x18007bf 25167807 (172: nrow: 192 rrow: 192)
leaf: 0x18007c0 25167808 (173: nrow: 198 rrow: 198)
leaf: 0x18007c1 25167809 (174: nrow: 210 rrow: 210)
leaf: 0x18007c2 25167810 (175: nrow: 195 rrow: 195)
leaf: 0x18007c3 25167811 (176: nrow: 186 rrow: 186)
leaf: 0x18007c4 25167812 (177: nrow: 183 rrow: 183)
leaf: 0x18007c5 25167813 (178: nrow: 183 rrow: 183)
leaf: 0x18007c6 25167814 (179: nrow: 209 rrow: 209)
leaf: 0x18007c7 25167815 (180: nrow: 197 rrow: 197)
leaf: 0x18007c8 25167816 (181: nrow: 201 rrow: 201)
leaf: 0x18007c9 25167817 (182: nrow: 184 rrow: 184)
leaf: 0x18007ca 25167818 (183: nrow: 184 rrow: 184)
leaf: 0x18007cb 25167819 (184: nrow: 184 rrow: 184)
leaf: 0x18007cc 25167820 (185: nrow: 180 rrow: 180)
leaf: 0x18007cd 25167821 (186: nrow: 178 rrow: 178)
leaf: 0x18007ce 25167822 (187: nrow: 179 rrow: 179)
leaf: 0x18007cf 25167823 (188: nrow: 179 rrow: 179)
leaf: 0x18007d0 25167824 (189: nrow: 179 rrow: 179)
leaf: 0x18007d1 25167825 (190: nrow: 180 rrow: 180)
leaf: 0x18007d2 25167826 (191: nrow: 183 rrow: 183)
leaf: 0x18007d3 25167827 (192: nrow: 178 rrow: 178)
leaf: 0x18007d4 25167828 (193: nrow: 179 rrow: 179)
leaf: 0x18007d5 25167829 (194: nrow: 186 rrow: 186)
leaf: 0x18007d6 25167830 (195: nrow: 191 rrow: 191)
leaf: 0x18007d7 25167831 (196: nrow: 203 rrow: 203)
leaf: 0x18007d8 25167832 (197: nrow: 190 rrow: 190)
leaf: 0x18007d9 25167833 (198: nrow: 186 rrow: 186)
leaf: 0x18007da 25167834 (199: nrow: 185 rrow: 185)
leaf: 0x18007db 25167835 (200: nrow: 183 rrow: 183)
leaf: 0x18007dc 25167836 (201: nrow: 198 rrow: 198)
leaf: 0x18007dd 25167837 (202: nrow: 208 rrow: 208)
leaf: 0x18007de 25167838 (203: nrow: 184 rrow: 184)
leaf: 0x18007df 25167839 (204: nrow: 183 rrow: 183)
leaf: 0x18007e0 25167840 (205: nrow: 186 rrow: 186)
leaf: 0x18007e1 25167841 (206: nrow: 189 rrow: 189)
leaf: 0x18007e2 25167842 (207: nrow: 181 rrow: 181)
leaf: 0x18007e3 25167843 (208: nrow: 181 rrow: 181)
leaf: 0x18007e4 25167844 (209: nrow: 193 rrow: 193)
leaf: 0x18007e5 25167845 (210: nrow: 197 rrow: 197)
leaf: 0x18007e6 25167846 (211: nrow: 186 rrow: 186)
leaf: 0x18007e7 25167847 (212: nrow: 189 rrow: 189)
leaf: 0x18007e8 25167848 (213: nrow: 201 rrow: 201)
leaf: 0x18007e9 25167849 (214: nrow: 201 rrow: 201)
leaf: 0x18007ea 25167850 (215: nrow: 183 rrow: 183)
leaf: 0x18007eb 25167851 (216: nrow: 196 rrow: 196)
leaf: 0x18007ec 25167852 (217: nrow: 178 rrow: 178)
leaf: 0x18007ed 25167853 (218: nrow: 183 rrow: 183)
leaf: 0x18007ee 25167854 (219: nrow: 180 rrow: 180)
leaf: 0x18007ef 25167855 (220: nrow: 196 rrow: 196)
leaf: 0x18007f0 25167856 (221: nrow: 197 rrow: 197)
leaf: 0x18007f1 25167857 (222: nrow: 197 rrow: 197)
leaf: 0x18007f2 25167858 (223: nrow: 194 rrow: 194)
leaf: 0x18007f3 25167859 (224: nrow: 190 rrow: 190)
leaf: 0x18007f4 25167860 (225: nrow: 193 rrow: 193)
leaf: 0x18007f5 25167861 (226: nrow: 207 rrow: 207)
leaf: 0x18007f6 25167862 (227: nrow: 202 rrow: 202)
leaf: 0x18007f7 25167863 (228: nrow: 194 rrow: 194)
leaf: 0x18007f8 25167864 (229: nrow: 207 rrow: 207)
leaf: 0x18007f9 25167865 (230: nrow: 204 rrow: 204)
leaf: 0x18007fa 25167866 (231: nrow: 194 rrow: 194)
leaf: 0x18007fb 25167867 (232: nrow: 185 rrow: 185)
leaf: 0x18007fc 25167868 (233: nrow: 208 rrow: 208)
leaf: 0x18007fd 25167869 (234: nrow: 218 rrow: 218)
leaf: 0x18007fe 25167870 (235: nrow: 194 rrow: 194)
leaf: 0x18007ff 25167871 (236: nrow: 192 rrow: 192)
leaf: 0x1800800 25167872 (237: nrow: 200 rrow: 200)
leaf: 0x1800801 25167873 (238: nrow: 191 rrow: 191)
leaf: 0x1800802 25167874 (239: nrow: 186 rrow: 186)
leaf: 0x1800803 25167875 (240: nrow: 181 rrow: 181)
leaf: 0x1800804 25167876 (241: nrow: 182 rrow: 182)
leaf: 0x1800805 25167877 (242: nrow: 182 rrow: 182)
leaf: 0x1800806 25167878 (243: nrow: 190 rrow: 190)
leaf: 0x1800807 25167879 (244: nrow: 191 rrow: 191)
leaf: 0x1800808 25167880 (245: nrow: 180 rrow: 180)
leaf: 0x180080b 25167883 (246: nrow: 55 rrow: 55)
----- end tree dump
------------------
解釋 treedump 輸出
------------------
branch 表示的是 branch block ，它後面跟了一個十六進位制表示的DBA(data block address)，以及用10進製表示的DBA
DBA 之後表示在同一層次的相對位置(root 從0開始,branch 以及leaf從 -1開始)
nrow 表示塊中包含了多少條目(包括delete的條目)
rrow 表示塊中包含的實際條目(不包括delete的條目)
level 表示從該block到leaf的深度(leaf沒有 level)
branch: 0x1800014 25165844 (0: nrow: 2, level: 2)
這個 branch block 的 level 為2，也就是說 從這個branch block 到 leaf block 的深度為2,根據前面的查詢，這個索引的Blevel為2,所以這個
branch其實是 root block. 其實根據 nrow:2 也可以看出來它是root block，因為nrow:2 說明它只包含了2個條目，那麼這2個條目其實就是dump
檔案中的其他2個 branch block 的條目
現在我來驗證一下 branch: 0x1800014 25165844 (0: nrow: 2, level: 2) 是不是 root block , 我查詢這個 branch 的 DBA
SQL> select dbms_utility.data_block_address_file('25165844') FILE_ID,
2 dbms_utility.data_block_address_block('25165844') BLOCK_ID from dual;
FILE_ID BLOCK_ID
---------- ----------
6 20
Btree 索引的 root block總是segment header+1,所以我查詢該索引的段頭
SQL> select header_file,header_block from dba_segments where segment_name='I_TEST_OBJECT_NAME';
HEADER_FILE HEADER_BLOCK
----------- ------------
6 19
那麼現在已經證明了 branch: 0x1800014 25165844 (0: nrow: 2, level: 2) 是root block, 其實 treedump第一個 branch block 就是 root block
branch: 0x1800710 25167632 (-1: nrow: 247, level: 1)
這個branch 的 DBA為0x1800710(十六進位制)，25167632(十進位制) -1 表示它是與它在同一個深度的 branch block中的第一個 branch block
nrow: 247表示它有247個 leaf block. level: 1 表示 這個 branch block 到 leaf block的深度為1
leaf: 0x1800015 25165845 (-1: nrow: 182 rrow: 182)
leaf 表示它是一個 leaf block 0x1800015 25165845 分別是 這個 leaf block 的 十六進位制/十進位制的 DBA -1 表示它是 leaf block 中的第一個 block
nrow: 182 表示它一共有182條記錄 rrow: 182 表示它實際有182條記錄
-----------------
branch block dump
-----------------
這裡選擇dump branch: 0x1800710 25167632 (-1: nrow: 247, level: 1)
SQL> select dbms_utility.data_block_address_file('25167632') FILE_ID,
2 dbms_utility.data_block_address_block('25167632') BLOCK_ID from dual;
FILE_ID BLOCK_ID
---------- ----------
6 1808
SQL> alter system dump datafile 6 block 1808;
System altered.
部分 DUMP 檔案
Block header dump: 0x01800710
Object id on Block? Y
seg/obj: 0xd78a csc: 0x00.17bbef0 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1800709 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.017bbef0
Branch block dump
=================
header address 156329548=0x951664c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 246
kdxcofbo 520=0x208
kdxcofeo 549=0x225
kdxcoavs 29
kdxbrlmc 25165845=0x1800015
kdxbrsno 0
kdxbrbksz 8060
kdxbr2urrc 15
row#0[8027] dba: 25165846=0x1800016
col 0; len 24; (24):
2f 31 30 64 65 32 32 63 36 5f 43 6c 61 73 73 54 79 70 65 49 6d 70 6c 32
col 1; len 3; (3): 01 80 03
row#1[7988] dba: 25165847=0x1800017
col 0; len 30; (30):
2f 31 31 64 35 30 39 31 32 5f 44 61 74 65 46 6f 72 6d 61 74 5a 6f 6e 65 44
61 74 61 5f 7a
col 1; len 3; (3): 01 80 04
row#2[7977] dba: 25165848=0x1800018
col 0; len 5; (5): 2f 31 32 61 32
col 1; TERM
..................................省略..................................
row#245[549] dba: 25167630=0x180070e
col 0; len 10; (10): 41 4c 4c 24 4f 4c 41 50 5f 44
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 7 file#: 6 minblk 1808 maxblk 1808
----------------
解釋部分dump輸出
----------------
kdxcolev 1 --該block到leaf block的深度(leaf block 為0).這裡branch block 的level 為1 與前面查詢相吻合
KDXCOLEV Flags = - - -
kdxcolok 0 --表示是否有事務lock了這個branch block，如果有 有多少事務
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2 --索引值條目. 這裡表示有2個條目
kdxcosdc 0 --這個block的結構被更改次數.這裡0表示沒有更改
kdxconro 246 --索引條目(不包含kdxbrlmc 指標)
kdxcofbo 520=0x208 --空閒空間的起始偏移量
kdxcofeo 549=0x225 --空閒空間的末尾偏移量
kdxcoavs 29 --block中的空閒空間=kdxcofeo-kdxcofbo
kdxbrlmc 25165845=0x1800015 --如果index value小於row#0，指向該 block 的地址
kdxbrsno 0 --最後被更改的索引條目
kdxbrbksz 8060 --塊中的可用空間
kdxbr2urrc 15
row#2[7977] dba: 25165848=0x1800018 --row# 表示索引條目數，從0開始，緊接著就是十進位制和十六進位制的DBA，該DBA指向 leaf block
col 0; len 5; (5): 2f 31 32 61 32 --列的行號，從0開始，緊接著的就是列的長度以及列的值，那麼這個值稱之為separator key，
這個separator key 可以區分真實的索引值，所以從這裡我們也知道 branch block不會儲存完整的索引值，只要能區分就行
col 1; TERM
---------------
leaf block DUMP
---------------
這裡選擇 dump row#2[7977] dba: 25165848=0x1800018 ，因為它包含的索引鍵很少
dump 之前先看一下 row#2[7977] 儲存了什麼索引鍵
2f 表示 /
31 表示 1
32 表示 2
61 表示 a
具體轉換過程如下
SQL> declare n varchar2(2000);
2 begin
3 dbms_stats.convert_raw_value('2f',n);
4 dbms_output.put_line(n);
5 end;
6 /
/
PL/SQL procedure successfully completed.
SQL> select dbms_utility.data_block_address_file('25165848') FILE_ID,
2 dbms_utility.data_block_address_block('25165848') BLOCK_ID from dual;
FILE_ID BLOCK_ID
---------- ----------
6 24
SQL> alter system dump datafile 6 block 24;
System altered.
部分DUMP檔案
Block header dump: 0x01800018
Object id on Block? Y
seg/obj: 0xd78a csc: 0x00.17bbef0 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.017bbef0
Leaf block dump
===============
header address 146564708=0x8bc6664
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 189
kdxcofbo 414=0x19e
kdxcofeo 1248=0x4e0
kdxcoavs 834
kdxlespl 0
kdxlende 0
kdxlenxt 25165849=0x1800019
kdxleprv 25165847=0x1800017
kdxledsz 0
kdxlebksz 8036
row#0[7996] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 00 e2 00 16
row#1[7956] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 00 e2 00 17
row#2[7916] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 03 b5 00 25
row#3[7876] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 03 b5 00 26
row#4[7836] flag: ------, lock: 0, len=40
..................................省略..................................
row#188[1248] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 33 38 35 32 32 37 66 5f 4d 61 70 52 65 67 69 6f 6e 43 6f 6e 74 61 69
6e 6d 65 6e 74
col 1; len 6; (6): 01 80 01 6a 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 7 file#: 6 minblk 24 maxblk 24
----------------
解釋部分dump輸出
----------------
kdxcolev 0 --該block到leaf block的深度(leaf block 為0).
KDXCOLEV Flags = - - -
kdxcolok 0 --表示是否有事務lock了這個branch block，如果有 有多少事務
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2 --索引值條目. 這裡表示有2個條目
kdxcosdc 0 --這個block的結構被更改次數.這裡0表示沒有更改
kdxconro 189 --索引條目 ,這裡有189個索引條目 它等於 row#188-row#0+1
kdxcofbo 414=0x19e --空閒空間的起始偏移量
kdxcofeo 1248=0x4e0 --空閒空間的末尾偏移量
kdxcoavs 834 --block中的空閒空間=kdxcofeo-kdxcofbo
kdxlespl 0 --block split的時候沒有commit的記錄的大小(byte)
kdxlende 0 --被刪除的條目
kdxlenxt 25165849=0x1800019 --指向下一個 leaf block的指標(DBA)
kdxleprv 25165847=0x1800017 --指向上一個 leaf block的指標(DBA)
kdxledsz 0 --被刪除的空間
kdxlebksz 8036 --可用空間
row#0[7996] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 00 e2 00 16
row# 表示索引值的條目，從0開始 lock: 0 表示ITL中的鎖資訊 0表示沒有被鎖 len=40 表示索引值長度
col 表示列號，從0開始 那麼接下來就是索引的鍵值 以及 rowid中後12位值
下面我們來看一下row#0儲存的值是什麼
SQL> declare n varchar2(2000);
2 begin
3 dbms_stats.convert_raw_value('2f31326132373365365f496e7465726e6174696f6e616c466f726d617474',n);
4 dbms_output.put_line(n);
5 end;
6 /
/12a273e6_InternationalFormatt
PL/SQL procedure successfully completed.
ROWID一共用18位表示
最前6位表示 data object number
之後後3位表示 datafile number
之後後6位表示 datablock number
最後3位表示 row number
所以這裡的rowid其實只是除去了 data object number的一部分而已
SQL> select to_number('18000e2','xxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('18000E2','XXXXXXXXX
------------------------------
25166050
SQL> select dbms_utility.data_block_address_file('25166050') FILE_ID,dbms_utility.data_block_address_block('25166050') BLOCK_ID from dual;
FILE_ID BLOCK_ID
---------- ----------
6 226
SQL> select dbms_rowid.rowid_relative_fno(rowid)file_id, dbms_rowid.rowid_block_number(rowid)block_id,dbms_rowid.rowid_row_number(rowid) row#
2 from test where object_name='/12a273e6_InternationalFormatt';
FILE_ID BLOCK_ID ROW#
---------- ---------- ----------
6 226 22
6 226 23
6 949 37
6 949 38
根據這個還原的值我來查詢一下
SQL> select owner,object_name,rowid from test where object_name='/12a273e6_InternationalFormatt';
OWNER OBJECT_NAME ROWID
------------------------------ ------------------------------ ------------------
SYS /12a273e6_InternationalFormatt AAANeJAAGAAAADiAAW
PUBLIC /12a273e6_InternationalFormatt AAANeJAAGAAAADiAAX
SYS /12a273e6_InternationalFormatt AAANeJAAGAAAAO1AAl
PUBLIC /12a273e6_InternationalFormatt AAANeJAAGAAAAO1AAm
SQL> select owner,object_name,dump(rowid,16) from test where object_name='/12a273e6_InternationalFormatt';
OWNER OBJECT_NAME DUMP(ROWID,16)
---------- ----------------------------------- --------------------------------------------------------------------------------
SYS /12a273e6_InternationalFormatt Typ=69 Len=10: 0,0,d7,89,1,80,0,e2,0,16
PUBLIC /12a273e6_InternationalFormatt Typ=69 Len=10: 0,0,d7,89,1,80,0,e2,0,17
SYS /12a273e6_InternationalFormatt Typ=69 Len=10: 0,0,d7,89,1,80,3,b5,0,25
PUBLIC /12a273e6_InternationalFormatt Typ=69 Len=10: 0,0,d7,89,1,80,3,b5,0,26
回憶一下 branch block
row#2[7977] dba: 25165848=0x1800018
col 0; len 5; (5): 2f 31 32 61 32 -------它正是真正的索引鍵的字首
col 1; TERM
row#0[7996] flag: ------, lock: 0, len=40
col 0; len 30; (30):
2f 31 32 61 32 37 33 65 36 5f 49 6e 74 65 72 6e 61 74 69 6f 6e 61 6c 46 6f
72 6d 61 74 74
col 1; len 6; (6): 01 80 00 e2 00 16
之所以Oracle在 Branch block中只記錄 索引鍵值的字首，而不是所有值，是因為這樣可以節約空間，從而能夠儲存更多的 索引條目
同時，我們也能理解了為什麼 查詢使用 like '%xxx' 這種方法不會走Btree 索引，因為Branch block 儲存的是字首.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22990797/viewspace-1200698/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL DBA(47) - Index(Btree)SQLIndex
- Index internal 結構 試驗一Index
- mysql中BTree索引的理解MySql索引
- The Storage Situation: Removable StorageREM
- 【Postgresql】索引型別（btree、hash、GIST、GIN）SQL索引型別
- Mysql中的Btree與Hash索引比較MySql索引
- MySQL InnoDB表--BTree基本資料結構MySql資料結構
- MySQL的btree索引和hash索引的區別MySql索引
- storage事件中的坑,storage.setItem()無法觸發storage事件事件
- oracle internalOracle
- Database StorageDatabase
- Oracle-InternalOracle
- JavaScript storage 事件JavaScript事件
- Web Storage概述Web
- Kafka Offset StorageKafka
- MySQL Storage EnginesMySql
- Physical Storage StructuresStruct
- C#中public、private、protected、internal、protected internal (轉載）C#
- Display Storage Map for Database | Tablespace | Datafile Storage_1377458.1Database
- KEEP INDEX | DROP INDEXIndex
- oracle INTERNAL_FUNCTIONOracleFunction
- mongodb replicat internal(一）MongoDB
- Internal Server Error..ServerError
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- cookie、session、web storageCookieSessionWeb
- Docker storage儲存Docker
- Storage location 地址資訊
- SAP WM Storage Type Search配置裡的Storage Class & WPC標記
- INDEX UNIQUE SCAN，INDEX FULL SCAN和INDEX FAST FULL SCANIndexAST
- Clustered Index Scan and Clustered Index SeekIndex
- oracle hint之full,index,index_asc,index_desc,index_combile示例OracleIndex
- git internal for computer scientistsGit
- IndexIndex
- Index的掃描方式：index full scan/index fast full scanIndexAST
- rebuild index 和 recreate index (重新建立index) 的區別RebuildIndex
- ASM(Automatic Storage Management)ASM
- 【ionic】storage本地快取快取
- Azure 基礎：File Storage