困扰了很久的一个问题![@more@]
SQL> create table tt(id int , name varchar2(20)) tablespace users;
表已创建。
SQL> insert into tt values(1,'a');
已创建 1 行。
SQL> insert into tt values(2,'aa');
已创建 1 行。
SQL> insert into tt values(2,'aaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaaaaaaaaaa');
已创建 1 行。
SQL> insert into tt values(2,'aaaaaaaaaaaaaaaaaaaa');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from tt;
ID NAME
---------- --------------------
1 a
2 aa
2 aaa
2 aaaa
2 aaaaa
2 aaaaaa
2 aaaaaaa
2 aaaaaaaa
2 aaaaaaaaa
2 aaaaaaaaaa
2 aaaaaaaaaaa
ID NAME
---------- --------------------
2 aaaaaaaaaaaa
2 aaaaaaaaaaaaa
2 aaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaaaaaa
已选择20行。
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_numb
er(rowid) block from tt ;
FNO BLOCK
---------- ----------
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
FNO BLOCK
---------- ----------
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
已选择20行。
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_b
lock_number(rowid) block from tt ;
FNO BLOCK
---------- ----------
4 22605
SQL> analyze table tt compute statistics;
表已分析。
SQL> select avg_row_len from dba_tables where table_name='TT' and owner='SYS';
AVG_ROW_LEN
-----------
17
SQL>
--===============================
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
tab 0, row 1, @0x1f87
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 2] 61 61
tab 0, row 2, @0x1f7d
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 61 61 61
tab 0, row 3, @0x1f72
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 4] 61 61 61 61
tab 0, row 4, @0x1f66
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 61 61 61 61 61
tab 0, row 5, @0x1f59
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 6] 61 61 61 61 61 61
tab 0, row 6, @0x1f4b
tl: 14 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 7] 61 61 61 61 61 61 61
tab 0, row 7, @0x1f3c
tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 8] 61 61 61 61 61 61 61 61
tab 0, row 8, @0x1f2c
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 9] 61 61 61 61 61 61 61 61 61
tab 0, row 9, @0x1f1b
tl: 17 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [10] 61 61 61 61 61 61 61 61 61 61
tab 0, row 10, @0x1f09
tl: 18 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [11] 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 11, @0x1ef6
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [12] 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 12, @0x1ee2
tl: 20 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [13] 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 13, @0x1ecd
tl: 21 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [14] 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 14, @0x1eb7
tl: 22 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [15] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 15, @0x1ea0
tl: 23 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 16, @0x1e88
tl: 24 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [17] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 17, @0x1e6f
tl: 25 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [18] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 18, @0x1e55
tl: 26 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [19] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 19, @0x1e3a
tl: 27 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [20] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 22605 maxblk 22605
--=================================
--上面的dump信息中我们发现每行都有一个tl,这个tl我猜测是total length,不知道是否准确,
也就是说tl代表的是row length,根据这个意思,可以计算:
SQL> select trunc((8+27)*10/20) from dual;
TRUNC((8+27)*10/20)
-------------------
17
下面是通过我们对row里面数据存储的理解来计算的:
A row fully contained in one block has at least 3 bytes of row header. After the row header information, each row contains column length and data. 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, and precedes the column data. Space required for column data depends on the datatype. If the datatype of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.
To conserve space, a null in a column only stores the column length (zero). Oracle does not store data for the null column. Also, for trailing null columns, Oracle does not even store the column length.
SQL> select vsize(id)+1+vsize(name)+1+3 row_len from tt;
ROW_LEN
----------
8
9
10
11
12
13
14
15
16
17
18
ROW_LEN
----------
19
20
21
22
23
24
25
26
27
已选择20行。
计算出来的8~27不正是我们dump出来的tl嘛!
--=================================
通过dbms_stats搜集统计信息和analyze计算统计信息有点差别,
dbms_stats搜集统计信息计算avg_row_len时不包括row header占用的3byte:
SQL> exec dbms_stats.gather_table_stats('SYS','TT');
PL/SQL 过程已成功完成。
SQL> select avg_row_len from dba_tables where table_name='TT' and owner='SYS';
AVG_ROW_LEN
-----------
14
SQL> select vsize(id)+1+vsize(name)+1 from tt;
VSIZE(ID)+1+VSIZE(NAME)+1
-------------------------
5
6
7
8
9
10
11
12
13
14
15
VSIZE(ID)+1+VSIZE(NAME)+1
-------------------------
16
17
18
19
20
21
22
23
24
已选择20行。
SQL> select trunc((5+24)*10/20) from dual;
TRUNC((5+24)*10/20)
-------------------
14
SQL>