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
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
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
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