OLTP compression: uncompress tables

jhall Sep 12, 2012 4:47 PM

I have read the Oracle whitepaper and the metalink technical note about advanced compression, and neither of these covered the procedure to uncompress OLTP tables after they have been compressed. We are running an SAP system, and there is a canned procedure to uncompress tables, but I think there are better ways to do this.

Anyone who has had experience uncompressing OLTP tables, would you mind sharing your procedure/experience on how you have done this? I have some large tables I will need to uncompress, and want to do this the fastest and most efficient way. Any input is appreciated.

Thanks.

1. Re: OLTP compression: uncompress tables

Hemant K Chitale Apr 28, 2011 4:09 PM (in response to jhall)
If you want to completely uncompress a table (i.e. uncompress all blocks), you would simply
ALTER TABLE tablename MOVE NOCOMPRESS NOLOGGING PARALLEL 4;
(The NOLOGGING PARALLEL 4 are optional -- I put them in for performance).

Hemant K Chitale
Like Show 0 Likes(0)

Actions
2. Re: OLTP compression: uncompress tables

jhall Sep 12, 2012 4:47 PM (in response to Hemant K Chitale)

Thanks Hermant,

All in one reorg statement, that is very efficient. Nologging is also a good idea, to cut down on the IO. I'll try this procedure, thanks.

Thanks
Like Show 0 Likes(0)

Actions
3. Re: OLTP compression: uncompress tables

Hemant K Chitale Apr 30, 2011 4:12 AM (in response to jhall)

Note that you have to REBUILD indexes on the table because the MOVE relocates table rows to new blocks.

So, for each index on the table
ALTER INDEX indexname REBUILD PARALLEL 4 NOLOGGING;

(as usual, the PARALLEL 4 and NOLOGGING are optional).

Hemant K Chitale
Like Show 0 Likes(0)

Actions

Go to original post