1

I'm working with Oracle 11g and a requirement came down the pipeline to "[C]ompress our data [sic]." And I'm wondering how compression will affect future data.

The actual PL/SQL isn't an issue (something along these lines: ALTER TABLE SOME_TABLE MOVE COMPRESS;), but I'm told by the DBA that MOVE COMPRESS will only compress the existing data, and that any further transactions will require explicit compression. After running that line, looking at the table's storage properties, Table Compression is properly set and everything, but that leads me to believe that this will go on in the background.

I guess my question is this: Do I need to run MOVE COMPRESS (or something similar) after new transactions, or will Oracle compress the data going forward? If not, is there something I can amend to my ALTER TABLE statements that will enforce this?

1 Answer 1

2

Your DBA was kind of correct, for the typical scenario.

MOVE COMPRESS compresses existing data with Basic compression. Any new data inserted with regular insert will be uncompressed. New data inserted with direct-path insert will be compressed.

If you want all new data to be compressed, then you can use OLTP compression. Then syntax for that is COMPRESS FOR OLTP. OLTP compression requires extra license as it is part of the Advanced Compression option.

2
  • This probably sounds like a terrible idea/question, but: let's say I wanted to stick with basic compression for the moment. Would there be any danger in a a scheduled job that'd run MOVE COMPRESS on a regular basis? Like, if there's some potential cascading issue, or does it just compress what hasn't been already?
    – Dortimer
    Jan 29, 2020 at 22:00
  • A MOVE operation copies the whole table, not just the uncompressed data. In 11g, a MOVE operation makes all indexes on the table UNUSABLE so you will need to rebuild them. In 11g, a MOVE operation locks the table during the whole process, meaning no other DML or DDL operation is possible on the table during that. Jan 29, 2020 at 22:13

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.