Forum Stats

  • 3,855,563 Users
  • 2,264,523 Discussions
  • 7,906,067 Comments

Discussions

OLTP compression: uncompress tables

jhall
jhall Member Posts: 39
edited Sep 12, 2012 12:47PM in Advanced Compression
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.
1962300

Best Answer

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    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

Answers

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    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
  • jhall
    jhall Member Posts: 39
    edited Sep 12, 2012 12:47PM
    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
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    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
This discussion has been closed.