排球

oracle index split规则_a

2020-01-16 23:54:50来源:励志吧0次阅读

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

http://www.dbazine.com/jlewis22.shtml

加上我的测试,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)

查看本文来源

老年性关节炎疼怎么办
静脉炎的治疗
月经量多可以吃什么调理
术后ED用药治疗效果好吗
分享到: