How can I estimate the size of a new table?

Question

We have tables with a lot of data (50M new rows each month, just in one table).

Now I want to know what would be the impact of adding a new column to those tables, or creating a new table. Something like: the new column on table X can increase the space used by 12% of its current size. Or: given that the new table is going to grow by 1000 rows/day, the expected size will be Y in one month.

I already found some information about the DBMS_SPACE package, but it seems useless because create_table_cost_colinfo() is returning useful information only for VARCHAR2.

Do you have any suggestion to "easily" estimate those information?

Answer 1

You can start by measuring the size that this table has and the number of rows. If you do this every hour/day/week then you get an idea on the growth of this table.

select   to_char(sum(bytes)/1024/1024, '999,999,999')||' Mb'
from     dba_segments
where    owner=upper('&TableOwner')
and      segment_name=upper('&Table')
/

select  count(*)
from    tableowner.table
/

Take into account that the size you get also contains the empty space that is allocated to the table. When the table was just extended and only 1 new record is written in this extension then the information is less correct. But given the fact that you have nearly 2M of rows per day the space per row will be quite accurate.

Answer 2

2 tables are definitely better from maintenance perspective. New table can be archived, dropped, truncated without affecting existing one. It can be physically stored in separate tablespace. Also, adding new columns will have much bigger overall impact compared to new table[s]. Execution plans for the queries that hit this table will be invalidated, which may potentially degrade performance a lot (assuming all is good at the current moment).

Another thing that storing , for instance , person's first name in one table, and last name in another would look quite odd . I'd rather focus on logical structure...