Compression test, 12cR2

HCC is available only on Oracle supplied storage, such as a ZFS storage appliance or (as in this case) an Exadata engineered system. Furthermore, it occurs only for direct loads: in my examples, using CTAS. This is the script I’m running for this (not very scientific) test:

set timing on
create table t1 as select * from all_objects;
create table t1_bas row store compress basic as select * from all_objects;
create table t1_adv row store compress advanced as select * from all_objects;
create table t1_cql column store compress for query low as select * from all_objects;
create table t1_cqh column store compress for query high as select * from all_objects;
create table t1_cal column store compress for archive low as select * from all_objects;
create table t1_cah column store compress for archive high as select * from all_objects;

select table_name,blocks from user_tables where table_name like 'T1%' order by 2;

The script creates a table using no compression, then using the basic and advanced de-duplication methods, then the four HCC algorithms. Here’s what happens:

x122>
x122> set timing on
x122> create table t1 as select * from all_objects;

Table created.

Elapsed: 00:00:01.71
x122> create table t1_bas row store compress basic as select * from all_objects;

Table created.

Elapsed: 00:00:01.54
x122> create table t1_adv row store compress advanced as select * from all_objects;

Table created.

Elapsed: 00:00:01.55
x122> create table t1_cql column store compress for query low as select * from all_objects;

Table created.

Elapsed: 00:00:01.50
x122> create table t1_cqh column store compress for query high as select * from all_objects;

Table created.

Elapsed: 00:00:01.92
x122> create table t1_cal column store compress for archive low as select * from all_objects;

Table created.

Elapsed: 00:00:02.57
x122> create table t1_cah column store compress for archive high as select * from all_objects;

Table created.

Elapsed: 00:00:13.41
x122>
x122> select table_name,blocks from user_tables where table_name like 'T1%' order by 2;

TABLE_NAME                         BLOCKS
------------------------------ ----------
T1_CQH                                 60
T1_CAH                                 62
T1_CAL                                 62
T1_CQL                                128
T1_BAS                                382
T1_ADV                                425
T1                                   1244

7 rows selected.

Elapsed: 00:00:00.45
x122>
x122>

The results show that the deduplication comes in at three or four to one compression ratio, and that HCC is around ten to one for Query Low, twenty to one for the others. The astonishing figure is that the Archive High algorithm is nearly eight times as slow as no compression. Most of the other algorithms are actually faster than no compression.
The lesson from this? Compression may give you huge space savings, but test the algorithms carefully. In another article I’ll look at the effects on subsequent SELECTs and DMLs.
Tests done on database release 12.2.0.0.3, Exadata.
—
John Watson
Oracle Certified Master DBA
https://skillbuilders.com