Partitioning on a flag (column)

[danimaltex]

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!

[dnoeth]

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.