Is Oracle Advanced Compression underestimated?

Abraham Lincoln said: “I can make more generals, but horses cost money”.

Often all I hear about Oracle Advanced Compression is: “.. but it is an extra option on top of Enterprise Edition”. And thus its hidden power is often neglected.

Advanced Compression is not just about having the data occupy less space on disk, tape or in the network. It is also about better performance of the database, deduplication and the utilization of less hardware resources: memory and CPU. Thus, you can consolidate more databases on the same server and reduce the license costs.

I will try to compress this blog post, I will make it as short as possible:

1. What and which tables to compress?

Compress the segments that are involved in most reads: physical, logical or unoptimized. Consider also the segments that are used in SQL statements having most reads.

But do not compress all of them! Use DBMS_COMPRESSION.GET_COMPRESSION_RATIO in order to verify that you will benefit from the compression. Do only those with “Compression Ratio” 2 to 1 and higher. Here is an example of how you can estimate the compression ratio:

But when you store the XMLs as securefiles, use the CLOB option instead of the binary XML option in order to avoid the double compression which will have the reverse effect: the compressed XML will occupy more space than the non-compressed one. In other words use: “STORE AS SECUREFILE clob (COMPRESS HIGH)” instead of “STORE AS SECUREFILE binary XML (COMPRESS HIGH)”.

Thank You. The problem I have in implementing HCC is :
1. I need to partition the table with old partitions as warehouse compression and new partition as oltp
2. change the appln code to use new partition key
3. what tables should I implement HCC for?
Thanks

[…] Tables with a compression ratio above 2.0. For doing to I wrote a small SQL script you can execute and it will tell you the compression ratio for a specific table. The SQL code is not my creation and I don’t remember where I found it, probable on Julian Dontcheff’s Blog: […]

Julian is the Global Database Lead of Accenture. His primary responsibility is managing and leading the Global Oracle Technology Practice which includes Autonomous Cloud, IaaS, PaaS, Database Services, Engineered Systems, Java, Middleware, Security and all other areas falling under Oracle Technology. He is also the Accenture-Enkitec Group Managing Director for ... Continue reading →