1

To save space on disk I want to compress some partitions of my tables:

alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations;

I have also indexes on this partition.
Should I recreate indexes after compression, or is it not necessary?

3

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use:

ALTER TABLE table_name MOVE partition TEST_TAB_2014 COMPRESS;

And then rebuild(not recreated) all indexes manually, preferably with reasonable parallel option.

PS: you should also read something about various compression options Oracle offers. Unfortunately it is not simple from both technical and also licensing perspective.

Briefly, Oracle supports these compression options:

  • BASIC, offline, block level. Usually the bigger the tablespace's block size the better compression ratio. This one is used only when moving tables/partitions, or when using direct path load (/* +APPEND */ hint)

  • OTLP, online, row level, being part of advanced compression extra cost option.

ALTER TABLE … COMPRESS FOR OLTP  This approach will enable OLTP Table Compression for all future DML -- however, the existing data in the table will remain uncompressed.

ALTER TABLE … MOVE COMPRESS FOR OLTP  This approach will enable OLTP Table Compression for future DML and also compress existing data.

  • COLUMNAR, works on column level, this one can also used for LOB compression. You can use it only on Exatada or when you use some SAN/NAS storage from Oracle.
  • I do not fully understand why your option is doing actual compression and mine not. I am issuing move command also. In fact I need to compress only historical partitions (which are read only) and not whole table – Michał Herman Mar 27 '15 at 8:11
  • 1
    yes you're right. the SQL was wrong. I fixed it - only one partition should be used. – ibre5041 Mar 27 '15 at 8:26
0

You can rebuild the indexes after or use the

WITH UPDATE INDEX

when executing your statement. E.g.

ALTER TABLE table_name MOVE COMPRESS FOR ALL OPERATIONS WITH UPDATE INDEX;

Note. Your statement will not compress existing data: only future data inserted.

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.