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