I am new to Teradata 14.0 so would like to get expert opinion on a scenario i have.
I have a data set which contains few dimensions and metrics corresponding to certain CODES.
data structure would look like this :
columns : CODE, ColA, ColB, MetricA, MetricB
data : A111,ABC, ZZZ,10,50.00
This data set is used for end user reporting and has more than billion rows. One of the key access pattern for end user reporting is to look at data for just a specific CODE.
As the volume of the data is very large i was planning to range partition the table but the challenge is the CODE is not a date value, it is a CODE which is not pre-defined or does not have a specific pattern but comes from source and could be any string value.
a) Can we add partition dynamically based on value of a column coming in the data set before the table is loaded (only new ones, so if the partition already exist it should not add it again)
b) If we were to drop any range partition, does it require any extra cleanup work or it happens as soon as command is executed ?
c) Is there an alternate solution that range partitioning to support this reporting requirement ?
Your inputs and suggestions will be greatly helpful