首页 » ORACLE 9i-23ai » About Columns Order(列先后顺序)

About Columns Order(列先后顺序)

有时总是忽略在列的顺序带来的性能开销,还有列的null值字段的位置,想理解这个问题要首先程序列数据是如何在block上存储的,并且是如何定位的?

我做个测试 db version:11203 ,os version: rhel 5.8

anbob@ANBOB> select rowid from obj where object_id=1001;

ROWID
------------------
AAAS2cAAEAAAAKeAAH
AAAS2cAAEAAAAs0AAD

anbob@ANBOB> select * from anbob.obj where rowid='AAAS2cAAEAAAAs0AAD';

OWNER                          OBJECT_NAME                    SUBOBJECT_NAME
------------------------------ ------------------------------ ------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME
---------- -------------- ------------------- ------------------- -------------------
TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------------------- ------- - - - ---------- ------------------------------
SYS                            NOEXP$
      1001           1001 TABLE               2009-08-13 23:01:03 2009-08-13 23:01:03
2009-08-13:23:01:03 VALID   N N N          1

--dump block content
anbob@ANBOB> select dbms_rowid.ROWID_RELATIVE_FNO('AAAS2cAAEAAAAs0AAD')file# ,dbms_rowid.ROWID_BLOCK_NUMBEr('AAAS2cAAEAAAAs0AAD') blk# from dual;

     FILE#       BLK#
---------- ----------
         4       2868

anbob@ANBOB> conn / as sysdba
Connected.

sys@ANBOB> alter system dump datafile 4 block 2868;

System altered.

sys@ANBOB> select * from v$diag_info where name='Default Trace File';

sys@ANBOB> host
[oracle@ora11 ~]$ vi /ora11g/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_27537.trc

...省略
tab 0, row 2, @0x11c5
tl: 79 fb: --H-FL-- lb: 0x1  cc: 14
col  0: [ 3]  53 59 53
col  1: [ 7]  45 58 50 41 43 54 24
col  2: *NULL*
col  3: [ 2]  c2 0b
col  4: [ 2]  c2 0b
col  5: [ 5]  54 41 42 4c 45
col  6: [ 7]  78 6d 08 0d 18 02 04
col  7: [ 7]  78 6d 08 0d 18 02 04
col  8: [19]  32 30 30 39 2d 30 38 2d 31 33 3a 32 33 3a 30 31 3a 30 33
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
col 13: [ 2]  c1 02
tab 0, row 3, @0x1214
tl: 80 fb: --H-FL-- lb: 0x1  cc: 14
col  0: [ 3]  53 59 53
col  1: [ 6]  4e 4f 45 58 50 24
col  2: *NULL*
col  3: [ 3]  c2 0b 02
col  4: [ 3]  c2 0b 02
col  5: [ 5]  54 41 42 4c 45
col  6: [ 7]  78 6d 08 0d 18 02 04
col  7: [ 7]  78 6d 08 0d 18 02 04
col  8: [19]  32 30 30 39 2d 30 38 2d 31 33 3a 32 33 3a 30 31 3a 30 33
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
col 13: [ 2]  c1 02
...省略

sys@ANBOB> select dbms_rowid.ROWID_ROW_NUMBER('AAAS2cAAEAAAAs0AAD') row# from dual;

      ROW#
----------
         3
--The below was I verifing dump block data
sys@ANBOB> select column_name,data_type,column_id from dba_tab_columns where table_name='OBJ' and owner='ANBOB' order by 3;

COLUMN_NAME                    DATA_TYPE             COLUMN_ID
------------------------------ -------------------- ----------
OWNER                          VARCHAR2                      1
OBJECT_NAME                    VARCHAR2                      2
SUBOBJECT_NAME                 VARCHAR2                      3
OBJECT_ID                      NUMBER                        4
DATA_OBJECT_ID                 NUMBER                        5
OBJECT_TYPE                    VARCHAR2                      6
CREATED                        DATE                          7
LAST_DDL_TIME                  DATE                          8
TIMESTAMP                      VARCHAR2                      9
STATUS                         VARCHAR2                     10
TEMPORARY                      VARCHAR2                     11
GENERATED                      VARCHAR2                     12
SECONDARY                      VARCHAR2                     13
NAMESPACE                      NUMBER                       14
EDITION_NAME                   VARCHAR2                     15

sys@ANBOB> select dump('NOEXP$',16) from dual; --col  1: [ 6]  4e 4f 45 58 50 24

DUMP('NOEXP$',16)
-------------------------------
Typ=96 Len=6: 4e,4f,45,58,50,24

sys@ANBOB> select dump(1001,16) from dual;--col  3: [ 3]  c2 0b 02

DUMP(1001,16)
-------------------
Typ=2 Len=3: c2,b,2

sys@ANBOB> select convertdump('c2 0b 02') from dual;

CONVERDUMP('C20B02')
--------------------------------------------------
1001
sys@ANBOB> select convertdump('4e 4f 45 58 50 24',1) from dual;

CONVERDUMP('4E4F45585024',1)
---------------------------------------------------
NOEXP$

sys@ANBOB> select convertdump('78 6d 08 0d 18 02 04',2) from dual;

CONVERTDUMP('786D080D180204',2)
---------------------------------------------------
2009-08-13 23:01:03

tip:
convertdump function was created by me

数据是一致的,cc: columns it contains 细心的朋友会发现dump columns是14,也就是包含14列数据,而表定义是15列,虽然第15列全是null,是不是列上是null也cc就不记在内呢?我们做个实验

anbob@ANBOB> create table testcc(col1 int,col2 int,col3 int,col4 int,col5 int,col6 int,col7 int,col8 int,col9 int);

anbob@ANBOB> insert into testcc(col1,col5,col6) values(1,5,6);

anbob@ANBOB> commit;

############TRACE FILE Content###############
data_block_dump,data header at 0xe11664
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x00e11664
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f89
avsp=0x1f75
tosp=0x1f75
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f89
block_row_dump:
tab 0, row 0, @0x1f89
tl: 15 fb: --H-FL-- lb: 0x1  cc: 6
col  0: [ 2]  c1 02
col  1: *NULL*
col  2: *NULL*
col  3: *NULL*
col  4: [ 2]  c1 06
col  5: [ 2]  c1 07
end_of_block_dump

插入一条数据再dump

anbob@ANBOB> insert into testcc(col8) values(8);

block_row_dump:
tab 0, row 0, @0x1f8b
tl: 13 fb: --H-FL-- lb: 0x1  cc: 8
col  0: *NULL*
col  1: *NULL*
col  2: *NULL*
col  3: *NULL*
col  4: *NULL*
col  5: *NULL*
col  6: *NULL*
col  7: [ 2]  c1 09
end_of_block_dump

note:
cc是记录的这一行上包含的列数据,末端列的null是不存储的,前或中间的null 用*NULL*标记,所以把null字段放在后面是不错的,影响了列长度和平均列长度。

一行数据存储在block 是分为两部份,1,row header 记录了行的row size、flag byte、ITL entry that has this row locked、Number of columns in this ROW piece;2,column data,因为每列数据有不同长度,所以也分为两部分,前部分是长度,后部分是数据

数据库是没有办法一步定位到某行的某列的值的,因为它不知道那列在一行上的offset,我猜想一下,还是从开始的trace文件的两行记录发现在block head有第一行的offset
很快定位到第一行,@0x1214-@0x11c5 就是row2 的tl: 79行长度,用这个可以记录行的offset,同一行内的列就是用列的长度来计算offset,比如要找第90列,就要从第1列开始根据长度+第2列+第3列....,所以列的前后位置顺序也影响读取效率。所以通常要把经常使用的列放到最前面,把字段为null的放到最后面
tab 0, row 2, @0x11c5
tl: 79 fb: --H-FL-- lb: 0x1  cc: 14
tab 0, row 3, @0x1214
tl: 80 fb: --H-FL-- lb: 0x1  cc: 14

--下面在做个实验证明

anbob@ANBOB> declare 
  2  v_sql varchar2(300);
  3  v_sqlfull varchar2(30000);
  4  v_col varchar2(30000);
  5  begin
  6  v_sql :='create table testcols(col1 int';
  7   for i in 2..254 loop
  8    v_col :=v_col||',col'||i||' int';
  9   end loop;
 10  --dbms_output.put_line(v_sql||v_col||');');
 11   v_sqlfull := v_sql||v_col||')';
 12  execute immediate  v_sqlfull ;
 13  end;
 14  /


anbob@ANBOB> select 'insert into testcols values('||rpad('1',507,',1')||');' from dual;

执行,insert 全部字段值1.

anbob@ANBOB> begin
  2    for i in 1..15 loop
  3     insert into testcols select * from testcols;
  4   end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

anbob@ANBOB> select count(*) from testcols;

  COUNT(*)
----------
     32768


anbob@ANBOB> declare
  2  v_speed number;
  3  v_count number;
  4  begin
  5  v_speed:=dbms_utility.get_time;
  6  for i in 1..1000 loop
  7  select count(col1) into v_count from testcols;
  8  end loop;
  9  dbms_output.put_line(dbms_utility.get_time-v_speed);
 10  v_speed:=dbms_utility.get_time;
 11  for i in 1..1000 loop
 12  select count(col254) into v_count from testcols;
 13  end loop;
 14  dbms_output.put_line(dbms_utility.get_time-v_speed);
 15  end;
 16  /
-------------
1089
3205

dbms_utility.get_time是什么时间单位呢?测一下
anbob@ANBOB> declare
  2  v_speed number;
  3  begin
  4  v_speed:=dbms_utility.get_time;
  5  dbms_lock.sleep(5);
  6  dbms_output.put_line(dbms_utility.get_time-v_speed);
  7  end;
  8  /
500

count第一列用了10秒,第254列用了32秒,在我的机器上可以看到有3倍的差距,在列上有大字段时会更明显

note:
数据库计算列的offset是从每行的第1列开始加列长度定位的,所以把使用频繁的列放到表的前端,末端null值字段在block上是不存储的,所以应该放到表的尾端。

打赏

对不起,这篇文章暂时关闭评论。

上一篇:

下一篇: