What would be the best approach?

newborndba · June 3, 2014, 9:16am

I have a table in one of my DB. The DB is about 300 gig - of that 249 gig is in this table. The data is somewhat important but even if we delete half of it won't affect anybody.

I would like to reclaim some space back so my question is what would be the best approach to accomplish this task. Delete half of the data? Shrink the table?

Someone suggested to enable row movement followed by the shrink space?

Any suggestion?

SriniShoo · June 3, 2014, 9:21am

How about compressing the table?

alter table aradmin.t253 move compress;

radoulov · June 3, 2014, 9:28am

What technology: Oracle, MySQL, PostgreSQL, ... other?

newborndba · June 3, 2014, 2:27pm

Oracle

---------- Post updated at 01:27 PM ---------- Previous update was at 01:26 PM ----------

Compressing should be good. It is not as shrinking the table right?

rbatte1 · June 4, 2014, 6:48am

You may be into an export/truncate/import to remove the data because otherwise you will generate vast amounts of redo. Either the transaction will fail and rollback (so you don't delete anything) or you may hang your database. I suppose you could do it as an un-logged transaction, but then a failure is unrecoverable.

The problem with export/truncate/import might be with the fill ratio. When adding data organically, it will probably fill pages/chunks/blocks (depending on your DB terminology) before allocating the next. With an import, it will likely leave a portion for inserting to later, just in case you add something that would better sit on the same page.

It's all a bit obscure, but the thing you have to bear in mind is that if you export your data with a query that only gives you 50% of it, you might only retrieve 25% of the space allocated.

At towards 300G, any mass manipulation is going to be difficult.

The space you have in your redo log filesystem is going to be critical.

Robin

newborndba · June 4, 2014, 9:07am

rbatte1:

You may be into an export/truncate/import to remove the data because otherwise you will generate vast amounts of redo. Either the transaction will fail and rollback (so you don't delete anything) or you may hang your database. I suppose you could do it as an un-logged transaction, but then a failure is unrecoverable.

The problem with export/truncate/import might be with the fill ratio. When adding data organically, it will probably fill pages/chunks/blocks (depending on your DB terminology) before allocating the next. With an import, it will likely leave a portion for inserting to later, just in case you add something that would better sit on the same page.

It's all a bit obscure, but the thing you have to bear in mind is that if you export your data with a query that only gives you 50% of it, you might only retrieve 25% of the space allocated.

At towards 300G, any mass manipulation is going to be difficult.

The space you have in your redo log filesystem is going to be critical.

Robin

Thanks for the explanation. I am not an Oracle DBA and didn't realize that deleting multiple gigs of data at the same time will impact the redolog unless we delete 1000 rows and commit and repeat this process (We won't do that). I think compressing or shrinking would be an option to consider for now or go with what you already have suggested.

Thanks again

gandolf989 · June 4, 2014, 1:18pm

Deleting data always creates redo. you can truncate the table, but then you would not have any data and truncate is an unrecoverable operation. It can't be rolled back. If there are columns that you don't need, dropping columns will eventually cause the table to shrink. You want to first drop columns that aren't needed, then do a alter table move compress and you should end up with a smaller table. CHAR's can take up the most space, because they use the stated amount even if they are empty. If the table has blobs, you can store them in a different table space, which might help as well.