[Oracle]让B*Tree索引一直向左走

ZDNet软件频道 时间:2008-08-06 作者:盖国强 | 开发者在线 我要评论()
本文关键词:盖国强 索引 Oracle Oracle
Jonathan Lewis在他的文章中,奇妙的利用B*Tree索引分裂的原则,通过24条记录就成功的构造出了24层的索引结构,从而验证了B*Tree索引的最大允许高度。
Jonathan Lewis在他的文章中,奇妙的利用B*Tree索引分裂的原则,通过24条记录就成功的构造出了24层的索引结构,从而验证了B*Tree索引的最大允许高度。

通常我们按照升序创建索引条目,索引会一直向右扩展;可是如果反过来,索引将会向左扩展。
这样,除了最左面一个Branch会有两个Leaf节点以外,所有Branch就只有一个Leaf节点。

我采用Oracle9.2.0.4,测试表空间Block_Size大小为2K.
背景文章:
1.B*Tree索引能有多高?--推荐Jonathan Lewis 的一篇经典文章
2.如何转储B*Tree索引的分枝结构
我们来看一下这个结构:
[Oracle@jumper udump]$ cat t.sql
drop table t1;

create table t1 (
        v1 varchar2(1469));

create index t1_i1 on t1(v1);

begin   for i in reverse 1..3
        loop
                dbms_output.put_line(i);
                insert into t1 values (lpad(i,1469,'0'));
        end loop;
end;
/
[Oracle@jumper udump]$ exit
exit

SQL> set serveroutput on
SQL> @t

Table dropped.


Table created.


Index created.

3
2
1

PL/SQL procedure successfully completed.

SQL> select object_id from dba_objects where object_name=upper('t1_i1') and wner='TEST';

 OBJECT_ID
----------
      7008

SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 7008';

Session altered.



然后我们检查trace文件,得到如下信息:
----- begin tree dump
branch: 0x2400042 37748802 (0: nrow: 2, level: 2)
   branch: 0x2400046 37748806 (-1: nrow: 2, level: 1)
      leaf: 0x2400043 37748803 (-1: nrow: 1 rrow: 1)
      leaf: 0x2400045 37748805 (0: nrow: 1 rrow: 1)
   branch: 0x2400047 37748807 (0: nrow: 1, level: 1)
      leaf: 0x2400044 37748804 (-1: nrow: 1 rrow: 1)
----- end tree dump

注意只有左侧一个Branch会有两个Leaf节点以外,其他Branch就只有一个Leaf节点。
扩展到多层也是如此。

我们再看一下五个索引条目的情况:
SQL> @t

Table dropped.


Table created.


Index created.

5
4
3
2
1

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 7010';

Session altered.


然后我们检查trace文件,得到如下信息:
----- begin tree dump
branch: 0x2400042 37748802 (0: nrow: 2, level: 4)
   branch: 0x240004f 37748815 (-1: nrow: 2, level: 3)
      branch: 0x240004a 37748810 (-1: nrow: 2, level: 2)
         branch: 0x2400046 37748806 (-1: nrow: 2, level: 1)
            leaf: 0x2400043 37748803 (-1: nrow: 1 rrow: 1)
            leaf: 0x240004c 37748812 (0: nrow: 1 rrow: 1)
         branch: 0x240004d 37748813 (0: nrow: 1, level: 1)
            leaf: 0x2400048 37748808 (-1: nrow: 1 rrow: 1)
      branch: 0x240004e 37748814 (0: nrow: 1, level: 2)
         branch: 0x2400049 37748809 (-1: nrow: 1, level: 1)
            leaf: 0x2400045 37748805 (-1: nrow: 1 rrow: 1)
   branch: 0x2400050 37748816 (0: nrow: 1, level: 3)
      branch: 0x240004b 37748811 (-1: nrow: 1, level: 2)
         branch: 0x2400047 37748807 (-1: nrow: 1, level: 1)
            leaf: 0x2400044 37748804 (-1: nrow: 1 rrow: 1)
----- end tree dump


我们看一下升序插入的情况:
[Oracle@jumper udump]$ cat tt.sql
drop table t1;

create table t1 (
        v1 varchar2(1469));

create index t1_i1 on t1(v1);

begin   for i in 1..5
        loop
                dbms_output.put_line(i);
                insert into t1 values (lpad(i,1469,'0'));
        end loop;
end;
/
[Oracle@jumper udump]$ exit
exit

SQL> @tt

Table dropped.


Table created.


Index created.

1
2
3
4
5

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 7012';


我们看一下索引结构:
Session altered.
----- begin tree dump
branch: 0x2400042 37748802 (0: nrow: 2, level: 3)
   branch: 0x240004b 37748811 (-1: nrow: 2, level: 2)
      branch: 0x2400046 37748806 (-1: nrow: 2, level: 1)
         leaf: 0x2400043 37748803 (-1: nrow: 1 rrow: 1)
         leaf: 0x2400044 37748804 (0: nrow: 1 rrow: 1)
      branch: 0x2400047 37748807 (0: nrow: 2, level: 1)
         leaf: 0x2400045 37748805 (-1: nrow: 1 rrow: 1)
         leaf: 0x2400048 37748808 (0: nrow: 1 rrow: 1)
   branch: 0x240004c 37748812 (0: nrow: 1, level: 2)
      branch: 0x240004a 37748810 (-1: nrow: 1, level: 1)
         leaf: 0x2400049 37748809 (-1: nrow: 1 rrow: 1)
----- end tree dump


注意,此时存在多个leaf的Branch有两个,这就是我们常见的索引结构。

Ok,我们来看一下24层是什么样的架构,首先看一下Jonathan Lewis的这张图,这张图放大以后就是这个特殊的索引:
jlewis22-1.gif
我们来看一下Lewis构建的索引结构:
SQL> @t

Table dropped.


Table created.


Index created.

24
23
22
21
20
19
18
17
16
15
14
13
12
11
10
9
8
7
6
5
4
3
2
1

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 7014';

Session altered.



鉴于这是一个非常奇妙的索引结构,所以我这里记录了全部的结构跟踪文件:
----- begin tree dump
branch: 0x2400042 37748802 (0: nrow: 2, level: 23)
   branch: 0x240016c 37749100 (-1: nrow: 2, level: 22)
      branch: 0x2400154 37749076 (-1: nrow: 2, level: 21)
         branch: 0x240013d 37749053 (-1: nrow: 2, level: 20)
            branch: 0x2400127 37749031 (-1: nrow: 2, level: 19)
               branch: 0x2400112 37749010 (-1: nrow: 2, level: 18)
                  branch: 0x24000fe 37748990 (-1: nrow: 2, level: 17)
                     branch: 0x24000eb 37748971 (-1: nrow: 2, level: 16)
                        branch: 0x24000d9 37748953 (-1: nrow: 2, level: 15)
                           branch: 0x24000c8 37748936 (-1: nrow: 2, level: 14)
                              branch: 0x24000b8 37748920 (-1: nrow: 2, level: 13)
                                 branch: 0x24000a9 37748905 (-1: nrow: 2, level: 12)
                                    branch: 0x240009b 37748891 (-1: nrow: 2, level: 11)
                                       branch: 0x240008e 37748878 (-1: nrow: 2, level: 10)
                                          branch: 0x2400082 37748866 (-1: nrow: 2, level: 9)
                                             branch: 0x2400077 37748855 (-1: nrow: 2, level: 8)
                                                branch: 0x240006d 37748845 (-1: nrow: 2, level: 7)
                                                   branch: 0x2400064 37748836 (-1: nrow: 2, level: 6)
                                                      branch: 0x240005c 37748828 (-1: nrow: 2, level: 5)
                                                         branch: 0x2400055 37748821 (-1: nrow: 2, level: 4)
                                                            branch: 0x240004f 37748815 (-1: nrow: 2, level: 3)
                                                               branch: 0x240004a 37748810 (-1: nrow: 2, level: 2)
                                                                  branch: 0x2400046 37748806 (-1: nrow: 2, level: 1)
                                                                     leaf: 0x2400043 37748803 (-1: nrow: 1 rrow: 1)
                                                                     leaf: 0x2400156 37749078 (0: nrow: 1 rrow: 1)
                                                                  branch: 0x2400157 37749079 (0: nrow: 1, level: 1)
                                                                     leaf: 0x240013f 37749055 (-1: nrow: 1 rrow: 1)
                                                               branch: 0x2400158 37749080 (0: nrow: 1, level: 2)
                                                                  branch: 0x2400140 37749056 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400129 37749033 (-1: nrow: 1 rrow: 1)
                                                            branch: 0x2400159 37749081 (0: nrow: 1, level: 3)
                                                               branch: 0x2400141 37749057 (-1: nrow: 1, level: 2)
                                                                  branch: 0x240012a 37749034 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400114 37749012 (-1: nrow: 1 rrow: 1)
                                                         branch: 0x240015a 37749082 (0: nrow: 1, level: 4)
                                                            branch: 0x2400142 37749058 (-1: nrow: 1, level: 3)
                                                               branch: 0x240012b 37749035 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400115 37749013 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400100 37748992 (-1: nrow: 1 rrow: 1)
                                                      branch: 0x240015b 37749083 (0: nrow: 1, level: 5)
                                                         branch: 0x2400143 37749059 (-1: nrow: 1, level: 4)
                                                            branch: 0x240012c 37749036 (-1: nrow: 1, level: 3)
                                                               branch: 0x2400116 37749014 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400101 37748993 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x24000ed 37748973 (-1: nrow: 1 rrow: 1)
                                                   branch: 0x240015c 37749084 (0: nrow: 1, level: 6)
                                                      branch: 0x2400144 37749060 (-1: nrow: 1, level: 5)
                                                         branch: 0x240012d 37749037 (-1: nrow: 1, level: 4)
                                                            branch: 0x2400117 37749015 (-1: nrow: 1, level: 3)
                                                               branch: 0x2400102 37748994 (-1: nrow: 1, level: 2)
                                                                  branch: 0x24000ee 37748974 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x24000db 37748955 (-1: nrow: 1 rrow: 1)
                                                branch: 0x240015d 37749085 (0: nrow: 1, level: 7)
                                                   branch: 0x2400145 37749061 (-1: nrow: 1, level: 6)
                                                      branch: 0x240012e 37749038 (-1: nrow: 1, level: 5)
                                                         branch: 0x2400118 37749016 (-1: nrow: 1, level: 4)
                                                            branch: 0x2400103 37748995 (-1: nrow: 1, level: 3)
                                                               branch: 0x24000ef 37748975 (-1: nrow: 1, level: 2)
                                                                  branch: 0x24000dc 37748956 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x24000ca 37748938 (-1: nrow: 1 rrow: 1)
                                             branch: 0x240015e 37749086 (0: nrow: 1, level: 8)
                                                branch: 0x2400146 37749062 (-1: nrow: 1, level: 7)
                                                   branch: 0x240012f 37749039 (-1: nrow: 1, level: 6)
                                                      branch: 0x2400119 37749017 (-1: nrow: 1, level: 5)
                                                         branch: 0x2400104 37748996 (-1: nrow: 1, level: 4)
                                                            branch: 0x24000f0 37748976 (-1: nrow: 1, level: 3)
                                                               branch: 0x24000dd 37748957 (-1: nrow: 1, level: 2)
                                                                  branch: 0x24000cb 37748939 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x24000ba 37748922 (-1: nrow: 1 rrow: 1)
                                          branch: 0x240015f 37749087 (0: nrow: 1, level: 9)
                                             branch: 0x2400147 37749063 (-1: nrow: 1, level: 8)
                                                branch: 0x2400130 37749040 (-1: nrow: 1, level: 7)
                                                   branch: 0x240011a 37749018 (-1: nrow: 1, level: 6)
                                                      branch: 0x2400105 37748997 (-1: nrow: 1, level: 5)
                                                         branch: 0x24000f1 37748977 (-1: nrow: 1, level: 4)
                                                            branch: 0x24000de 37748958 (-1: nrow: 1, level: 3)
                                                               branch: 0x24000cc 37748940 (-1: nrow: 1, level: 2)
                                                                  branch: 0x24000bb 37748923 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x24000ab 37748907 (-1: nrow: 1 rrow: 1)
                                       branch: 0x2400160 37749088 (0: nrow: 1, level: 10)
                                          branch: 0x2400148 37749064 (-1: nrow: 1, level: 9)
                                             branch: 0x2400131 37749041 (-1: nrow: 1, level: 8)
                                                branch: 0x240011b 37749019 (-1: nrow: 1, level: 7)
                                                   branch: 0x2400106 37748998 (-1: nrow: 1, level: 6)
                                                      branch: 0x24000f2 37748978 (-1: nrow: 1, level: 5)
                                                         branch: 0x24000df 37748959 (-1: nrow: 1, level: 4)
                                                            branch: 0x24000cd 37748941 (-1: nrow: 1, level: 3)
                                                               branch: 0x24000bc 37748924 (-1: nrow: 1, level: 2)
                                                                  branch: 0x24000ac 37748908 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x240009d 37748893 (-1: nrow: 1 rrow: 1)
                                    branch: 0x2400161 37749089 (0: nrow: 1, level: 11)
                                       branch: 0x2400149 37749065 (-1: nrow: 1, level: 10)
                                          branch: 0x2400132 37749042 (-1: nrow: 1, level: 9)
                                             branch: 0x240011c 37749020 (-1: nrow: 1, level: 8)
                                                branch: 0x2400107 37748999 (-1: nrow: 1, level: 7)
                                                   branch: 0x24000f3 37748979 (-1: nrow: 1, level: 6)
                                                      branch: 0x24000e0 37748960 (-1: nrow: 1, level: 5)
                                                         branch: 0x24000ce 37748942 (-1: nrow: 1, level: 4)
                                                            branch: 0x24000bd 37748925 (-1: nrow: 1, level: 3)
                                                               branch: 0x24000ad 37748909 (-1: nrow: 1, level: 2)
                                                                  branch: 0x240009e 37748894 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400090 37748880 (-1: nrow: 1 rrow: 1)
                                 branch: 0x2400162 37749090 (0: nrow: 1, level: 12)
                                    branch: 0x240014a 37749066 (-1: nrow: 1, level: 11)
                                       branch: 0x2400133 37749043 (-1: nrow: 1, level: 10)
                                          branch: 0x240011d 37749021 (-1: nrow: 1, level: 9)
                                             branch: 0x2400108 37749000 (-1: nrow: 1, level: 8)
                                                branch: 0x24000f4 37748980 (-1: nrow: 1, level: 7)
                                                   branch: 0x24000e1 37748961 (-1: nrow: 1, level: 6)
                                                      branch: 0x24000cf 37748943 (-1: nrow: 1, level: 5)
                                                         branch: 0x24000be 37748926 (-1: nrow: 1, level: 4)
                                                            branch: 0x24000ae 37748910 (-1: nrow: 1, level: 3)
                                                               branch: 0x240009f 37748895 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400091 37748881 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400084 37748868 (-1: nrow: 1 rrow: 1)
                              branch: 0x2400163 37749091 (0: nrow: 1, level: 13)
                                 branch: 0x240014b 37749067 (-1: nrow: 1, level: 12)
                                    branch: 0x2400134 37749044 (-1: nrow: 1, level: 11)
                                       branch: 0x240011e 37749022 (-1: nrow: 1, level: 10)
                                          branch: 0x2400109 37749001 (-1: nrow: 1, level: 9)
                                             branch: 0x24000f5 37748981 (-1: nrow: 1, level: 8)
                                                branch: 0x24000e2 37748962 (-1: nrow: 1, level: 7)
                                                   branch: 0x24000d0 37748944 (-1: nrow: 1, level: 6)
                                                      branch: 0x24000bf 37748927 (-1: nrow: 1, level: 5)
                                                         branch: 0x24000af 37748911 (-1: nrow: 1, level: 4)
                                                            branch: 0x24000a0 37748896 (-1: nrow: 1, level: 3)
                                                               branch: 0x2400092 37748882 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400085 37748869 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400079 37748857 (-1: nrow: 1 rrow: 1)
                           branch: 0x2400164 37749092 (0: nrow: 1, level: 14)
                              branch: 0x240014c 37749068 (-1: nrow: 1, level: 13)
                                 branch: 0x2400135 37749045 (-1: nrow: 1, level: 12)
                                    branch: 0x240011f 37749023 (-1: nrow: 1, level: 11)
                                       branch: 0x240010a 37749002 (-1: nrow: 1, level: 10)
                                          branch: 0x24000f6 37748982 (-1: nrow: 1, level: 9)
                                             branch: 0x24000e3 37748963 (-1: nrow: 1, level: 8)
                                                branch: 0x24000d1 37748945 (-1: nrow: 1, level: 7)
                                                   branch: 0x24000c0 37748928 (-1: nrow: 1, level: 6)
                                                      branch: 0x24000b0 37748912 (-1: nrow: 1, level: 5)
                                                         branch: 0x24000a1 37748897 (-1: nrow: 1, level: 4)
                                                            branch: 0x2400093 37748883 (-1: nrow: 1, level: 3)
                                                               branch: 0x2400086 37748870 (-1: nrow: 1, level: 2)
                                                                  branch: 0x240007a 37748858 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x240006f 37748847 (-1: nrow: 1 rrow: 1)
                        branch: 0x2400165 37749093 (0: nrow: 1, level: 15)
                           branch: 0x240014d 37749069 (-1: nrow: 1, level: 14)
                              branch: 0x2400136 37749046 (-1: nrow: 1, level: 13)
                                 branch: 0x2400120 37749024 (-1: nrow: 1, level: 12)
                                    branch: 0x240010b 37749003 (-1: nrow: 1, level: 11)
                                       branch: 0x24000f7 37748983 (-1: nrow: 1, level: 10)
                                          branch: 0x24000e4 37748964 (-1: nrow: 1, level: 9)
                                             branch: 0x24000d2 37748946 (-1: nrow: 1, level: 8)
                                                branch: 0x24000c1 37748929 (-1: nrow: 1, level: 7)
                                                   branch: 0x24000b1 37748913 (-1: nrow: 1, level: 6)
                                                      branch: 0x24000a2 37748898 (-1: nrow: 1, level: 5)
                                                         branch: 0x2400094 37748884 (-1: nrow: 1, level: 4)
                                                            branch: 0x2400087 37748871 (-1: nrow: 1, level: 3)
                                                               branch: 0x240007b 37748859 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400070 37748848 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400066 37748838 (-1: nrow: 1 rrow: 1)
                     branch: 0x2400166 37749094 (0: nrow: 1, level: 16)
                        branch: 0x240014e 37749070 (-1: nrow: 1, level: 15)
                           branch: 0x2400137 37749047 (-1: nrow: 1, level: 14)
                              branch: 0x2400121 37749025 (-1: nrow: 1, level: 13)
                                 branch: 0x240010c 37749004 (-1: nrow: 1, level: 12)
                                    branch: 0x24000f8 37748984 (-1: nrow: 1, level: 11)
                                       branch: 0x24000e5 37748965 (-1: nrow: 1, level: 10)
                                          branch: 0x24000d3 37748947 (-1: nrow: 1, level: 9)
                                             branch: 0x24000c2 37748930 (-1: nrow: 1, level: 8)
                                                branch: 0x24000b2 37748914 (-1: nrow: 1, level: 7)
                                                   branch: 0x24000a3 37748899 (-1: nrow: 1, level: 6)
                                                      branch: 0x2400095 37748885 (-1: nrow: 1, level: 5)
                                                         branch: 0x2400088 37748872 (-1: nrow: 1, level: 4)
                                                            branch: 0x240007c 37748860 (-1: nrow: 1, level: 3)
                                                               branch: 0x2400071 37748849 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400067 37748839 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x240005e 37748830 (-1: nrow: 1 rrow: 1)
                  branch: 0x2400167 37749095 (0: nrow: 1, level: 17)
                     branch: 0x240014f 37749071 (-1: nrow: 1, level: 16)
                        branch: 0x2400138 37749048 (-1: nrow: 1, level: 15)
                           branch: 0x2400122 37749026 (-1: nrow: 1, level: 14)
                              branch: 0x240010d 37749005 (-1: nrow: 1, level: 13)
                                 branch: 0x24000f9 37748985 (-1: nrow: 1, level: 12)
                                    branch: 0x24000e6 37748966 (-1: nrow: 1, level: 11)
                                       branch: 0x24000d4 37748948 (-1: nrow: 1, level: 10)
                                          branch: 0x24000c3 37748931 (-1: nrow: 1, level: 9)
                                             branch: 0x24000b3 37748915 (-1: nrow: 1, level: 8)
                                                branch: 0x24000a4 37748900 (-1: nrow: 1, level: 7)
                                                   branch: 0x2400096 37748886 (-1: nrow: 1, level: 6)
                                                      branch: 0x2400089 37748873 (-1: nrow: 1, level: 5)
                                                         branch: 0x240007d 37748861 (-1: nrow: 1, level: 4)
                                                            branch: 0x2400072 37748850 (-1: nrow: 1, level: 3)
                                                               branch: 0x2400068 37748840 (-1: nrow: 1, level: 2)
                                                                  branch: 0x240005f 37748831 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400057 37748823 (-1: nrow: 1 rrow: 1)
               branch: 0x2400168 37749096 (0: nrow: 1, level: 18)
                  branch: 0x2400150 37749072 (-1: nrow: 1, level: 17)
                     branch: 0x2400139 37749049 (-1: nrow: 1, level: 16)
                        branch: 0x2400123 37749027 (-1: nrow: 1, level: 15)
                           branch: 0x240010e 37749006 (-1: nrow: 1, level: 14)
                              branch: 0x24000fa 37748986 (-1: nrow: 1, level: 13)
                                 branch: 0x24000e7 37748967 (-1: nrow: 1, level: 12)
                                    branch: 0x24000d5 37748949 (-1: nrow: 1, level: 11)
                                       branch: 0x24000c4 37748932 (-1: nrow: 1, level: 10)
                                          branch: 0x24000b4 37748916 (-1: nrow: 1, level: 9)
                                             branch: 0x24000a5 37748901 (-1: nrow: 1, level: 8)
                                                branch: 0x2400097 37748887 (-1: nrow: 1, level: 7)
                                                   branch: 0x240008a 37748874 (-1: nrow: 1, level: 6)
                                                      branch: 0x240007e 37748862 (-1: nrow: 1, level: 5)
                                                         branch: 0x2400073 37748851 (-1: nrow: 1, level: 4)
                                                            branch: 0x2400069 37748841 (-1: nrow: 1, level: 3)
                                                               branch: 0x2400060 37748832 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400058 37748824 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400051 37748817 (-1: nrow: 1 rrow: 1)
            branch: 0x2400169 37749097 (0: nrow: 1, level: 19)
               branch: 0x2400151 37749073 (-1: nrow: 1, level: 18)
                  branch: 0x240013a 37749050 (-1: nrow: 1, level: 17)
                     branch: 0x2400124 37749028 (-1: nrow: 1, level: 16)
                        branch: 0x240010f 37749007 (-1: nrow: 1, level: 15)
                           branch: 0x24000fb 37748987 (-1: nrow: 1, level: 14)
                              branch: 0x24000e8 37748968 (-1: nrow: 1, level: 13)
                                 branch: 0x24000d6 37748950 (-1: nrow: 1, level: 12)
                                    branch: 0x24000c5 37748933 (-1: nrow: 1, level: 11)
                                       branch: 0x24000b5 37748917 (-1: nrow: 1, level: 10)
                                          branch: 0x24000a6 37748902 (-1: nrow: 1, level: 9)
                                             branch: 0x2400098 37748888 (-1: nrow: 1, level: 8)
                                                branch: 0x240008b 37748875 (-1: nrow: 1, level: 7)
                                                   branch: 0x240007f 37748863 (-1: nrow: 1, level: 6)
                                                      branch: 0x2400074 37748852 (-1: nrow: 1, level: 5)
                                                         branch: 0x240006a 37748842 (-1: nrow: 1, level: 4)
                                                            branch: 0x2400061 37748833 (-1: nrow: 1, level: 3)
                                                               branch: 0x2400059 37748825 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400052 37748818 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x240004c 37748812 (-1: nrow: 1 rrow: 1)
         branch: 0x240016a 37749098 (0: nrow: 1, level: 20)
            branch: 0x2400152 37749074 (-1: nrow: 1, level: 19)
               branch: 0x240013b 37749051 (-1: nrow: 1, level: 18)
                  branch: 0x2400125 37749029 (-1: nrow: 1, level: 17)
                     branch: 0x2400110 37749008 (-1: nrow: 1, level: 16)
                        branch: 0x24000fc 37748988 (-1: nrow: 1, level: 15)
                           branch: 0x24000e9 37748969 (-1: nrow: 1, level: 14)
                              branch: 0x24000d7 37748951 (-1: nrow: 1, level: 13)
                                 branch: 0x24000c6 37748934 (-1: nrow: 1, level: 12)
                                    branch: 0x24000b6 37748918 (-1: nrow: 1, level: 11)
                                       branch: 0x24000a7 37748903 (-1: nrow: 1, level: 10)
                                          branch: 0x2400099 37748889 (-1: nrow: 1, level: 9)
                                             branch: 0x240008c 37748876 (-1: nrow: 1, level: 8)
                                                branch: 0x2400080 37748864 (-1: nrow: 1, level: 7)
                                                   branch: 0x2400075 37748853 (-1: nrow: 1, level: 6)
                                                      branch: 0x240006b 37748843 (-1: nrow: 1, level: 5)
                                                         branch: 0x2400062 37748834 (-1: nrow: 1, level: 4)
                                                            branch: 0x240005a 37748826 (-1: nrow: 1, level: 3)
                                                               branch: 0x2400053 37748819 (-1: nrow: 1, level: 2)
                                                                  branch: 0x240004d 37748813 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400048 37748808 (-1: nrow: 1 rrow: 1)
      branch: 0x240016b 37749099 (0: nrow: 1, level: 21)
         branch: 0x2400153 37749075 (-1: nrow: 1, level: 20)
            branch: 0x240013c 37749052 (-1: nrow: 1, level: 19)
               branch: 0x2400126 37749030 (-1: nrow: 1, level: 18)
                  branch: 0x2400111 37749009 (-1: nrow: 1, level: 17)
                     branch: 0x24000fd 37748989 (-1: nrow: 1, level: 16)
                        branch: 0x24000ea 37748970 (-1: nrow: 1, level: 15)
                           branch: 0x24000d8 37748952 (-1: nrow: 1, level: 14)
                              branch: 0x24000c7 37748935 (-1: nrow: 1, level: 13)
                                 branch: 0x24000b7 37748919 (-1: nrow: 1, level: 12)
                                    branch: 0x24000a8 37748904 (-1: nrow: 1, level: 11)
                                       branch: 0x240009a 37748890 (-1: nrow: 1, level: 10)
                                          branch: 0x240008d 37748877 (-1: nrow: 1, level: 9)
                                             branch: 0x2400081 37748865 (-1: nrow: 1, level: 8)
                                                branch: 0x2400076 37748854 (-1: nrow: 1, level: 7)
                                                   branch: 0x240006c 37748844 (-1: nrow: 1, level: 6)
                                                      branch: 0x2400063 37748835 (-1: nrow: 1, level: 5)
                                                         branch: 0x240005b 37748827 (-1: nrow: 1, level: 4)
                                                            branch: 0x2400054 37748820 (-1: nrow: 1, level: 3)
                                                               branch: 0x240004e 37748814 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400049 37748809 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400045 37748805 (-1: nrow: 1 rrow: 1)
   branch: 0x240016d 37749101 (0: nrow: 1, level: 22)
      branch: 0x2400155 37749077 (-1: nrow: 1, level: 21)
         branch: 0x240013e 37749054 (-1: nrow: 1, level: 20)
            branch: 0x2400128 37749032 (-1: nrow: 1, level: 19)
               branch: 0x2400113 37749011 (-1: nrow: 1, level: 18)
                  branch: 0x24000ff 37748991 (-1: nrow: 1, level: 17)
                     branch: 0x24000ec 37748972 (-1: nrow: 1, level: 16)
                        branch: 0x24000da 37748954 (-1: nrow: 1, level: 15)
                           branch: 0x24000c9 37748937 (-1: nrow: 1, level: 14)
                              branch: 0x24000b9 37748921 (-1: nrow: 1, level: 13)
                                 branch: 0x24000aa 37748906 (-1: nrow: 1, level: 12)
                                    branch: 0x240009c 37748892 (-1: nrow: 1, level: 11)
                                       branch: 0x240008f 37748879 (-1: nrow: 1, level: 10)
                                          branch: 0x2400083 37748867 (-1: nrow: 1, level: 9)
                                             branch: 0x2400078 37748856 (-1: nrow: 1, level: 8)
                                                branch: 0x240006e 37748846 (-1: nrow: 1, level: 7)
                                                   branch: 0x2400065 37748837 (-1: nrow: 1, level: 6)
                                                      branch: 0x240005d 37748829 (-1: nrow: 1, level: 5)
                                                         branch: 0x2400056 37748822 (-1: nrow: 1, level: 4)
                                                            branch: 0x2400050 37748816 (-1: nrow: 1, level: 3)
                                                               branch: 0x240004b 37748811 (-1: nrow: 1, level: 2)
                                                                  branch: 0x2400047 37748807 (-1: nrow: 1, level: 1)
                                                                     leaf: 0x2400044 37748804 (-1: nrow: 1 rrow: 1)
----- end tree dump


理论上人人都可以想到,可是实际上只有极少人想到,这就是大师和普通人的区别吧。
我们只要增加到25层,那么就会报出600错误,也就是Oracle不允许大于24层的索引存在。
SQL> @t

Table dropped.


Table created.


Index created.

25
24
23
22
21
20
19
18
17
16
15
14
13
12
11
10
9
8
7
6
5
4
3
2
1
begin   for i in reverse 1..25
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [6051], [], [], [], [], [], [], []
ORA-06512: at line 4


这样Jonathan就完成了他的证明。

查看本文来源

盖国强

索引

Oracle

Oracle


百度大联盟认证黄金会员Copyright© 1997- CNET Networks 版权所有。 ZDNet 是CNET Networks公司注册服务商标。
中华人民共和国电信与信息服务业务经营许可证编号:京ICP证010391号 京ICP备09041801号-159
京公网安备:1101082134