How to create a dynamic partition (Oracle) by range which will be update every day?

Question

I am using Oracle. I have a table which has column "updated_date": timestamp. This table has millions new records inserted everyday. Our job is only focus on populating (update, select, ...) the new records which are within 90 days from current date (updated_date > current_date - 90 days). I am thinking about using table "Partition by range" to improve performance. But the problem is the partition is not dynamic. I want to have a partition only contains new records (within 90 days) against the current datetime and this partition is auto-updated everyday to eliminated the unused records (that records maybe new yesterday but they are old today). Do you have any solution for that?

Answer 1

Oracle provides INTERVAL partition for this purpose. Would be like this:

CREATE TABLE your_table
(
  updated_date  TIMESTAMP(3) DEFAULT LOCALTIMESTAMP NOT NULL,
  ... other columns
)
PARTITION BY RANGE (updated_date) INTERVAL (INTERVAL '1' DAY)
(  
   (PARTITION P_INITIAL VALUES LESS THAN (TIMESTAMP '2023-04-01 00:00:00') 
);

To drop outdated partitions, have a look at How to drop multiple interval partitions based on date?