How to calculate ROW size

How to calculate ROW size

This is the Row Header structure ( cf. "Oracle database 11g Concepts Manual 12.Logical Storage Structures " )
(a) +-Row Overhead(2Byte) +
(b) | Number of columns(1Byte) +
(c) | Cluster key ID(if clustered,1Byte) +
(d) +- ROWID of chained row piece(if any, 6Byte)

1. If one block can bs in one Row,

57 byte + 23* INITRANS + 4 (table directory) + 2* (the number of ROWs for one block) is allocated.

The overhead for each ROW is 3 bytes (a+b).
And each column's overhead is 1 byte if it is less than 250 bytes, or it is 3 bytes if it is more than 250 bytes) (e)
But, if null columns are located at the very end, the overhead of these columns is saved. (*)

Factor of Tuning

ex1)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)
and
Data : ('a',null,null,null,null,null)

then,

3B(a+b) +
2B(for data 'a', column overhead(1byte) included) = 5 bytes

; if there are 10 rows of this data, the total size will be 50 bytes

ex2)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)
and
Data : ('a',null,null,'d',null,null)

then,

3B(a+b) +
2B(for data 'a', column overhead included) +
2B(for two null columns ) +
11B(for data 'd',column overhead(1byte) included) = 18 bytes

ex3)

A varchar2(10), B number
and
Data : ('a',1000000000)

then,

3B(a+b) +
2B(for data 'a',column overhead included) +
3B(for data 1000000000,column overhead(1byte) included) = 8 bytes

; number 1000000000 is converted to 1*E9 internally (integer, decimal, float) -> 2bytes
; number 1000000001 is internally (?) -> 6 bytes

If lots of data is in it, x byte will be increased for the chain of the next block, and if we calculate this with the avg_row_len of user_tables, this size will be much larger bytes than the average row size.
=> (available space / avg_row_len)
* Available space = (block size - block header) - (block size - block header) * 0.1 (if pctfree is 10%)......

 

2. If one block cannot be in one Row,

Chain will be happened. So ROWID(6 bytes) as the overhead of this will be added in order to point chained block on the row header.
And the length indicator(if <= 250 bytes, then 1 bytes. if > 250 bytes, then 3 bytes) of the column which is linked with the chained block will be added will be added.

ex4)

SQL> create table Tbl1 ( c1 varchar2(2000)) pctfree 0;
SQL> Data insert ..

(a) (b) (d) (e)
(e) (Total Size)

; 1800 characters inserted(1 block) => 2 + 1 + 3 + 1800 = 1506 bytes

; 1900 characters inserted(2 block) => 2 + 1 + 6 + 3 + about 1850 + 1 + 50 = 1913 bytes

; even if pctfree = 50, at least one recorder should be inserted.


So,

SQL> analyze table Tbl1 compute statistics;

SQL> select table_name, num_rows, blocks, avg_row_len 

from user_tables;

SQL> select vsize(c1) from Tbl1;

 

If you have any question, please leaver your comment.