All Forums Data Modeling
danimaltex 8 posts Joined 10/14
17 Oct 2014
Partitioning on a flag (column)

Hi all!
I m new to TD and have a question on a given scenerio. I am building a fact table and need to retain 2 days of rolling data. The architect built in a flag (Is_current), and this will be used for the current days load. I have never used partitioning befaore and was told that it may provide a good solution. So, my questions is: what would the partition syntax look like for that given (column) scenerio. This is what i had in mind:
--set column flag from 2 to 3
--set column flag from 1 to 2
--add new partition
--populate data with flag = 1
--drop partition where flag = 3
Does this sound like i'm on the right track? If so, what is the general partition syntax look like in this scenerio?
Thanks all!
 

Thanks, Dan
dnoeth 4628 posts Joined 11/04
18 Oct 2014

I don't think this will be efficient, when you update the flag all rows will be moved (=deleted and re-inserted) to the new partition.
Instead of a current flag you might simply define daily partitions for the next 10+years, e.g.
PARTITION BY RANGE_N(datecol BETWEEN DATE '2014-10-16' AND DATE '2030-12-31' EACH INTERVAL '1' DAY
and use SQL to DELETE the old rows WHERE datecol < CURRENT_DATE - 1, the current data would be accessed with a WHERE datecol = CURRENT_DATE.
You don't have to drop old partitions, just delete the rows, an unused partition doesn't have any overhead in Teradata.

Dieter

You must sign in to leave a comment.