Character PPI question

Database
Enthusiast

Character PPI question

Hi,

I'm new to Teradata and have a question on creating a table with a CPPI.

I have a table with a few hundered million rows. I want to create partitioning on a column called SAP_CODE whose structure is always four digits. The first digit is alphanumeric the last 3 digits are numeric, e.g. S001, M123, Z012.

At the moment there are about 1000 - 2000 different SAP_CODEs, per year about 50 - 100 are added.

How could I tell Teradata to get one partition for each SAP_CODE? (it's no problem if all 26,000 possible partitions are created ...)

At the moment I'm using the construct

PARTITION BY RANGE_N (HASHBUCKET(HASHROW(SAP_CODE)) MOD 10000 BETWEEN 0 AND 9999 EACH 1)

taken from

http://forums.teradata.com/forum/database/partitioning-by-a-character-column

(thanks to Dieter!)

This is kind of a workaround for the time before Character PPI...

Is there a better solution, maybe using regular expression or stuff like that?

Any suggestions or advices are appreciated!

Thanks in advance

Helmut

2 REPLIES
Senior Apprentice

Re: Character PPI question

Hi Helmut,

of course there are ways to do character partitioning, but it will be based on how the table is accessed, e.g.

SAP_CODE = 'xxxx', SAP_CODE BETWEEN 'xxxx' and 'yyyy', SAP_CODE LIKE 'X%', etc.

One possible way:

SAP_CODE CHAR(4) UPPERCASE NOT CASESPECIFIC NOT NULL
...
PARTITION BY (RANGE_N(SAP_CODE BETWEEN
'A000' AND 'A999'
,'B000' AND 'B999'
,'C000' AND 'C999'
...
,'Z000' AND 'Z999'),
RANGE_N(SUBSTRING(SAP_CODE FROM 2) (INT) BETWEEN 0 AND 999 EACH 1));

This should work for all of them, but when it's not equality it will access all 1000 subpartitions of a character.

You might get the best partition elimination when the column is split into two, SAP_CODE1 CHAR(1) and SAP_CODE2 SMALLINT, but this would probably require some major rework.

When it's mainly SAP_CODE = 'xxxx' then the existiing partitioning might be ok, too.

Dieter

Enthusiast

Re: Character PPI question

Hello Dieter,

thanks for your answer!

The split into two columns is out of scope ... but the access is always SAP_CODE = 'xxxx'.

Your example with the multi-level partitioning is good, didn't think of it ...

Best regards

Helmut