表中列的排列顺序对查询的影响

列顺序测试

大家在做表设计的时候通常对表中列的排列顺序没有过多注意，但是其实越常用的列，它的位置越靠前，则查询速度越快。

因为每个block里面存储了row directory (每行数据在块中的位移地址)而没有存放column的位移
所以只能根据column#通过扫描row header 以及 column data 来获取相应的列值
越靠后的列，需要计算的越多

建立测试表col_test

declare  v_sql varchar2(4000) ;begin  v_sql := 'create table col_test (' ;  for i in 1 .. 100 loop    v_sql := v_sql || 'id'||i||' number ,' ;  end loop ;  v_sql := substr(v_sql,1,length(v_sql)-1) || ') ' ;  execute immediate v_sql ;end ;/_dexter@DAVID> desc col_test ; Name                                                  Null?    Type ----------------------------------------------------- -------- -------------- ID1                                                            NUMBER ID2                                                            NUMBER ID3                                                            NUMBER ID4                                                            NUMBER ID5                                                            NUMBER .... ID99                                                           NUMBER ID100                                                          NUMBER

初始化数据100w条

declare  v_sql varchar2(4000) ;begin  v_sql := 'insert into  col_test select ' ;  for i in 1 .. 100 loop    v_sql := v_sql || ' 1 ,' ;  end loop ;  v_sql := substr(v_sql,1,length(v_sql)-1) || ' from dual connect by level <= 1000000 ' ;  execute immediate v_sql ;  commit ;end ;/

测试耗时

declare  n       number ;  begin_t pls_integer ;  end_t   pls_integer ;  v_Sql   varchar2(4000) ;begin  for i in 1 .. 101 loop     begin_t := dbms_utility.get_time ;     if i = 1 then       v_sql := 'select count(*) from col_test' ;     else       v_sql := 'select count(id'||(i-1)||') from col_test' ;     end if ;     execute immediate v_sql into n ;     end_t   := dbms_utility.get_time ;     dbms_output.put_line('Col'||(i-1)||' : '||(end_t-begin_t)*10);  end loop ;end ;/--下面的单位为毫秒_dexter@DAVID> /Col0 : 150Col1 : 140Col2 : 110Col3 : 100Col4 : 100Col5 : 110Col6 : 110Col7 : 110Col8 : 110Col9 : 120Col10 : 130Col11 : 120Col12 : 110Col13 : 120Col14 : 120Col15 : 120Col16 : 130Col17 : 120Col18 : 160Col19 : 130Col20 : 140Col21 : 130Col22 : 140Col23 : 140Col24 : 140Col25 : 130Col26 : 160Col27 : 150Col28 : 140Col29 : 150Col30 : 150Col31 : 160Col32 : 180Col33 : 160Col34 : 160Col35 : 160Col36 : 160Col37 : 170Col38 : 180Col39 : 170Col40 : 170Col41 : 180Col42 : 180Col43 : 170Col44 : 210Col45 : 190Col46 : 180Col47 : 180Col48 : 190Col49 : 210Col50 : 190Col51 : 190Col52 : 200Col53 : 200Col54 : 230Col55 : 200Col56 : 210Col57 : 200Col58 : 210Col59 : 220Col60 : 210Col61 : 210Col62 : 220Col63 : 240Col64 : 220Col65 : 220Col66 : 220Col67 : 230Col68 : 270Col69 : 220Col70 : 230Col71 : 230Col72 : 270Col73 : 230Col74 : 240Col75 : 240Col76 : 250Col77 : 240Col78 : 260Col79 : 260Col80 : 280Col81 : 250Col82 : 250Col83 : 250Col84 : 250Col85 : 260Col86 : 260Col87 : 260Col88 : 290Col89 : 260Col90 : 270Col91 : 280Col92 : 280Col93 : 280Col94 : 270Col95 : 310Col96 : 270Col97 : 310Col98 : 290Col99 : 290Col100 : 280PL/SQL procedure successfully completed.

可以看到耗时是线性增加的，所以在做表设计的时候，应该注意建表时的列的排列顺序，将常用的列放在靠前的位置

null值测试

关于null值的存储，可以查看我的另外一篇帖子http://blog.csdn.net/renfengjun/article/details/7980811
因为基于它的存储原理，一个rowpiece中，如果列后面的值都为空，则不再占用存储空间，并且 rowheader 的cc(Column Count) 也不会将其记录到上面，
也就是说如果你的列个数为5，但是这一行的rowpiece中第二列不为空，第三、四、五列为空
则cc会记为2
但是如果第三、四列都为空，但是第五列不为空，则cc=5 ，第三列第四列使用都使用ff表示，占用了2个字节的存储空间
null值在表中的位置除了影响存储空间，还会影响查询，因为在扫描数据的时候
可以根据cc的个数来确定是否需要扫描column data的数据
感兴趣的可以做一下测试
100列，第50列为有值，其他都为null

下面是测试内容：

建立测试表null_test

declare  v_sql varchar2(4000) ;begin  v_sql := 'create table null_test (' ;  for i in 1 .. 100 loop    v_sql := v_sql || 'id'||i||' number ,' ;  end loop ;  v_sql := substr(v_sql,1,length(v_sql)-1) || ') ' ;  execute immediate v_sql ;end ;/

初始化数据100w条

insert into null_test(id50) select 100 from dual connect by level <= 1000000 ; commit ;

测试

declare  n       number ;  begin_t pls_integer ;  end_t   pls_integer ;  v_Sql   varchar2(4000) ;begin  for i in 1 .. 101 loop     begin_t := dbms_utility.get_time ;     if i = 1 then       v_sql := 'select count(*) from null_test' ;     else       v_sql := 'select count(id'||(i-1)||') from null_test' ;     end if ;     execute immediate v_sql into n ;     end_t   := dbms_utility.get_time ;     dbms_output.put_line('Col'||(i-1)||' : '||(end_t-begin_t)*10);  end loop ;end ;/--测试结果dexter@FAKE> /Col0 : 60Col1 : 80Col2 : 80Col3 : 90Col4 : 90Col5 : 100Col6 : 100Col7 : 100Col8 : 120Col9 : 110Col10 : 120Col11 : 130Col12 : 130Col13 : 140Col14 : 150Col15 : 160Col16 : 160Col17 : 160Col18 : 170Col19 : 170Col20 : 170Col21 : 180Col22 : 180Col23 : 180Col24 : 190Col25 : 190Col26 : 200Col27 : 200Col28 : 200Col29 : 200Col30 : 200Col31 : 210Col32 : 210Col33 : 220Col34 : 220Col35 : 230Col36 : 220Col37 : 220Col38 : 230Col39 : 240Col40 : 240Col41 : 230Col42 : 240Col43 : 250Col44 : 250Col45 : 250Col46 : 260Col47 : 260Col48 : 270Col49 : 260Col50 : 270Col51 : 70Col52 : 80Col53 : 70Col54 : 70Col55 : 80Col56 : 70Col57 : 70Col58 : 80Col59 : 70Col60 : 70Col61 : 70Col62 : 70Col63 : 80Col64 : 80Col65 : 70Col66 : 70Col67 : 70Col68 : 80Col69 : 70Col70 : 70Col71 : 70Col72 : 80Col73 : 70Col74 : 70Col75 : 70Col76 : 80Col77 : 70Col78 : 70Col79 : 70Col80 : 80Col81 : 70Col82 : 70Col83 : 80Col84 : 70Col85 : 70Col86 : 70Col87 : 80Col88 : 70Col89 : 70Col90 : 80Col91 : 70Col92 : 70Col93 : 70Col94 : 80Col95 : 70Col96 : 70Col97 : 70Col98 : 70Col99 : 70Col100 : 80PL/SQL procedure successfully completed.

可以看到前50列的查询耗时是线性增加，但是从第51列开始到最后都维持到一个稳定的值
线性增加是因为Oracle 数据库会不断扫描row header 以及 column data 计算要查找的列的位移值
而从第51列开始，则可以直接扫描row header 的 CC 个数即可拿到要查找的列的值（null），所以节省了大量扫描column data部分的时间，也证实了上面提出的观点
所以设计表的时候，尽量把会存储null值的列放在表的末尾

为了加深印象，我们使用bbed抽出一条数据来看一下它在block中到底是怎样存储的，加深一下印象

dexter@FAKE> select dbms_rowid.rowid_relative_fno(t.rowid) as "FNO#",    2         dbms_rowid.rowid_block_number(t.rowid) as "BLK#",    3         dbms_rowid.rowid_row_number(t.rowid) as "ROW#"    4    from dexter.null_test t   5   where rownum<2 ;      FNO#       BLK#       ROW#---------- ---------- ----------         6        284          0BBED> set dba 6,284        DBA             0x0180011c (25166108 6,284)BBED> p *kdbr[0]rowdata[4675]-------------ub1 rowdata[4675]                           @5878     0x2cBBED> set offset 5878        OFFSET          5878BBED> dump /v offset 5878 count 58 File: /u01/apps/oracle/oradata/fake/gg_trans201.dbf (6) Block: 284     Offsets: 5878 to 5935  Dba:0x0180011c------------------------------------------------------- 2c0032ff ffffffff ffffffff ffffffff l ,.2............. ffffffff ffffffff ffffffff ffffffff l ................ ffffffff ffffffff ffffffff ffffffff l ................ ffffffff 02c2022c 0032              l .....Â.,.2 <16 bytes per line>

大家一定对count 58 产生疑问，请继续看下去

row header 2c0032
 它包括：
 2c=flag=00101100=--H-FL--=header+first+last
 00=lb itl slot 0x00 
 32=cc column count = 16x3+2=50
因为这是一个普通的堆积表，所以后面就是rowpiece的column data了，包括前49列49个字节的ff和第50列的列长度02和列值c202

dexter@FAKE> select dump(100,16) from dual ;DUMP(100,16)-----------------Typ=2 Len=2: c2,2

so 3+49+1+2=55
后面3个字节是下一行的row header : 2c 0032 

LBITLCCCOL#1 valueUntil COL#49 valueCOL#50 lengthCOL#50 valueCOL#51 valueUntil COL#100 value2C0032FFFF02C202FFFF

附录：

rowpiece构成

可以参考官方文档中的说明
图12-7

row piece header包括columns个数，other row piece的位置（当出现row chain或者列个数大于253的时候），cluster key （cluster table）
通常一个row header 至少包含3个字节（状态、itl槽位、column个数）


cluster table的存储情况会在其他章节中介绍
关于row chain的存储，可以我的另外一篇帖子，超过253列的存储

http://blog.csdn.net/renfengjun/article/details/8092735

LB 介绍

KCHDFLPN

K=Cluster key                    (用来表示cluster key)

C=Cluster table member    (当表中拥有cluster时使用此标识)

H=Head of row piece         (含有Rowpiece header)

D=Delete row                   (表示已经删除)

F=First data piece             (Rowpiece 的第一个piece)

L=Last data piece              (Rowpiece 的最后一个piece,Rowchain或者列个数大于253时候会出现)

P=First column continues from previous piece  (没有重现过)

N=Last column continues in next piece            (没有重现过)