oracleindexslit规则节能

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_t_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: 0x (0: nrow: 2, level: 23)

branch: 0x300014c (-1: nrow: 2, level: 22)

branch: 0x (-1: nrow: 2, level: 21)

branch: 0x300011d (-1: nrow: 2, level: 20)

branch: 0x (-1: nrow: 2, level: 19)

branch: 0x30000f2 (-1: nrow: 2, level: 18)

branch: 0x30000de (-1: nrow: 2, level: 17)

branch: 0x30000cb (-1: nrow: 2, level: 16)

branch: 0x30000b9 (-1: nrow: 2, level: 15)

branch: 0x30000a8 (-1: nrow: 2, level: 14)

branch: 0x (-1: nrow: 2, level: 13)

branch: 0x (-1: nrow: 2, level: 12)

branch: 0x300007b (-1: nrow: 2, level: 11)

branch: 0x300006e (-1: nrow: 2, level: 10)

branch: 0x (-1: nrow: 2, level: 9)

branch: 0x (-1: nrow: 2, level: 8)

branch: 0x300004d (-1: nrow: 2, level: 7)

branch: 0x (-1: nrow: 2, level: 6)

branch: 0x300003c (-1: nrow: 2, level: 5)

branch: 0x (-1: nrow: 2, level: 4)

branch: 0当务之急x300002f (-1: nrow: 2, level: 3)

branch: 0x300002a (-1: nrow: 2, level: 2)

branch: 0x (-1: nrow: 2, level: 1)

leaf: 0x (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 1)

leaf: 0x300011f (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 2)

branch: 0x (-1: nrow: 1, level: 1)

leaf: 0x (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 3)

branch: 0x (-1: nrow: 1, level: 2)

branch: 0x300010a (-1: nrow: 1, level: 1)

leaf: 0x30000f4 (-1: nrow: 1 rrow: 1)

branch: 0x300013a (0: nrow: 1, level: 4)

branch: 0x (-1: nrow: 1, level: 3)

branch: 0x300010b (-1: nrow: 1, level: 2)

branch: 0x30000f5 (-1: nrow: 1, level: 1)

leaf: 0x30000e0 (-1: nrow: 1 rrow: 1)

branch: 0x300013b (0: nrow: 1, level: 5)

branch: 0x (-1: nrow: 1, level: 4)

branch: 0x300010c (-1: nrow: 1, level: 3)

branch: 0x30000f6 (-1: nrow: 1, level: 2)

branch: 0x30000e1 (-1: nrow: 1, level: 1)

leaf: 0x30000cd (-1: nrow: 1 rrow: 1)

branch: 0x300013c (0: nrow: 1, level: 6)

branch: 0x (-1: nrow: 1, level: 5)

branch: 0x300010d (-1: nrow: 1, level: 4)

branch: 0x30000f7 (-1: nrow: 1, level: 3)

branch: 0x30000e2 (-1: nrow: 1, level: 2)

branch: 0x30000ce (-1: nrow: 1, level: 1)

leaf: 0x30000bb (-1: nrow: 1 rrow: 1)

branch: 0x300013d (0: nrow: 1, level: 7)

branch: 0x (-1: nrow: 1, level: 6)

branch: 0x300010e (-1: nrow: 1, level: 5)

branch: 0x30000f8 (-1: nrow: 1, level: 4)

branch: 0x30000e3 (-1: nrow: 1, level: 3)

branch: 0x30000cf (-1: nrow: 1, level: 2)

branch: 0x30000bc (-1: nrow: 1, level: 1)

leaf: 0x30000aa (-1: nrow: 1 rrow: 1)

branch: 0x300013e (0: nrow: 1, level: 8)

branch: 0x (-1: nrow: 1, level: 7)

branch: 0x300010f (-1: nrow: 1, level: 6)

branch: 0x30000f9 (-1: nrow: 1, level: 5)

branch: 0x30000e4 (-1: nrow: 1, level: 4)

branch: 0x30000d0 (-1: nrow: 1, level: 3)

branch: 0x30000bd (-1: nrow: 1, level: 2)

branch: 0x30000ab (-1: nrow: 1, level: 1)

leaf: 0x300009a (-1: nrow: 1 rrow: 1)

branch: 0x300013f (0: nrow: 1, level: 9)

branch: 0x (-1: nrow: 1, level: 8)

branch: 0x (-1: nrow: 1, level: 7)

branch: 0x30000fa (-1: nrow: 1, level: 6)

branch: 0x30000e5 (-1: nrow: 1, level: 5)

branch: 0x30000d1 (-1: nrow: 1, level: 4)

branch: 0x30000be (-1: nrow: 1, level: 3)

branch: 0x30000ac (-1: nrow: 1, level: 2)

branch: 0x300009b (-1: nrow: 1, level: 1)

leaf: 0x300008b (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 10)

branch: 0x (-1: nrow: 1, level: 9)

branch: 0x (-1: nrow: 1, level: 8)

branch: 0x30000fb (-1: nrow: 1, level: 7)

branch: 0x30000e6 (-1: nrow: 1, level: 6)

branch: 0x30000d2 (-1: nrow: 1, level: 5)

branch: 0x30000bf (-1: nrow: 1, level: 4)

branch: 0x30000ad (-1: nrow: 1, level: 3)

branch: 0x300009c (-1: nrow: 1, level: 2)

branch: 0x300008c (-1: nrow: 1, level: 1)

leaf: 0x300007d (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 11)

branch: 0x (-1: nrow: 1, level: 10)

branch: 0x (-1: nrow: 1, level: 9)

branch: 0x30000fc (-1: nrow: 1, level: 8)

branch: 0x30000e7 (-1: nrow: 1, level: 7)

branch: 0x30000d3 (-1: nrow: 1, level: 6)

branch: 0x30000c0 (-1: nrow: 1, level: 5)

branch: 0x30000ae (-1: nrow: 1, level: 4)

branch: 0x300009d (-1: nrow: 1, level: 3)

branch: 0x300008d (-1: nrow: 1, level: 2)

branch: 0x300007e (-1: nrow: 1, level: 1)

leaf: 0x (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 12)

branch: 0x300012a (-1: nrow: 1, level: 11)

branch: 0x (-1: nrow: 1, level: 10)

branch: 0x30000fd (-1: nrow: 1, level: 9)

branch: 0x30000e8 (-1: nrow: 1, level: 8)

branch: 0x30000d4 (-1: nrow: 1, level: 7)

branch: 0x30000c1 (-1: nrow: 1, level: 6)

branch: 0x30000af (-1: nrow: 1, level: 5)

branch: 0x300009e (-1: nrow: 1, level: 4)

branch: 0x300008e (-1: nrow: 1, level: 3)

branch: 0x300007f (-1: nrow: 1, level: 2)

branch: 0x (-1: nrow: 1, level: 1)

leaf: 0x (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 13)

branch: 0x300012b (-1: nrow: 1, level: 12)

branch: 0x (-1: nrow: 1, level: 11)

branch: 0x30000fe (-1: nrow: 1, level: 10)

branch: 0x30000e9 (-1: nrow: 1, level: 9)

branch: 0x30000d5 (-1: nrow: 1, level: 8)

branch: 0x30000c2 (-1: nrow: 1, level: 7)

branch: 0x30000b0 (-1: nrow: 1, level: 6)

branch: 0x300009f (-1: nrow: 1, level: 5)

branch: 0x300008f (-1: nrow: 1, level: 4)

branch: 0x (-1: nrow: 1, level: 3)

branch: 0x (-1: nrow: 1, level: 2)

branch: 0x (-1: nrow: 1, level: 1)

leaf: 0x (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 14)

branch: 0x300012c (-1: nrow: 1, level: 13)

branch: 0x (-1: nrow: 1, level: 12)

branch: 0x30000ff (-1: nrow: 1, level: 11)

branch: 0x30000ea (-1: nrow: 1, level: 10)

branch: 0x30000d6 (-1: nrow: 1, level: 9)

branch: 0x30000c3 (-1: nrow: 1, level: 8)

branch: 0x30000b1 (-1: nrow: 1, level: 7)

branch: 0x30000a0 (-1: nrow: 1, level: 6)

branch: 0x (-1: nrow: 1, level: 5)

branch: 0x (-1: nrow: 1, level: 4)

branch: 0x (-1: nrow: 1, level: 3)

branch: 0x (-1: nrow: 1, level: 2)

branch: 0x300005a (-1: nrow: 1, level: 1)

leaf: 0x300004f (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 15)

branch: 0x300012d (-1: nrow: 1, level: 14)

branch: 0x (-1: nrow: 1, level: 13)

branch: 0x (-1: nrow: 1, level: 12)

branch: 0x30000eb (-1: nrow: 1, level: 11)

branch: 0x30000d7 (-1: nrow: 1, level: 10)

branch: 0x30000c4 (-1: nrow: 1, level: 9)

branch: 0x30000b2 (-1: nrow: 1, level: 8)

branch: 0x30000a1 (-1: nrow: 1, level: 7)

branch: 0x (-1: nrow: 1, level: 6)

branch: 0x (-1: nrow: 1, level: 5)

branch: 0x (-1: nrow: 1, level: 4)

branch: 0x (-1: nrow: 1, level: 3)

branch: 0x300005b (-电子商务在经历了拼低价、抢货源、砸广告和盖仓库之后1: nrow: 1, level: 2)

branch: 0x (-1: nrow: 1, level: 1)

leaf: 0x (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 16)

branch: 0x300012e (-1: nrow: 1, level: 15)

branch: 0x (-1: nrow: 1, level: 14)

branch: 0x (-1: nrow: 1, level: 13)

branch: 0x30000ec (-1: nrow: 1, level: 12)

branch: 0x30000d8 (-1: nrow: 1, level: 11)

branch: 0x30000c5 (-1: nrow: 1, level: 10)

branch: 0x30000b3 (-1: nrow: 1, level: 9)

branch: 0x30000a2 (-1: nrow: 1, level: 8)

branch: 0x (-1: nrow: 1, level: 7)

branch: 0x (-1: nrow: 1, level: 6)

branch: 0x (-1: nrow: 1, level: 5)

branch: 0x (-1: nrow: 1, level: 4)

branch: 0x300005c (-1: nrow: 1, level: 3)

branch: 0x (-1: nrow: 1, level: 2)

branch: 0x (-1: nrow: 1, level: 1)

leaf: 0x300003e (-1: nrow: 1 rrow: 1)

branch: 0x (0: nrow: 1, level: 17)

branch: 0x300012f (-1: nrow: 1, level: 16)

branch: 0x (-1: nrow: 1, level: 15)

branch: 0x (-1: nrow: 1, level: 14)

branch: 0x30000ed (-1: nrow: 1, level: 13)

branch: 0x30000d9 (-1: nrow: 1, level: 12)

branch: 0x30000c6 (-1: nrow: 1, level: 11)

查看本文来源

河源白癜风专业治疗医院
西藏整形美容
三明牛皮癣医院那个好