how to estimate table size?

[parijat67]

Hi everybody,

i want to estimate the table sizes for each of my tables before inserting 1 million records in each of them. I also want to estimate the corresponding size of the indices. could any one of you pls guide me on how to go about it? Pls take the example of Employee table and mention the steps. At present I am taking the help of TOAD to estimate the table sizes. I want to know how can we do it without taking help from any of the tools.

The motive behind doing all this is to foretell the clients about the expected size of database after they have fed in i million records in each of the tables.

thanks a lot

Parijat Paul

[marcio.68]

select table_name table, (sum (data_length) / 1048576) * 1000000 "Length MB"
from dba_tab_columns
where owner = 'owner'
group by table_name
;

[parijat67]

hi,

thanks a lot for your mail. i ran this query of yours and found that the size of only one record in the following table "TB_PROPERTIES_TEXT" will be 160 bytes and it would be 152MB for 1 million records. Pls mention about how oracle is reaching upto this figure of 160 Bytes. I am providing the table structure so that you can do the exact calculations based on the column datatype.

TB_PROPERTIES_TEXT
-----------------------------
COMPANYID number
OBJECTTYPEID number
OBJECTID number
PROPERTYID number
SEQUENCE number
VALUEPROPERTY varchar2(50)

Pls also mention about how to calculate the index sizes for a given table.

thanks once again

Parijat Paul

[parijat67]

well i got the answer to my question.. its (22*5 + 50) = 160 bytes.
But this is the total maximum size for a row of data and most of the time this wont be fully occupied by a given row. So, to plan the table sizing in a reasonable way, my question is how can we estimate the size of a new row based on the average size of existing data in the table?

thanks

Parijat Paul

[marcio.68]

I made the test and it did not give to 160KB and yes 160 MB. I have the habit esteem the size of the short while bigger table of the one than its initial size, this prevents spalling and for the NEXT I normally place I eat 10% you the total size of the table.

SQL> Create table teste
2 (COMPANYID number,
3 OBJECTTYPEID number,
4 OBJECTID number,
5 PROPERTYID number,
6 SEQUENCE number,
7 VALUEPROPERTY varchar2(50))
8 ;

Table created.

SQL> select table_name tabela, (sum (data_length) / 1048576) * 1000000 "Length MB"
2 from dba_tab_columns
3 where owner = 'MARCIO'
4 group by table_name
5 ;

TABELA Length MB
------------------------------ ----------
PLAN_TABLE 1566.8869
TESTE 152.587891