HCC Compression Unit Sizing
Since the beginning Oracle has provided four compression levels to offer a trade-off between the compression ratio and various other factors including table scans and the performance of single-row retrieval. I can not emphasize enough that the various trade offs mean that YMMV with the different levels and you should always test what works best with your application and hardware when choosing the level. Historically people have rarely used Query Low since the fast compression with reduced compression ratio means that the extra disk I/O is slower than the cost of decompression with Query High. The one time that Query Low makes sense on spinning disks is if you still have a significant number of row retrieval operations (including from index access joins).
NMVe Flash
X5 introduced NVMe technology which means that the extra I/O from Query Low is faster than ZLIB decompression which makes Query Low beneficial. So we needed to reassess the sizing of Compression Units. from 11.2.0.1 to 12.1.2.4 the sizing guidelines are as follows:
| Name | Target Rows | Target Minimum Size | Target Maximum Size | Compression |
| Query Low | 1000 to 8000 | 32 kb | 32 kb | LZO |
| Query High | 1000 to 8000 | 32 kb | 64 kb | ZLIB |
| Archive Low | 8000 | 64 kb | 256 kb | ZLIB |
| Archive High | 8000 | 256 kb | 256 kb | BZ2 |
So, for example, Compress for Query High aims to pivot around at least 1000 rows and create a minimum compression unit size of 32 kb and a maximum of 64 kb. Using 12.1.2.3 I ran these against a TPC-H Lineitem table than contained between 1 and 6 copies of each column.
For Query Low fixed 32 kb CUs this gave us the following:
| Additional copies of lineitem | Rows per 32 kb CU |
| 0 | 2797 |
| 1 | 580 |
| 2 | 318 |
| 3 | 216 |
| 4 | 162 |
| 5 | 129 |
and for Query High 32 to 64 kb CUs this gave us:
| Additional copies of lineitem | Rows per 32 kb CU | CU ave size |
| 0 | 5031 | 32 |
| 1 | 1010 | 32 |
| 2 | 936 | 51 |
| 3 | 794 | 63 |
| 4 | 595 | 67 |
| 5 | 476 | 63 |
so we see that the CU size remains as 32 kb as long as we are getting a 1000 rows or more then increases in size to 64 kb to try and fit in at least 1000 rows.
It became clear that this size range was inadequate for wide tables so to get more efficient compression and longer column runs for faster predicate performance (and also better CELLMEMORY rewrites) we removed the fixed size for Query Low and increased the max:
Query Low: 32 kb to 64 kb
Query High: 32 kb to 80 kb
This will not affect narrow tables at all but wider tables should see better table compression and faster scans at the cost of slightly slower single row retrieval for data loaded by 12.2 RDBMS. If you have HCC wide tables and typically cache them on flash cache you should consider re-evaluating Query Low for data loaded in 12.2 (or use Alter Table Move Compress to recompress existing data).
Roger