About Partitioned Primary Index (PPI)

Database
Enthusiast

About Partitioned Primary Index (PPI)

Hi everyone..

Well, I have a table with the following structure and I need to add  a new RANGE by adding the field People_Subscription_Start_DT:

PARTITION BY RANGE_N (People_Subscription_Start_DT DATE '2008-09-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY, NO RANGE);

My question to all of you is: Do I need to add the field People_Subscription_Start_DT as part of the PPI? Is it a MUST when adding a RANGE to put the field as the PPI?

Thanks in advance

CREATE MULTISET TABLE TR_PEOPLE_ACT ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
People_Subscription_Start_DT DATE FORMAT 'YYYY-MM-DD',
People_ID Integer,
People_Subscription_End_DT DATE FORMAT 'YYYY-MM-DD',
People_Type Varchar(8)
PRIMARY INDEX PI_TR_PPL_ACT ( People_ID) 

Tags (2)
2 REPLIES
Enthusiast

Re: About Partitioned Primary Index (PPI)

hi,

you do not need to add the field People_Subscription_Start_DT in PPI.

yes you need PPI if you want to add range.

Enthusiast

Re: About Partitioned Primary Index (PPI)

Hi Vikas, thanks for ur response...So, what You say is that If I add a new range by  People_Subscription_Start_DT, Do I need to include People_Subscription_Start_DT as part of PI as it follows? Every time a partition is created on a table the field MUST be part of the PI?

1

2

3

4

5

6

7

8

9

10

11

12

CREATE MULTISET TABLE TR_PEOPLE_ACT ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      People_Subscription_Start_DT DATE FORMAT 'YYYY-MM-DD',

      People_ID Integer,

      People_Subscription_End_DT DATE FORMAT 'YYYY-MM-DD',

      People_Type Varchar(8)

PRIMARY INDEX PI_TR_PPL_ACT ( People_ID, People_Subscription_Start_DT) 

PARTITION BY RANGE_N(People_Subscription_Start_DT  BETWEEN DATE '2012-09-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY ,

 NO RANGE);