Partitioning in V12, utilising MOD

Database

Partitioning in V12, utilising MOD

Sorry for the ancient version, everybody who's moved on!

I am trying to check my maths skills and coming up empty.

Basically, I have read that there is a way to effectively implement v13 multilevel partitioning in v12, via the mod statement.

I'm concerned however, that I may be causing clashes to occur in the Partition assignment as a result of the below.  

How can I test for it?  And (other than upgrade), how do I work around it?

PARTITION BY ( RANGE_N(Prd_Dt  BETWEEN DATE '2010-03-01' AND DATE '2015-03-31' EACH INTERVAL '1' DAY ,

NO RANGE OR UNKNOWN),RANGE_N((HASHBUCKET(HASHROW(Prd_Type_Cd))) MOD  10  BETWEEN 0  AND 9  EACH 1 ,

NO RANGE OR UNKNOWN) )  ;

select  (DATE '2015-03-31' -  DATE '2010-03-01') mod 100;

select (HASHBUCKET(HASHROW('DAY'))) MOD  10 ;

select (HASHBUCKET(HASHROW('WEEK'))) MOD  10;

select (HASHBUCKET(HASHROW('MONTH'))) MOD  10;

3 REPLIES
Senior Apprentice

Re: Partitioning in V12, utilising MOD

MLPPI is already available in TD12, you probably mean CHAR-PPI, which was introduced in 13.10.

The HASHBUCKET/MOD approach only works for equality-constraints and you have to watch out for different values mapping to the same bucket.

Dieter

Re: Partitioning in V12, utilising MOD

Hi Dieter,

Yes, clarifying, I'd like to use the Char-PPI, but I'm stuck with Hashbucket and Mod for now.

How are the buckets calculated?  For the example I'm working to, I can see the range of days is 1856, and the 3 values for the character column resolve to 3 values using MOD.

But, I'm not understanding their combined effect on the RANGE assignment.  Eg. First date = 1, and 'Day'=2, so is the partition = 00002?

Or, can I force the date range to an offset (by multiplication ), and keep the 'Day', 'Week', 'Month' values at the rightmost digit?  Eg.  00012

Regards,

Colin

Senior Apprentice

Re: Partitioning in V12, utilising MOD

HashRow is Terdata's hashing alhorithm and hashbucket extracts the first 20 bits of this 32 bit value as an integer.

The MOD 10 creates 10 possible values which are mapped to 10 partitions using EACH 1 (plus the NO RANGE). As a result each of the 1856 daily partitions is sub-partitioned by 11 Prd_Type_Cd partitions resulting in 1856*111=20416 partitions.

Try to keep the partitioning as simple as possible, i.e. avoid additional calculations. The optimizer must still be able to do partition elimination based on the WHERE-condition.

Dieter