Hybrid Columnar Compression enables us to implement different levels of data compression and provides cost-savings at storage level and performance improvements due to reduced I/O when accessing large tables. When we Implement HCC. Tables are organized and stored in compression unit. In this compression unit, data is stored in columnar format and after it is compressed. similar values inside the compression unit are grouped and will enhance the expected compression ratio.
Two types of Hybrid Columnar Compression
1) Query optimized compression will help fetch large data workloads such at Data Warehouses
As we know data warehousing environment have historical data. Here compressing helps in storage saving and helps in analytic performance
2)Archive compression provides the highest degree of compression and is targeted for particular accessed data ( partition on the table) which are kept online.
On OLTP systems, Hybrid Columnar Compression can be used to compress older, less active data while newer, more active and update intensive data can be compressed using Advanced Row Compression.
For example we can create different HCC compression Types while creating table
HCC compression available are:
compress for query low;
compress for query high;
compress for archive low;
compress for archive high;
for example we can create a table with different partition levels and apply different HCC for different partitions as shown below
create table world (id num(10))
partition by range (id)
(partition p1 values less than (100000) nocompress,
partition p2 values less than (200000) compress for archive low,
partition p3 values less than (300000) compress for query high,
partition p4 values less than (maxvalue) compress for query low)
enable row movement;
following query will display type of compression level is implemented on the partition table
select partition_name, compression, compress_for from dba_tab_partitions where table_name like ‘WORLD’;
from the above table, we can confirm that, we can apply different HCC levels to provide cost saving at storage level and to improve performance of sql query's
lets say, of the table is partition is unused by the application, we can compress with HCC option "compress for archive high"
sql> alter table world modify partition p4 compress for archive high;
HCC is mostly compatible with OLAP dataware housing environments
for OLTP we can use basic compression techniques introduced in 12c AD0 :)
---Nikhil Tatineni--
---Exadata---
Two types of Hybrid Columnar Compression
1) Query optimized compression will help fetch large data workloads such at Data Warehouses
As we know data warehousing environment have historical data. Here compressing helps in storage saving and helps in analytic performance
2)Archive compression provides the highest degree of compression and is targeted for particular accessed data ( partition on the table) which are kept online.
On OLTP systems, Hybrid Columnar Compression can be used to compress older, less active data while newer, more active and update intensive data can be compressed using Advanced Row Compression.
For example we can create different HCC compression Types while creating table
HCC compression available are:
compress for query low;
compress for query high;
compress for archive low;
compress for archive high;
for example we can create a table with different partition levels and apply different HCC for different partitions as shown below
create table world (id num(10))
partition by range (id)
(partition p1 values less than (100000) nocompress,
partition p2 values less than (200000) compress for archive low,
partition p3 values less than (300000) compress for query high,
partition p4 values less than (maxvalue) compress for query low)
enable row movement;
following query will display type of compression level is implemented on the partition table
select partition_name, compression, compress_for from dba_tab_partitions where table_name like ‘WORLD’;
from the above table, we can confirm that, we can apply different HCC levels to provide cost saving at storage level and to improve performance of sql query's
lets say, of the table is partition is unused by the application, we can compress with HCC option "compress for archive high"
sql> alter table world modify partition p4 compress for archive high;
HCC is mostly compatible with OLAP dataware housing environments
for OLTP we can use basic compression techniques introduced in 12c AD0 :)
---Nikhil Tatineni--
---Exadata---
