We created a huge table (over 1.7 billion rows) that was hash partitioned with each partition compressed that had horrible performance during an update. Here is an explanation as to why updating a compressed table is not a good idea.
Oracle table compression is only useful for tables where data is inserted and not updated. Upon update the row is uncompressed and not re-compressed. Compression occurs when data is inserted with a bulk (direct-path) insert operation. A table can consist of compressed and uncompressed blocks transparently. Any DML operation can be applied to a table storing compressed blocks. However, conventional DML operations cause records to be stored uncompressed after the operations and the operations themselves are subject to a small performance overhead due to the nature of the table compression.
Bottom line: Consider using table compression when your data is mostly read only. Do not use table compression for tables that are updated frequently. UPDATE operations are 10-20% slower for compressed tables on average, mainly due to some complex optimizations that have been implemented for uncompressed tables, and not yet implemented for compressed tables.
In our case the update to just one of these huge partitions was running over 12 hours. So, the update was actually never finishing. IMHO, don't compress any table that may be updated later.
No comments:
Post a Comment