首页 » ORACLE 9i-23c » 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上是不存储的，所以应该放到表的尾端。
微信扫一扫，打赏作者吧～上一篇： excel 列匹配公式
下一篇： about parallel
对不起，这篇文章暂时关闭评论。