Apr 1, 2020

Interval partitioning

I received a request to add a partition automatically on every data based on date. Assume up to 2000 one partition but after for each date partition to be created based on a date column.

Here is the example:

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , time_id       DATE
  , quantity_sold NUMBER(3)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 (PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
  );

-- Your test data like some thing like this
insert into sales values(9,'9-Jan-2005',100);
commit;

-- To see all partition tables

select * from USER_TAB_PARTITIONS where table_name='SALES';

8 comments:

Translate >>