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?

2 Answers 2


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 Mar 27, 2015 at 8:11
  • 1
    yes you're right. the SQL was wrong. I fixed it - only one partition should be used.
    – ibre5041
    Mar 27, 2015 at 8:26

You can rebuild the indexes after or use the


when executing your statement. E.g.


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 and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.