Estimating Table-Size

Hi,

I try to estimate table-size in MB with PL/SQL, but I have a great difference between my results and the size-calculation of the programm TOAD. If anyone is interested I can send the whole procedure.

The treatment is like this:

The procedure has as input-parameters table_name, owner and the number of table_rows.

1. Get PCT-FREE from SYS.DBA_TABLES (i.e. 10%)
2. Get Block-Size from SYS.v$parameter (i.e. 8192bytes)
3. Analyse Table-rows (SYS.DBA_TAB_COLUMNS): Count table-rows "<250bytes" and ">=250bytes" (column-type "NUMBER": column-bytes = data_precision + data_scale else : column-bytes = data_length)
4. Average row size = SELECT AVG(VSIZE(Column1)+1 + VSIZE(Column2)+1 ...)
5. AVG-R-SIZE = AVG-R-SIZE +3 + "<250bytes" + (3* ">=250bytes")
6. x = Block_Size / AVG-R-SIZE
7. Round x down to the next integer: y = Block_Size - ((Block_Size - (52+(4*x))) * PCT_FREE
8. Table_Size = (number_table_rows / x) * Block_Size
9. Calculate Table_Size in MB or KB

What is wrong?

O. Braun Received on Sun Jan 25 2004 - 06:02:17 CST