-1

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?

  • Adding a new column? so if you won't populate it, there is no additional cost involved. column usage will depend on how wide it is actually going to be. If a row then can't fit in a block, there will be row migration, or row chaining involved. Will you be going back and populating old data? that would play a role too. also compare the cost of adding a new table, means you need to join that everywhere and modifying process that inserts. i suspect in the end you will come out ahead in adding a column to the table. – Raj Aug 25 '16 at 13:52
  • .. continues from previous The easy way to estimate is take a 1-5% sample data from your existing table to create a new table, add column and populate it and then compare size before and after. that would give you some idea. – Raj Aug 25 '16 at 13:53
0

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.

-1

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.