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