About Exadata Hybrid Columnar Compression
- Oracle introduced basic compress feature from Oracle 9i version.
- Exadata Hybrid Columnar Compression can be specified at Table or Table Partition and Tablespace Level.
- Not Suitable for data which is frequently changing.
- Locking at CU level instead of row level so potentially many rows could be locked instead of single row
- Data needs to be loaded using direct path loads like Parallel DML, INSERT /*+ APPEND*/, direct path SQL Loader
Two types of compression options are available for Exadata –
- Warehouse Compression – optimized for query performance as its reduced the I/O and mostly for for data warehouse type of applications. With this option, compression can be up to 10x.
- COMPRESS FOR QUERY {LOW | HIGH}
- Archive Compression – optimized for maximum compression ration for space and recommended to use this option if data changes rarely. With this option, compressed can be 10x to 50x.
- COMPRESS FOR ARCHIVE {LOW | HIGH}
Data organization with Hybrid Columnar Compression:
- Exadata Hybrid Columnar Compression data is organized into sets of rows called Compression units (CU). Data is Organized by column in CU and then compressed.
- Data is organized by column during loading
- Each column is compressed separately
- HCC is supported by Smart Scan
- Table is organized into compression units
Example of Exadata Hybrid Columnar Compression
SQL> connect smartscan/samartscan
Connected.
SQL> select count(*) from sales;
COUNT(*)
----------
20000000
SQL> select sum(bytes)/1048576 SIZE_MB from user_segments where segment_name='SALES';
SIZE_MB
----------
992
Example of COMPRESS FOR QUERY:
SQL> create table sales_compression_low
compress for query low
nologging parallel 4
as select * from sales;
Table created.
SQL> select sum(bytes)/1048576 SIZE_MB from user_segments where segment_name='SALES';
SIZE_MB
----------
992
SQL> select sum(bytes)/1048576 SIZE_MB from user_segments where segment_name='SALES_COMPRESSION_LOW';
SIZE_MB
----------
228.1875
Example of COMPRESS FOR ARCHIVE:
With archive compression option, Table has been compressed from 992 MB to 5.25 MB. Compression ratio is showing so high because of data. As most of records for columns are containing same data so compression ratio is high. But in real life data it could be up to 50%.
SQL> create table sales_compression_high
compress for archive high
nologging parallel 4
as select * from sales;
Table created.
SQL> select sum(bytes)/1048576 SIZE_MB from user_segments where segment_name='SALES_COMPRESSION_HIGH';
SIZE_MB
----------
5.25
