In this blog, and my next blogs, we are going to try something a bit different. We are going to discuss some of the most common compression-related questions that I have been asked. A very common question, and a question where the answer has changed over the last few years, is the following:
Question: Should we compress the entire database?
A few years back my answer, to this question, would have been to suggest that you did not have to compress the entire database and instead could compress the largest tables first, and then decide if you really want to compress your smaller tables later. In many cases, users would compress their largest tables and then often not compress the smaller tables/partitions. In those days, I only really talked about the storage savings related to compression, and thought that the storage savings for a small table, say a few GB’s, may, by itself, not be that significant.
While this approach to the database compression question is still one that many users choose to implement, it did fail to take into to account some additional considerations that are very relevant to the question we are discussing.
What I had failed to consider, and came to realize over time after talking with users, is that there are indeed benefits to compressing even the smallest tables. First, compressing even a very small table, say just a few GB will often yield performance benefits. Reducing the size of a table/partition by 50% (which is the average compression for Oracle Advanced Row Compression) can benefit query performance by reducing the number of blocks (reducing I/O) and may make the newly even smaller table memory resident in the data buffers (remember that we keep data and indexes compressed in memory).
Even if the amount of space saved for a small table/partition is not, by itself, significant, remember that when you add up all the storage saved by even your smallest tables, that can be pretty significant. This storage savings, for the small tables, will also apply to your future storage needs as those small tables continue to grow in the future.
No matter if, you choose to compress your smaller tables/partitions or not, there are two best practices related to what tables/partitions not to compress when using Advanced Row Compression, these include:
If the table/partition is used as a queue, i.e. rows are inserted into the table, then later most or all of the rows are deleted, then more rows are inserted then deleted, then you shouldn't compress the table
Advanced Row Compression is NOT supported for use with tables that have LONG data types
I hope you found this discussion informative. In my next blog(s) we will discuss additional common compression questions. The database storage optimization adventure continues!