Hybrid Columnar Compression, Column projection, Predicate Filtering, Storage Indexes, Bloom filters

Hybrid Columnar Compression:
Tables are organized into compression units (CUs) that are larger than database blocks. Compression Unit is usually arround 32k. With compression unit method, the data is organized by column that similar values close together so this improving the compression. There are 2 mode of compression:

1) Query Mode - 10x saving
2) Archive Mode - 15x to 50x savings


Column projection: Returns only columns of interest between the database tier and storage tiers.

Predicate Filtering:  Returns only rows of interest to the database tier.

Storage Indexes:  Maintain a max and min value for each 1 MB disk storage unit, up to eight columns of a table.  It eliminates time to read from the storage servers by eliminating no-matching rows as fre-filter like partitioning mechanism.

To disable / enable storage indexes:  alter system set "_kcfis_storageidx_disabled"=true / false

To disable/enable smart scan:  alter session set cell_offload_processing=false;

To  use the effectiveness of smart scan, You run the following query:

select sql_ID, physical_read_bytes, physical_write_bytes, io_interconnect_bytes eligible, io_cell_offload_eligible_bytes, io_cell_uncompressed_bytes, io_cell_offload_returned_bytes, optimized_phy_read_requests
from v$sql
where sql_text like 'your query here %';


Simple joins (Bloom Filters):
Reduce traffic tween parallel query slaves processes mostly in RAC.

To disable / enable bloom filter off loading
alter session set "_bloom_predicate_pushdown_to_storage"=false / true;