Hybrid columnar compression exadata

To Estimate compression ratio:

DECLARE

   l_blkcnt_cmp     BINARY_INTEGER;
   l_blkcnt_uncmp   BINARY_INTEGER;
   l_row_cmp         BINARY_INTEGER;
   l_row_uncmp      BINARY_INTEGER;
   l_cmp_ratio       NUMBER;
   l_comptype_str   VARCHAR2 (200);

BEGIN

   DBMS_COMPRESSION.get_compression_ratio (
      scratchtbsname   => 'HMA_DM_SALES_DATA',
      ownname           => 'HMA_DM_SALES',
      tabname            => 'SLM_TH_VEHICLE_MONTHLY',
      partname           => NULL,
      comptype           => DBMS_COMPRESSION.comp_for_query_low,
      blkcnt_cmp        => l_blkcnt_cmp,
      blkcnt_uncmp     => l_blkcnt_uncmp,
      row_cmp            => l_row_cmp,
      row_uncmp        => l_row_uncmp,
      cmp_ratio          => l_cmp_ratio,
      comptype_str      => l_comptype_str
   );

   DBMS_OUTPUT.put_line ('l_blkcnt_cmp=' || l_blkcnt_cmp);
   DBMS_OUTPUT.put_line ('l_blkcnt_uncmp=' || l_blkcnt_uncmp);
   DBMS_OUTPUT.put_line ('l_row_cmp=' || l_row_cmp);
   DBMS_OUTPUT.put_line ('l_row_uncmp=' || l_row_uncmp);
   DBMS_OUTPUT.put_line ('l_cmp_ratio=' || l_cmp_ratio);
   DBMS_OUTPUT.put_line ('l_comptype_str=' || l_comptype_str);

END;

**********************************************************************************
We can create compression using CTAS or Alter table move

After using Alter to compress tables indexes will become unusable. So rebuild them again

CREATE TABLE OI_HIGH TABLESPACE COMP COMPRESS FOR QUERY HIGH AS SELECT * FROM OE.OI;

ALTER TABLE OI_TABLE MOVE COMPRESS FOR ARCHIVE LOW;


To uncompress
ALTER TABLE EMP MOVE NOCOMPRESS;(UNTESTED)

We have 2 types of compression
Archive and DWH

DWH means Query high or Query low used in DWH.. High or low means compression rates
Archive also has high/low  and it is used on tables that are not queried much.. Gives maximum storage savings




Very important document regarding Compression
http://www.oracle.com/technetwork/server-storage/sun-unified-storage/documentation/problemsolver-hcc-52014-2202692.pdf


To see what tables are compressed
SELECT table_name, compression, compress_for
FROM all_tables where compression='ENABLED';

SELECT * FROM (
  SELECT
    OWNER, SEGMENT_NAME, round((BYTES/1024/1024/1024),2) SIZE_GB
  FROM
    DBA_SEGMENTS
WHERE
      SEGMENT_TYPE = 'TABLE'
  ORDER BY
    BYTES/1024/1024/1024  DESC ) WHERE ROWNUM <= 100 ;


select 'Alter index  '||owner||'.'||INDEX_NAME||' REBUILD;' from dba_indexes where status='UNUSABLE';
ALTER TABLE HMA_PROD.PERSON MOVE COMPRESS FOR QUERY HIGH;