分类：
2008-10-28 18:09:34
oracle10以前，index block split 有 90:10 和50:50的规律。Jonathan 采用90:10split rule,使B-tree索引相左倾斜，大师就是能够灵活应用。Great Guru.
How High Can You Go? by Jonathan Lewis
How High Can You Go?
by Jonathan Lewis
加上我的，dbazine改版后好看多了。
SQL> create table t1 (
2 v1 varchar2(1469)
3 );
Table created.
SQL> create index t1_i1 on t1(v1)
2 tablespace test2k;
Index created.
SQL> set serveroutput on
SQL> begin
2 for i in reverse 1..24 loop
3 dbms_output.put_line(i);
4 insert into t1 values (lpad(i,1469,'0'));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze index t1_i1 validate structure;
Index analyzed.
SQL> select height from index_stats;
HEIGHT
----------
24
SQL> insert into t1 values(lpad(0,1469,'0'));
insert into t1 values(lpad(0,1469,'0'))
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [6051], [], [], [], [], [], [], []
据说10g已经修改了index split算法，在算法和kernel code上越来越完善了
全文见SQL> alter session set events 'immediate trace name treedump level 30558';得到的b-tree树结构
----- begin tree dump
branch: 0x3000022 50331682 (0: nrow: 2, level: 23)
branch: 0x300014c 50331980 (-1: nrow: 2, level: 22)
branch: 0x3000134 50331956 (-1: nrow: 2, level: 21)
branch: 0x300011d 50331933 (-1: nrow: 2, level: 20)
branch: 0x3000107 50331911 (-1: nrow: 2, level: 19)
branch: 0x30000f2 50331890 (-1: nrow: 2, level: 18)
branch: 0x30000de 50331870 (-1: nrow: 2, level: 17)
branch: 0x30000cb 50331851 (-1: nrow: 2, level: 16)
branch: 0x30000b9 50331833 (-1: nrow: 2, level: 15)
branch: 0x30000a8 50331816 (-1: nrow: 2, level: 14)
branch: 0x3000098 50331800 (-1: nrow: 2, level: 13)
branch: 0x3000089 50331785 (-1: nrow: 2, level: 12)
branch: 0x300007b 50331771 (-1: nrow: 2, level: 11)
branch: 0x300006e 50331758 (-1: nrow: 2, level: 10)
branch: 0x3000062 50331746 (-1: nrow: 2, level: 9)
branch: 0x3000057 50331735 (-1: nrow: 2, level: 8)
branch: 0x300004d 50331725 (-1: nrow: 2, level: 7)
branch: 0x3000044 50331716 (-1: nrow: 2, level: 6)
branch: 0x300003c 50331708 (-1: nrow: 2, level: 5)
branch: 0x3000035 50331701 (-1: nrow: 2, level: 4)
branch: 0x300002f 50331695 (-1: nrow: 2, level: 3)
branch: 0x300002a 50331690 (-1: nrow: 2, level: 2)
branch: 0x3000026 50331686 (-1: nrow: 2, level: 1)
leaf: 0x3000023 50331683 (-1: nrow: 1 rrow: 1)
branch: 0x3000137 50331959 (0: nrow: 1, level: 1)
leaf: 0x300011f 50331935 (-1: nrow: 1 rrow: 1)
branch: 0x3000138 50331960 (0: nrow: 1, level: 2)
branch: 0x3000120 50331936 (-1: nrow: 1, level: 1)
leaf: 0x3000109 50331913 (-1: nrow: 1 rrow: 1)
branch: 0x3000139 50331961 (0: nrow: 1, level: 3)
branch: 0x3000121 50331937 (-1: nrow: 1, level: 2)
branch: 0x300010a 50331914 (-1: nrow: 1, level: 1)
leaf: 0x30000f4 50331892 (-1: nrow: 1 rrow: 1)
branch: 0x300013a 50331962 (0: nrow: 1, level: 4)
branch: 0x3000122 50331938 (-1: nrow: 1, level: 3)
branch: 0x300010b 50331915 (-1: nrow: 1, level: 2)
branch: 0x30000f5 50331893 (-1: nrow: 1, level: 1)
leaf: 0x30000e0 50331872 (-1: nrow: 1 rrow: 1)
branch: 0x300013b 50331963 (0: nrow: 1, level: 5)
branch: 0x3000123 50331939 (-1: nrow: 1, level: 4)
branch: 0x300010c 50331916 (-1: nrow: 1, level: 3)
branch: 0x30000f6 50331894 (-1: nrow: 1, level: 2)
branch: 0x30000e1 50331873 (-1: nrow: 1, level: 1)
leaf: 0x30000cd 50331853 (-1: nrow: 1 rrow: 1)
branch: 0x300013c 50331964 (0: nrow: 1, level: 6)
branch: 0x3000124 50331940 (-1: nrow: 1, level: 5)
branch: 0x300010d 50331917 (-1: nrow: 1, level: 4)
branch: 0x30000f7 50331895 (-1: nrow: 1, level: 3)
branch: 0x30000e2 50331874 (-1: nrow: 1, level: 2)
branch: 0x30000ce 50331854 (-1: nrow: 1, level: 1)
leaf: 0x30000bb 50331835 (-1: nrow: 1 rrow: 1)
branch: 0x300013d 50331965 (0: nrow: 1, level: 7)
branch: 0x3000125 50331941 (-1: nrow: 1, level: 6)
branch: 0x300010e 50331918 (-1: nrow: 1, level: 5)
branch: 0x30000f8 50331896 (-1: nrow: 1, level: 4)
branch: 0x30000e3 50331875 (-1: nrow: 1, level: 3)
branch: 0x30000cf 50331855 (-1: nrow: 1, level: 2)
branch: 0x30000bc 50331836 (-1: nrow: 1, level: 1)
leaf: 0x30000aa 50331818 (-1: nrow: 1 rrow: 1)
branch: 0x300013e 50331966 (0: nrow: 1, level: 8)
branch: 0x3000126 50331942 (-1: nrow: 1, level: 7)
branch: 0x300010f 50331919 (-1: nrow: 1, level: 6)
branch: 0x30000f9 50331897 (-1: nrow: 1, level: 5)
branch: 0x30000e4 50331876 (-1: nrow: 1, level: 4)
branch: 0x30000d0 50331856 (-1: nrow: 1, level: 3)
branch: 0x30000bd 50331837 (-1: nrow: 1, level: 2)
branch: 0x30000ab 50331819 (-1: nrow: 1, level: 1)
leaf: 0x300009a 50331802 (-1: nrow: 1 rrow: 1)
branch: 0x300013f 50331967 (0: nrow: 1, level: 9)
branch: 0x3000127 50331943 (-1: nrow: 1, level: 8)
branch: 0x3000110 50331920 (-1: nrow: 1, level: 7)
branch: 0x30000fa 50331898 (-1: nrow: 1, level: 6)
branch: 0x30000e5 50331877 (-1: nrow: 1, level: 5)
branch: 0x30000d1 50331857 (-1: nrow: 1, level: 4)
branch: 0x30000be 50331838 (-1: nrow: 1, level: 3)
branch: 0x30000ac 50331820 (-1: nrow: 1, level: 2)
branch: 0x300009b 50331803 (-1: nrow: 1, level: 1)
leaf: 0x300008b 50331787 (-1: nrow: 1 rrow: 1)
branch: 0x3000140 50331968 (0: nrow: 1, level: 10)
branch: 0x3000128 50331944 (-1: nrow: 1, level: 9)
branch: 0x3000111 50331921 (-1: nrow: 1, level: 8)
branch: 0x30000fb 50331899 (-1: nrow: 1, level: 7)
branch: 0x30000e6 50331878 (-1: nrow: 1, level: 6)
branch: 0x30000d2 50331858 (-1: nrow: 1, level: 5)
branch: 0x30000bf 50331839 (-1: nrow: 1, level: 4)
branch: 0x30000ad 50331821 (-1: nrow: 1, level: 3)
branch: 0x300009c 50331804 (-1: nrow: 1, level: 2)
branch: 0x300008c 50331788 (-1: nrow: 1, level: 1)
leaf: 0x300007d 50331773 (-1: nrow: 1 rrow: 1)
branch: 0x3000141 50331969 (0: nrow: 1, level: 11)
branch: 0x3000129 50331945 (-1: nrow: 1, level: 10)
branch: 0x3000112 50331922 (-1: nrow: 1, level: 9)
branch: 0x30000fc 50331900 (-1: nrow: 1, level: 8)
branch: 0x30000e7 50331879 (-1: nrow: 1, level: 7)
branch: 0x30000d3 50331859 (-1: nrow: 1, level: 6)
branch: 0x30000c0 50331840 (-1: nrow: 1, level: 5)
branch: 0x30000ae 50331822 (-1: nrow: 1, level: 4)
branch: 0x300009d 50331805 (-1: nrow: 1, level: 3)
branch: 0x300008d 50331789 (-1: nrow: 1, level: 2)
branch: 0x300007e 50331774 (-1: nrow: 1, level: 1)
leaf: 0x3000070 50331760 (-1: nrow: 1 rrow: 1)
branch: 0x3000142 50331970 (0: nrow: 1, level: 12)
branch: 0x300012a 50331946 (-1: nrow: 1, level: 11)
branch: 0x3000113 50331923 (-1: nrow: 1, level: 10)
branch: 0x30000fd 50331901 (-1: nrow: 1, level: 9)
branch: 0x30000e8 50331880 (-1: nrow: 1, level: 8)
branch: 0x30000d4 50331860 (-1: nrow: 1, level: 7)
branch: 0x30000c1 50331841 (-1: nrow: 1, level: 6)
branch: 0x30000af 50331823 (-1: nrow: 1, level: 5)
branch: 0x300009e 50331806 (-1: nrow: 1, level: 4)
branch: 0x300008e 50331790 (-1: nrow: 1, level: 3)
branch: 0x300007f 50331775 (-1: nrow: 1, level: 2)
branch: 0x3000071 50331761 (-1: nrow: 1, level: 1)
leaf: 0x3000064 50331748 (-1: nrow: 1 rrow: 1)
branch: 0x3000143 50331971 (0: nrow: 1, level: 13)
branch: 0x300012b 50331947 (-1: nrow: 1, level: 12)
branch: 0x3000114 50331924 (-1: nrow: 1, level: 11)
branch: 0x30000fe 50331902 (-1: nrow: 1, level: 10)
branch: 0x30000e9 50331881 (-1: nrow: 1, level: 9)
branch: 0x30000d5 50331861 (-1: nrow: 1, level: 8)
branch: 0x30000c2 50331842 (-1: nrow: 1, level: 7)
branch: 0x30000b0 50331824 (-1: nrow: 1, level: 6)
branch: 0x300009f 50331807 (-1: nrow: 1, level: 5)
branch: 0x300008f 50331791 (-1: nrow: 1, level: 4)
branch: 0x3000080 50331776 (-1: nrow: 1, level: 3)
branch: 0x3000072 50331762 (-1: nrow: 1, level: 2)
branch: 0x3000065 50331749 (-1: nrow: 1, level: 1)
leaf: 0x3000059 50331737 (-1: nrow: 1 rrow: 1)
branch: 0x3000144 50331972 (0: nrow: 1, level: 14)
branch: 0x300012c 50331948 (-1: nrow: 1, level: 13)
branch: 0x3000115 50331925 (-1: nrow: 1, level: 12)
branch: 0x30000ff 50331903 (-1: nrow: 1, level: 11)
branch: 0x30000ea 50331882 (-1: nrow: 1, level: 10)
branch: 0x30000d6 50331862 (-1: nrow: 1, level: 9)
branch: 0x30000c3 50331843 (-1: nrow: 1, level: 8)
branch: 0x30000b1 50331825 (-1: nrow: 1, level: 7)
branch: 0x30000a0 50331808 (-1: nrow: 1, level: 6)
branch: 0x3000090 50331792 (-1: nrow: 1, level: 5)
branch: 0x3000081 50331777 (-1: nrow: 1, level: 4)
branch: 0x3000073 50331763 (-1: nrow: 1, level: 3)
branch: 0x3000066 50331750 (-1: nrow: 1, level: 2)
branch: 0x300005a 50331738 (-1: nrow: 1, level: 1)
leaf: 0x300004f 50331727 (-1: nrow: 1 rrow: 1)
branch: 0x3000145 50331973 (0: nrow: 1, level: 15)
branch: 0x300012d 50331949 (-1: nrow: 1, level: 14)
branch: 0x3000116 50331926 (-1: nrow: 1, level: 13)
branch: 0x3000100 50331904 (-1: nrow: 1, level: 12)
branch: 0x30000eb 50331883 (-1: nrow: 1, level: 11)
branch: 0x30000d7 50331863 (-1: nrow: 1, level: 10)
branch: 0x30000c4 50331844 (-1: nrow: 1, level: 9)
branch: 0x30000b2 50331826 (-1: nrow: 1, level: 8)
branch: 0x30000a1 50331809 (-1: nrow: 1, level: 7)
branch: 0x3000091 50331793 (-1: nrow: 1, level: 6)
branch: 0x3000082 50331778 (-1: nrow: 1, level: 5)
branch: 0x3000074 50331764 (-1: nrow: 1, level: 4)
branch: 0x3000067 50331751 (-1: nrow: 1, level: 3)
branch: 0x300005b 50331739 (-1: nrow: 1, level: 2)
branch: 0x3000050 50331728 (-1: nrow: 1, level: 1)
leaf: 0x3000046 50331718 (-1: nrow: 1 rrow: 1)
branch: 0x3000146 50331974 (0: nrow: 1, level: 16)
branch: 0x300012e 50331950 (-1: nrow: 1, level: 15)
branch: 0x3000117 50331927 (-1: nrow: 1, level: 14)
branch: 0x3000101 50331905 (-1: nrow: 1, level: 13)
branch: 0x30000ec 50331884 (-1: nrow: 1, level: 12)
branch: 0x30000d8 50331864 (-1: nrow: 1, level: 11)
branch: 0x30000c5 50331845 (-1: nrow: 1, level: 10)
branch: 0x30000b3 50331827 (-1: nrow: 1, level: 9)
branch: 0x30000a2 50331810 (-1: nrow: 1, level: 8)
branch: 0x3000092 50331794 (-1: nrow: 1, level: 7)
branch: 0x3000083 50331779 (-1: nrow: 1, level: 6)
branch: 0x3000075 50331765 (-1: nrow: 1, level: 5)
branch: 0x3000068 50331752 (-1: nrow: 1, level: 4)
branch: 0x300005c 50331740 (-1: nrow: 1, level: 3)
branch: 0x3000051 50331729 (-1: nrow: 1, level: 2)
branch: 0x3000047 50331719 (-1: nrow: 1, level: 1)
leaf: 0x300003e 50331710 (-1: nrow: 1 rrow: 1)
branch: 0x3000147 50331975 (0: nrow: 1, level: 17)
branch: 0x300012f 50331951 (-1: nrow: 1, level: 16)
branch: 0x3000118 50331928 (-1: nrow: 1, level: 15)
branch: 0x3000102 50331906 (-1: nrow: 1, level: 14)
branch: 0x30000ed 50331885 (-1: nrow: 1, level: 13)
branch: 0x30000d9 50331865 (-1: nrow: 1, level: 12)
branch: 0x30000c6 50331846 (-1: nrow: 1, level: 11)
