Testing the PPI waters


Testing the PPI waters

For our financial applications there are many tables that have columns accounting_year (samllint) and accounting_period (byteint).
These 2 columns may appear in the Primary Index both UPI and NUPI.
These 2 columns also may not be part of a UPI or NUPI.
The majority of the tables contain data for 2 years + the current year.

There seems to be many ways to partition the tables:
Partition by accounting_year + accounting_period; or
Partition by accounting_year * accounting_period; etc.

Partition by Case_N (
accounting_year=2005 and accounting_period=1,
accounting_year=2007 and accounting_period=12); etc.

The users will usually specify both accounting_year and accounting_period in their constraints.
The users will never specify (accounting_year + accounting_period) or (accounting_year * accounting_period) in their constraints.

Any ideas on the most effective way to partition tables using the above 2 columns?

Thanks for the feedback.


Re: Testing the PPI waters

For your tables whose PI is not a subset of {ACCOUNTING_YEAR, ACCOUNTING_PERIOD} columns , you can have a range expression of the following kind ... would help you with deleting old data and adding new ranges later.

Partition by RANGE_N (
ACCOUNTING_YEAR*100 + ACCOUNTING_PERIOD BETWEEN 200501 AND 200512 EACH 1, 200601 AND 200612 EACH 1, 200701 AND 200712 EACH 1

I would doubt if using either accounting_year or accounting_period on it's own for PI would be a good idea ( assuming these are large tables ... ), since it would lead to skew. even if you use both (year,period) as the PI , you would still have only about 36 distinct values and will still have skew. .

Re: Testing the PPI waters

Thanks Joe, accounting_year and accounting_period will not be utilized stand alone for UPI or NUPI due to the skew factor that you mention.
I like you range expression and will test it out.
For my clarification when the user specifies accounting_year and period in their constraints the optimzer will apply the range expression and therefore be able to utilize partition elimination, correct?


Re: Testing the PPI waters

Yes, (I did some basic checking before selling you that :-) )

That being said remember that you need to collect statistics on the special column PARTITION itself.