Hybrid Columnar Compression in 12.2

Oracle 12.2 introduced an interesting optimization for Hybrid columnar compression (HCC).  Until 12.2 we have use direct path load into HCC compressed segments for the data to be actually compressed, if we don’t use a direct path load it will still succeed in entering data into the segment, however this newly inserted data was not HCC compressed and there was no error message or warning about that.

 

Here is a test case from Oracle 11g database running on Exadata X7

 

demo@ORA11G> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g EE Extreme Perf Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

 

demo@ORA11G> create table t

  2  nologging

  3  compress for query high

  4  as

  5  select *

  6  from all_objects

  7  where 1 = 0 ;

 

Table created.

 

First we will start with a direct path load.

 

demo@ORA11G> insert /*+ append */ into t select * from all_objects where rownum <=10000;

 

10000 rows created.

 

demo@ORA11G> commit;

 

Commit complete.

 

So how do we verify the data loaded as part of the above dml, was compressed? Thankfully Oracle is well instrumented, and even more thankfully that instrumentation was not limited to performance. The Oracle provided a package dbms_compression features a method “get_compression_type”, which allow us to pass a ROWID and it will tell us the type of compression applied on that row.

 

demo@ORA11G> select dbms_compression.get_compression_type(user,'T',rowid) comp_type, count(*)

  2  from t

  3  group by dbms_compression.get_compression_type(user,'T',rowid);

 

COMP_TYPE   COUNT(*)

---------- ----------

         4      10000

 

Compression type =4 means HCC query high, all rows loaded using direct path were compressed based on the compression type set at the segment.

 

Now let’s repeat the test case without direct path (removing append hint) and see the type of compression getting applied.

 

demo@ORA11G> truncate table t;

 

Table truncated.

 

demo@ORA11G> insert into t select * from all_objects where rownum <=10000;

 

10000 rows created.

 

demo@ORA11G> commit;

 

Commit complete.

 

demo@ORA11G> select dbms_compression.get_compression_type(user,'T',rowid) comp_type, count(*)

  2  from t

  3  group by dbms_compression.get_compression_type(user,'T',rowid);

 

COMP_TYPE   COUNT(*)

---------- ----------

         1       8994

        64       1006

 

Compression type =1 means no compression, this means during convention path load, no compression is applied to the newly inserted data, therefore to have it effectively compressed all the load should be done using direct path load.

 

Repeating the same in Oracle 12c (12.2), we see this.

 

c##rajesh@PDB1> insert into t select * from all_objects;

 

82161 rows created.

 

c##rajesh@PDB1> commit;

 

Commit complete.

 

c##rajesh@PDB1> select dbms_compression.get_compression_type(user,'T',rowid) comp_type, count(*)

  2  from t

  3  group by dbms_compression.get_compression_type(user,'T',rowid)

  4  /

 

COMP_TYPE   COUNT(*)

---------- ----------

         4      82161

 

Though we did conventional path laod, all rows were effectively compressed. So there is no more need for the append hint for insert-select in 12.2? This would be great news, and should alleviate some issues people have from unknowingly using HCC without direct path inserts. And it’s good for concurrency (because direct path operations require a segment lock, whereas array inserts do not use a segment lock)! So array inserts into HCC segments can compress data in Oracle 12.2 even if we don’t specify APPEND hint.