Partition by Range_N

Database
N/A

Partition by Range_N

Hi all,

I have a question about partition of a table.

I have a table with a unique primary index column1 of datatype char(2).

I want to define a RANGE_N - partition on column1 using the hash-function HASHROW as follow:

partition by ( RANGE_N((HASHBUCKET(HASHROW(column1 ))) MOD  x  BETWEEN 0  AND (x-1)  EACH 1  ) 

My issue is that I need to find an appropriate integer x for which there are not overlappings of the values HASHBUCKET(HASHROW(column1)).

That is, for every two values of the field column1, I need to ensure that these values do not belong to the same partition. 

Is there a method to find an integer x with this property?

Thanks

2 REPLIES
Senior Apprentice

Re: Partition by Range_N

You can't be sure that no hashes overlap but a high number of x (e.g. 65535) will reduce the risk.

If it's Latin only you could also do two-levels using character partitioning for each character:

RANGE_N(SUBSTRING(column1 FROM 1 FOR 1 BETWEEN
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',
'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
AND 'Z',
NO RANGE)

Btw, if you got a UPI on a CHAR(2) the possible number of rows is probably way to small for partitioning.

Teradata Employee

Re: Partition by Range_N

What is the goal for partitioning this column? With the UPI, any query WHERE column1='xx' will already be a single AMP, single row query.