Friday, June 11, 2004
Recent discussions on news://comp.databases.oracle.server led me to draw up a little test that - hopefully anyone - can run that tests the assertion that repeated inserts into the same part of an index lead that part of the index to increase in depth, when the rest of the index doesn't.
The methodology is quite simple, create a small single column table with an even spread of values. Take a treedump to display the index structure graphically, then insert a large number of identical values, take a second treedump and compare the structure. You can play with the number of values in the table to start with, and the number of identicals to insert to your hearts content. The script is here.
It might be worth just introducing you to a (9i) treedump. It looks like this
As you will hopefully see (if the formatting isn't munged up) you get a nice tree like structure listing the branch and leaf blocks that comprise the index. (and some information like the number of rows in them.
the syntax to run this command is
My conclusions - no matter how hard you try oracle indexes are
always height balanced. Give it a whirl yourself and see what happens.
0 Comments
The methodology is quite simple, create a small single column table with an even spread of values. Take a treedump to display the index structure graphically, then insert a large number of identical values, take a second treedump and compare the structure. You can play with the number of values in the table to start with, and the number of identicals to insert to your hearts content. The script is here.
It might be worth just introducing you to a (9i) treedump. It looks like this
*** 2004-06-09 08:44:00.794
----- begin tree dump
branch: 0x240128c 37753484 (0: nrow: 7, level: 2)
branch: 0x24012e5 37753573 (-1: nrow: 29, level: 1)
leaf: 0x240128d 37753485 (-1: nrow: 19 rrow: 19)
leaf: 0x24012b4 37753524 (0: nrow: 19 rrow: 19)
leaf: 0x24012e2 37753570 (1: nrow: 13 rrow: 13)
leaf: 0x2401320 37753632 (2: nrow: 19 rrow: 19)
leaf: 0x2401345 37753669 (3: nrow: 19 rrow: 19)
leaf: 0x24013a8 37753768 (4: nrow: 17 rrow: 17)
leaf: 0x2401294 37753492 (5: nrow: 18 rrow: 18)
leaf: 0x24012e1 37753569 (6: nrow: 19 rrow: 19)
leaf: 0x240130a 37753610 (7: nrow: 19 rrow: 19)
leaf: 0x2401347 37753671 (8: nrow: 19 rrow: 19)
leaf: 0x240138c 37753740 (9: nrow: 19 rrow: 19)
leaf: 0x24012b6 37753526 (10: nrow: 19 rrow: 19)
leaf: 0x24012c5 37753541 (11: nrow: 19 rrow: 19)
leaf: 0x24012f4 37753588 (12: nrow: 13 rrow: 13)
leaf: 0x240132c 37753644 (13: nrow: 19 rrow: 19)
leaf: 0x240134b 37753675 (14: nrow: 19 rrow: 19)
leaf: 0x24013c0 37753792 (15: nrow: 11 rrow: 11)
leaf: 0x2401298 37753496 (16: nrow: 19 rrow: 19)
leaf: 0x24012d5 37753557 (17: nrow: 19 rrow: 19)
leaf: 0x24012f6 37753590 (18: nrow: 13 rrow: 13)
leaf: 0x240132e 37753646 (19: nrow: 19 rrow: 19)
leaf: 0x2401397 37753751 (20: nrow: 18 rrow: 18)
leaf: 0x24013c4 37753796 (21: nrow: 12 rrow: 12)
leaf: 0x24012a6 37753510 (22: nrow: 19 rrow: 19)
leaf: 0x24012d6 37753558 (23: nrow: 19 rrow: 19)
leaf: 0x24012f5 37753589 (24: nrow: 13 rrow: 13)
leaf: 0x240132d 37753645 (25: nrow: 19 rrow: 19)
leaf: 0x240139f 37753759 (26: nrow: 19 rrow: 19)
leaf: 0x24013c8 37753800 (27: nrow: 11 rrow: 11)
branch: 0x240134c 37753676 (0: nrow: 24, level: 1)
leaf: 0x2401295 37753493 (-1: nrow: 19 rrow: 19)
leaf: 0x24012d7 37753559 (0: nrow: 19 rrow: 19)
leaf: 0x2401305 37753605 (1: nrow: 13 rrow: 13)
leaf: 0x2401335 37753653 (2: nrow: 19 rrow: 19)
< omitted >
branch: 0x2401330 37753648 (5: nrow: 29, level: 1)
leaf: 0x2401290 37753488 (-1: nrow: 19 rrow: 19)
leaf: 0x24012c4 37753540 (0: nrow: 19 rrow: 19)
leaf: 0x24012f2 37753586 (1: nrow: 13 rrow: 13)
leaf: 0x240132f 37753647 (2: nrow: 19 rrow: 19)
leaf: 0x240134a 37753674 (3: nrow: 19 rrow: 19)
leaf: 0x24013bc 37753788 (4: nrow: 16 rrow: 16)
leaf: 0x24012a4 37753508 (5: nrow: 19 rrow: 19)
leaf: 0x24012e7 37753575 (6: nrow: 19 rrow: 19)
leaf: 0x240131d 37753629 (7: nrow: 19 rrow: 19)
leaf: 0x2401346 37753670 (8: nrow: 19 rrow: 19)
leaf: 0x24013a0 37753760 (9: nrow: 19 rrow: 19)
leaf: 0x24012b3 37753523 (10: nrow: 19 rrow: 19)
leaf: 0x24012da 37753562 (11: nrow: 19 rrow: 19)
leaf: 0x2401310 37753616 (12: nrow: 13 rrow: 13)
leaf: 0x2401334 37753652 (13: nrow: 19 rrow: 19)
leaf: 0x24013bb 37753787 (14: nrow: 19 rrow: 19)
leaf: 0x24013b5 37753781 (15: nrow: 11 rrow: 11)
leaf: 0x2401292 37753490 (16: nrow: 19 rrow: 19)
leaf: 0x24012c6 37753542 (17: nrow: 19 rrow: 19)
leaf: 0x24012f3 37753587 (18: nrow: 12 rrow: 12)
leaf: 0x240132a 37753642 (19: nrow: 19 rrow: 19)
leaf: 0x240134e 37753678 (20: nrow: 19 rrow: 19)
leaf: 0x24013ac 37753772 (21: nrow: 17 rrow: 17)
leaf: 0x2401291 37753489 (22: nrow: 19 rrow: 19)
leaf: 0x24012e8 37753576 (23: nrow: 19 rrow: 19)
leaf: 0x240131f 37753631 (24: nrow: 14 rrow: 14)
leaf: 0x240131e 37753630 (25: nrow: 19 rrow: 19)
leaf: 0x24013c7 37753799 (26: nrow: 14 rrow: 14)
leaf: 0x24013c3 37753795 (27: nrow: 10 rrow: 10)
----- end tree dump
As you will hopefully see (if the formatting isn't munged up) you get a nice tree like structure listing the branch and leaf blocks that comprise the index. (and some information like the number of rows in them.
the syntax to run this command is
alter session set events 'immediate trace name treedump level <objectid> ';
My conclusions - no matter how hard you try oracle indexes are
always height balanced. Give it a whirl yourself and see what happens.
0 Comments:
Post a Comment
