Skip to content
Advertisement

Oracle partitioning by range

I need to split the table into partitions, namely into three partitions according to the EVENT_TIME field, where the first partition is an interval of a week from today, the second last week and the third partition is history, where data that does not pass into the first two partitions are placed. In addition, I need to add a trigger that will clear the history every two weeks.

I know that this is not a valid request, so I am writing, please help

Advertisement

Answer

It sounds like you want to keep a rolling 2-3 weeks’ worth of data. In which case you can use interval partitioning, dropping the oldest partition each week.

Interval partitioning creates a new partition whenever you insert a row with a value greater than the current highest partition boundary.

All you need to define is an initial partition and the time interval. You can choose any value in the past as the boundary for the initial partition.

For example:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement