Tables with MLPPI requires much more space than the same one with PPI - why?

Database
Fan

Tables with MLPPI requires much more space than the same one with PPI - why?

Hello all,

we hav a large table (CDRs) with PPI defined on timestamp containing date & time when a record was loaded in DW.

CREATE SET TABLE CZ_PRD_TGT.AMAOUT_NA ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      AMAOUT_NA_ID VARCHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Amaout Network Activity Id' NOT NULL,
      REC_NUM_SEQ INTEGER TITLE 'Record Number Seq' NOT NULL,
     
      ...................... /* large number of columns here, not importat right now */

      LOAD_DTTM TIMESTAMP(6) TITLE 'Load Dttm' NOT NULL,
      HIST_TYPE INTEGER TITLE 'Historization Type' NOT NULL COMPRESS 990000005 )

PRIMARY INDEX AMAOUT_NA_NUPI_PPI ( AMAOUT_NA_ID )

PARTITION BY RANGE_N(CAST((ACTV_START_DTTM ) AS DATE AT TIME ZONE 'Europe Central')
                  BETWEEN DATE '2013-02-01' AND DATE '2013-09-30' EACH INTERVAL '1' DAY , NO RANGE, UNKNOWN);

However, because of a clear business requirement, multiple level partitionning would be much better - i.e. first level of partition should be the business validity of a record and the second level the should be the date & time when the records were loaded in DW:

CREATE SET TABLE CZ_PRD_MNC.AMAOUT_NA ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      AMAOUT_NA_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Amaout Network Activity Id' NOT NULL,
      REC_NUM_SEQ INTEGER TITLE 'Record Number Seq' NOT NULL,

       ...................... /* large number of columns here, not importat right now */

      LOAD_DTTM TIMESTAMP(6) TITLE 'Load Dttm' NOT NULL,
      HIST_TYPE INTEGER TITLE 'Historization Type' NOT NULL COMPRESS 990000005 )

PRIMARY INDEX AMAOUT_NA_NUPI_MLPPI ( AMAOUT_NA_ID )

PARTITION BY ( RANGE_N(CAST((ACTV_START_DTTM ) AS DATE AT TIME ZONE 'Europe Central')
BETWEEN DATE '2013-02-01' AND DATE '2013-09-30' EACH INTERVAL '1' DAY , NO RANGE, UNKNOWN),
RANGE_N(CAST((LOAD_DTTM ) AS DATE AT TIME ZONE 'Europe Central')
BETWEEN DATE '2013-02-01' AND DATE '2013-09-30' EACH INTERVAL '1' DAY ,NO RANGE, UNKNOWN) );

What we found was that the volume of table with MLPPI was remarkably higher - original table had ~ 1TB, the new table with MLPPI has ~1,3 TB!

Does anybony know what is the reason that MLPPI requires so much higher disk space?

Thanks to all in advance

Dag

Tags (3)
1 REPLY
Teradata Employee

Re: Tables with MLPPI requires much more space than the same one with PPI - why?

When using PPI/MLPPI, rows get bigger by 2 or 8 bytes (this depends on total count of created partitions , subpartitions are internally just partitions) ; 2Bytes when <= 65535 ; 8Bytes when >65535.

So on huge tables this might be disadvantage in matter of occupied permspace.

To calculate it, just use this formula (taken from manual)

(p1 - 1) * dd1 + (p2 - 1) * dd2 + ... + (pn-1 - 1) * ddn-1 + pn

 

where n is the number of partitioning expressions

pi is the value of the partitioning expression for level i

di is the number of partitions for level i

ddi is the product of di+1 through dn

dd = d1* d2 * ... * dn <= 65535

dd is the total number of combined partitions

Cheers

-vh-

Cheers
-=[VH]=-