All Forums Database
Sen_td 20 posts Joined 08/11
29 Apr 2013
Casting in PPI definition

Hi All,

I have a PPI table defined as like below,

CREATE SET TABLE SAMPLE.TEST ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
    EMP_ID INTEGER,
    DATE_ID    INTEGER NOT NULL,
    DATE_F    DATE NOT NULL
)
PRIMARY INDEX PI_TEST ( EMP_ID )
PARTITION BY RANGE_N(CAST(DATE_ID AS DATE) BETWEEN DATE '2013-04-01' AND '2014-12-31' EACH INTERVAL '1' DAY ,
NO RANGE);

DATE_ID is sourced from DATE_F column(just DATE --> INTERGER conversion).

As we have users query this table frequently using the integer column DATE_ID. In PPI definition, we are casting an INTEGER column to DATE and storing.

My question:

Would this approach result equal or better performance gain compared to having the PPI defined on DATE_F column
without casting and advising the users to use DATE_F column instead of DATE_ID column? If yes/no please explain!
 

You must sign in to leave a comment.