Advanced compression

Estimation  des  avantages  qu  on  pouurait avoir    en  compressant  certaine  donnees:

1.BASIC compression, introduced in Oracle 8 already and only recommended for Data Warehouse
2.OLTP compression, introduced in Oracle 11 and recommended for OLTP Databases as well
3.QUERY LOW compression (Exadata only), recommended for Data Warehouse with Load Time as a critical factor
4.QUERY HIGH compression (Exadata only), recommended for Data Warehouse with focus on Space Saving
5.ARCHIVE LOW compression (Exadata only), recommended for Archival Data with Load Time as a critical factor
6.ARCHIVE HIGH compression (Exadata only), recommended for Archival Data with maximum Space Saving



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(100);
BEGIN
  FOR i IN (SELECT table_name
            FROM dba_tables
            WHERE compression = 'DISABLED'  and  owner='RA_PPR'  and  table_name   in ('RES_CHARGES','E_INTERLINE_MSGS')
            ORDER BY table_name)
  LOOP
    FOR j IN 1..5
    LOOP
      dbms_compression.get_compression_ratio(
        -- input parameters
        scratchtbsname   => 'RA_PPR',       -- scratch tablespace
        ownname          => 'RA_PPR',            -- owner of the table
        tabname          => i.table_name,    -- table name
        partname         => NULL,            -- partition name
        comptype         => power(2,j),      -- compression algorithm
        -- output parameters
        blkcnt_cmp       => l_blkcnt_cmp,    -- number of compressed blocks
        blkcnt_uncmp     => l_blkcnt_uncmp,  -- number of uncompressed blocks
        row_cmp          => l_row_cmp,       -- number of rows in a compressed block
        row_uncmp        => l_row_uncmp,     -- number of rows in an uncompressed block
        cmp_ratio        => l_cmp_ratio,     -- compression ratio
        comptype_str     => l_comptype_str   -- compression type
      );
      dbms_output.put_line(i.table_name||' - '||'type: '||l_comptype_str||' ratio: '||to_char(l_cmp_ratio,'99.999'));
    END LOOP;
  END LOOP;
END;
SELECT table_name
               FROM dba_tables
               WHERE compression = 'DISABLED'  and  owner='RA_PPR'  and  table_name   in ('RES_CHARGES','E_INTERLINE_MSGS')
               ORDER BY table_name;
/

en  sortir on   a  un  rapport  :
E_INTERLINE_MSGS - type: "Compress For OLTP" ratio:   1.000
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
RES_CHARGES - type: "Compress For OLTP" ratio:   3.700
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows

E_INTERLINE_MSGS - type: "Compress For Query High" ratio:   7.300
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
E_INTERLINE_MSGS - type: "Compress For Query Low" ratio:   3.900
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
E_INTERLINE_MSGS - type: "Compress For Archive High" ratio:  12.300
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
E_INTERLINE_MSGS - type: "Compress For Archive Low" ratio:   7.900
RES_CHARGES - type: "Compress For Query High" ratio:  25.000
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
RES_CHARGES - type: "Compress For Query Low" ratio:  10.500
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
RES_CHARGES - type: "Compress For Archive High" ratio:  31.300
Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows
RES_CHARGES - type: "Compress For Archive Low" ratio:  27.000