All Forums Database
HelmutR 6 posts Joined 01/13
16 Jan 2013
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

dnoeth 4628 posts Joined 11/04
20 Jan 2013

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
 

Dieter

HelmutR 6 posts Joined 01/13
21 Jan 2013

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

You must sign in to leave a comment.