We are thinking to use Oracle compress table for data archiving via TTS, by creating a single table for every month, and then moving it to a cheaper storage. Since the history data will not be modified any more and will not be queried offten, it's possible to use compress table to save a lot of storage, and then save a lot of operation cost.
Two things we need to take into account. How much space we can save? How much faster we can convert non-compressed tables to compressed tables? We are quite happy with the space saving after we tested 5 millions rows.
TABLE_NAME BLOCKS
------------------- -----------
CR_NOCOMPRESS 10987
CR_COMPRESS 2219
But after we tested the CTAS speed of 1gb data on SUN Netra-T12 machine, we really feel sad. It took 6 times longer compare to non-compress table.
17:37:13 SQL> CREATE TABLE CR_NOCOMPRESS NOLOGGING ...;
17:37:56 SQL> DROP TABLE CR_NOCOMPRESS; --43 seconds
17:38:01 SQL> CREATE TABLE CR_COMPRESS NOLOGGING COMPRESS ...;
17:41:13 SQL> DROP TABLE CR_COMPRESS; -- 253 seconds
Any good ideas to speed it up? Can you help to test 1gb data on various hardware platforms? Because we have too much data (several TB) to be compressed.
Comments (2)
Fail over the box to another box with faster CPUs (not necessarily more CPUs) and do CTAS compress. After that, fail over back and return the faster-CPU box to whoever you borrowed from.
Posted by Yong Huang | November 2, 2007 10:27 AM
The final solution:
1, alter session enable parallel dml
2, create table xxx parallel x as
Now it run really fast.
Posted by anysql | November 2, 2007 3:44 PM