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 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.
Pls also mention about how to calculate the index sizes for a given table.
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?
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.