This is a very common question and a question where the answer has changed over the last few years.
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 you would like to see additional best practices related to database compression, please see this Oracle White Paper (pages 5 and 6)