oracle avg row len,dba_tables中的avg_row_len是如何被计算的？

收集信息时avg_row_len被计算，使用analyze 和dbms_stats搜集时计算avg_row_len的差别在于后者不考虑row header占用的3个byte

doc如下：

C:>sqlplus xys/manager

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 10月 8 22:10:18 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> desc tab1;

名称 是否为空? 类型

----------------------------------------- -------- ----------------------------

ID NUMBER(38)

NAME VARCHAR2(10)

TIME DATE

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

16

SQL> SELECT *FROM TAB1;

ID NAME TIME

---------- ---------- --------------

1 a 08-10月-07

SQL> insert into tab1 values(2,null , sysdate);

已创建 1 行。

SQL> commit;

提交完成。

SQL> analyze table tab1 compute statistics;

表已分析。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

15

SQL> select * from tab1;

ID NAME TIME

---------- ---------- --------------

1 a 08-10月-07

2 08-10月-07

--这里的3是row header占用的byte，1代表的是The column length requires 1 byte for columns that store 250 bytes or less

SQL> select 3+vsize(id)+1+vsize(name)+1+vsize(time)+1 from tab1;

3+VSIZE(ID)+1+VSIZE(NAME)+1+VSIZE(TIME)+1

-----------------------------------------

16

SQL> select 3+vsize(id)+1+nvl(vsize(name),0)+1+vsize(time)+1 from tab1;

3+VSIZE(ID)+1+NVL(VSIZE(NAME),0)+1+VSIZE(TIME)+1

------------------------------------------------

16

15

SQL> truncate table tab1;

表被截断。

SQL> insert into tab1 values(2,null , sysdate);

已创建 1 行。

SQL> commit;

提交完成。

SQL> analyze table tab1 compute statistics;

表已分析。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

15

--如果是null的话而且不在末尾，也就是不是最后一个字段，那么只有column len占用一个字节

SQL> select 3+vsize(id)+1+nvl(vsize(name),0)+1+vsize(time)+1 from tab1;

3+VSIZE(ID)+1+NVL(VSIZE(NAME),0)+1+VSIZE(TIME)+1

------------------------------------------------

15

SQL> truncate table tab1;

表被截断。

SQL> insert into tab1 values(2,'a',null);

已创建 1 行。

SQL> commit;

提交完成。

SQL> analyze table tab1 compute statistics;

表已分析。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

8

SQL> select * from tab1;

ID NAME TIME

---------- ---------- --------------

2 a

--如果最后的字段是null的话，那么column len也不占用字节，也就是doc上提到的for trailing null columns Oracle does not even store the column length.

前几天还看到有人问什么是"trailing null columns",biti大师做了回答

SQL> select 3+vsize(id)+1+vsize(name)+1 from tab1;

3+VSIZE(ID)+1+VSIZE(NAME)+1

---------------------------

8

SQL> alter table tab1 add col1 varchar2(300);

表已更改。

SQL> analyze table tab1 compute statistics;

表已分析。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

8

SQL> update tab1 set col1=rpad('a' , 249);

已更新 1 行。

SQL> commit;

提交完成。

SQL> analyze table tab1 compute statistics;

表已分析。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

259

SQL> select id , name , time , substr(col1 , 1 , 1) from tab1;

ID NAME TIME SU

---------- ---------- -------------- --

2 a a

SQL> select 3+vsize(id)+1+vsize(name)+1+nvl(vsize(time),0)+1+1+vsize(col1) from

tab1;

3+VSIZE(ID)+1+VSIZE(NAME)+1+NVL(VSIZE(TIME),0)+1+1+VSIZE(COL1)

--------------------------------------------------------------

259

SQL> update tab1 set col1=rpad('a' , 250);

已更新 1 行。

SQL> commit;

提交完成。

SQL> analyze table tab1 compute statistics;

表已分析。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

260

SQL> select 3+vsize(id)+1+vsize(name)+1+nvl(vsize(time),0)+1+1+vsize(col1) from

tab1;

3+VSIZE(ID)+1+VSIZE(NAME)+1+NVL(VSIZE(TIME),0)+1+1+VSIZE(COL1)

--------------------------------------------------------------

260

--The column length requires 1 byte for columns that store 250 bytes or less, or 3 bytes for columns that store more than 250 bytes

当列上的值超过250的时候，column len要求3个byte

SQL> update tab1 set col1=rpad('a' , 251);

已更新 1 行。

SQL> commit;

提交完成。

SQL> analyze table tab1 compute statistics;

表已分析。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

263

SQL> select 3+vsize(id)+1+vsize(name)+1+nvl(vsize(time),0)+1+1+vsize(col1) from

tab1;

3+VSIZE(ID)+1+VSIZE(NAME)+1+NVL(VSIZE(TIME),0)+1+1+VSIZE(COL1)

--------------------------------------------------------------

261

SQL> select 3+vsize(id)+1+vsize(name)+1+nvl(vsize(time),0)+1+3+vsize(col1) from

tab1;

3+VSIZE(ID)+1+VSIZE(NAME)+1+NVL(VSIZE(TIME),0)+1+3+VSIZE(COL1)

--------------------------------------------------------------

263

--==============================================================================

使用dbms_stats收集statistics情况会有所不同，计算avg_row_len时row header不被计算

SQL> truncate table tab1;

表被截断。

SQL> desc tab1;

名称 是否为空? 类型

----------------------------------------- -------- ----------------------------

ID NUMBER(38)

NAME VARCHAR2(10)

TIME DATE

COL1 VARCHAR2(300)

SQL> insert into tab1(id , name) values(1 , 'a');

已创建 1 行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('xys' , 'tab1');

PL/SQL 过程已成功完成。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

6

SQL> select vsize(id)+1+vsize(name)+1 from tab1;

VSIZE(ID)+1+VSIZE(NAME)+1

-------------------------

5

SQL> update tab1 set time=sysdate;

已更新 1 行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('xys' , 'tab1');

PL/SQL 过程已成功完成。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

13

SQL> select vsize(id)+1+vsize(name)+1+vsize(time)+1 from tab1;

VSIZE(ID)+1+VSIZE(NAME)+1+VSIZE(TIME)+1

---------------------------------------

13

SQL> update tab1 set col1=rpad('a',249);

已更新 1 行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('xys' , 'tab1');

PL/SQL 过程已成功完成。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

263

SQL> select vsize(id)+1+vsize(name)+1+vsize(time)+1+vsize(col1)+1 from tab1;

VSIZE(ID)+1+VSIZE(NAME)+1+VSIZE(TIME)+1+VSIZE(COL1)+1

-----------------------------------------------------

263

SQL> update tab1 set col1=rpad('a',250);

已更新 1 行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('xys' , 'tab1');

PL/SQL 过程已成功完成。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

264

SQL> select vsize(id)+1+vsize(name)+1+vsize(time)+1+vsize(col1)+1 from tab1;

VSIZE(ID)+1+VSIZE(NAME)+1+VSIZE(TIME)+1+VSIZE(COL1)+1

-----------------------------------------------------

264

SQL> update tab1 set col1=rpad('a',251);

已更新 1 行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('xys' , 'tab1');

PL/SQL 过程已成功完成。

SQL> select avg_row_len from user_tables where table_name='TAB1';

AVG_ROW_LEN

-----------

265

SQL> select vsize(id)+1+vsize(name)+1+vsize(time)+1+vsize(col1)+1 from tab1;

VSIZE(ID)+1+VSIZE(NAME)+1+VSIZE(TIME)+1+VSIZE(COL1)+1

-----------------------------------------------------

265

SQL>