All of Oracle’s database technologies fed into the backbone of Exadata over 10 years ago — but does the process work in the other direction? Perhaps it wasn’t obvious to me at first, but some of the features of Oracle In-Memory, first appearing in Oracle Database in version 12c led me to believe that there was some cross-pollination at work.
In the Oracle documentation for the DBMS_COMPRESSION package, which contains procedures that will either analyze a database block for its compression type or tell you how much a segment would benefit from each type of compression algorithm, I was reviewing Table 36-1 with the constants for each type of compression available. These particular constants intrigued me having worked extensively with both HCC on Exadata storage and In-Memory on every Oracle hardware platform. Here are the particular constants from that table that piqued my interest:
The constants for LOW and HIGH are inexplicably flipped, but the implied correlation is still obvious.
Sample Compressed Table
To do the comparison, I started with a copy of DBA_TAB_COLS. It has a variety of columns that can compress at varying levels and had enough rows to show the difference between compression levels for HCC or In-Memory. When that table is uncompressed as a heap-based table segment, it’s about 200 MB.
HCC versus In-Memory Compression
The results were somewhat correlated —
The compression levels were comparable except for Advanced Compression (ROW STORE COMPRESS ADVANCED), which doesn’t seem to have a counterpart within In-Memory. I had initially suspected that COMP_INMEMORY_DML was the logical equivalent since Advanced Compression was designed to support frequent DML transactions, but apparently that’s not the case. DML compression for In-Memory does compress a bit, but the documentation does say for MEMCOMPRESS FOR DML that “this method is optimized for DML operations and performs little or no data compression.”
It might be a coincidence; parts of the documentation for In-Memory sound like it’s a pure columnar format when in the In-Memory area of the SGA; other parts of the same Oracle Documentation talk about In-Memory CUs that have giant chunks of each table column as well as the equivalent of Exadata cell server storage indexes. But it could be the intention that lines up across compression technologies and not the actual mechanics. Next I’ll do some SGA memory block dumps of In-Memory tables and columns to see if they’re stored and compressed the same way that HCC tables are stored in Exadata storage cells.
Some of the advantages of Oracle In-Memory that use Intel Xeon vector processing need the data in a more usable format. This may account for some of the differences in how HCC tables are stored on Exadata versus how column values are stored in-memory. Most numeric and short string data types are stored in fixed-width columns to facilitate CPU vector instruction processing.
If your Oracle Database server has terabytes of free memory that you don’t know what to do with — In-Memory can help with that as if you had Exadata HCC cell storage for key table columns in tier 1 memory: on the database server itself. Not to mention many other things you can get with Oracle In-Memory such as external tables marked as INMEMORY, join groups, IM expressions, and of course full transactional consistency between the In-Memory contents and the corresponding row store contents during DML.