compressionnetezzaaginity

How to compress in an existing table - Netezza Aginity


I have created the following table SCH_DB.PREDICTION_HIS

CREATE TABLE SCH_DB.PREDICTION_HIS
(
    PERIOD CHARACTER VARYING(6) NOT NULL,
    MONTHS_FORECAST CHARACTER VARYING(13) NOT NULL,
    SUBSCRIBER_ID CHARACTER VARYING(12) NOT NULL,
    IDENTIFICATION CHARACTER VARYING(30),
    ID_PLAN CHARACTER VARYING(11),
    DESCRIPTION_PLAN CHARACTER VARYING(75),
    RATE DOUBLE PRECISION,
    SENIORITY_PLAN DOUBLE PRECISION,
    PREQUALIFIED_QUOTA DOUBLE PRECISION,
    CUSTOMER_SENIORITY DOUBLE PRECISION,
    SEGMENT NATIONAL CHARACTER VARYING(12),
    PROB_CHURN DOUBLE PRECISION,
    SERIE INTEGER,
    ORDER_PROB_GENERAL DOUBLE PRECISION,
    TOP_N_GENERAL INTEGER,
    ORDER_PROB_SEGMENT DOUBLE PRECISION,
    TOP_N_SEGMENT INTEGER,
    TYPE_BASE NATIONAL CHARACTER VARYING(7),
    CUSTOMER_NAME CHARACTER VARYING(200),
    STATUS CHARACTER VARYING(20),
    DESCRIPTION_STATUS CHARACTER VARYING(50),
    SOURCE CHARACTER VARYING(3)
)
DISTRIBUTE ON (PERIOD, SUBSCRIBER_ID);

the same one that has 25258910 records

and the compression ratio is 2.42

/nz/support-IBM_Netezza-11.2.1.1-210825-0050/bin/nz_compressedTableRatio_v2 SCH_DB.PREDICTION_HIS
/nz/support-IBM_Netezza-11.2.1.1-210825-0050/bin/sedTableRatio_v2 SCH_DB.PREDICTION_HIS

  ....................................................................................
  .  The values below show the estimated size ratio of a compressed table to its     .
  .  uncompressed form. An uncompressed table is approximately <ratio> times larger  .
  .  than its compressed version.                                                    .
  .                                                                                  .
  .  The 'Compressed Size' is the actual amount of storage being used by the table.  .
  .  The 'Uncompressed Size' is an estimate based on mathematical calculations.      .
  ....................................................................................


Database: SCH_DB

Table/MView Name                 Ratio   Compressed Size    Uncompressed Size    Size Difference
================================ ===== =================== =================== ===================
PREDICTION_HIS                    2.42       2,567,045,120       6,211,530,330       3,644,485,210
================================ ===== =================== =================== ===================
Total For This Database           2.42       2,567,045,120       6,211,530,330       3,644,485,210

I have used the following command to compress but it returns error

ALTER TABLE SCH_DB.PREDICTION_HIS COMPRESS YES

enter image description here

Is it possible to compress this table, what is the statement to compress?


Solution

  • All tables now a days in netezza are compressed, the syntax to alter it on/off are kept for backwards compatibility only