0

I need to create a range-partitioned table:

i.e.

    create table table1(item_id number(22), sys_entry_date timestamp default sysdate)
     partition by range(sys_entry_date) interval(NUMTOYMINTERVAL(1,'YEAR'))
   (partition p01 values less than (to_date('31-DEC-2016','DD-MON-YYYY')));

A few insert for demo purpose:

---Should lie in the main partition's main subpartition (crnt_part) since the it's part of the latest records received.

insert into table1 values(1, sysdatetime);

---Should lie in the main partition's subpartition of default section (prev_part) since the it's 2 days older
insert into table1 values(1, sysdatetime-3);
insert into table1 values(1, sysdatetime-4);

---Would help us identify the yearly partitions (suggestive)
insert into table1 values(2, sysdatetime-1500);
insert into table1 values(3, sysdatetime-1200);
insert into table1 values(4, sysdatetime-800);
insert into table1 values(1, sysdatetime-400);

I want to achieve following through it:

  1. An Yearly partition;
  2. Within the yearly, a sub-partition based on sys_entry_date which 2.a. latest 2 days held in crnt_part 2.b. remaining held in default partition, maybe in prev_part

Appreciate if someone could help in this particular context.

Praveen Mishra
  • 101
  • 6
  • if you can provide the number of rows; grouped by year and month i'll be able to provide a better suggestion on partitioning – Nizam - Madurai Nov 17 '20 at 09:46
  • Not sure exactly of the details you needed, but have added certain insert statements in order to make scenario clearer. – Praveen Mishra Nov 17 '20 at 09:58
  • Where does subpartitioning come into this? Or do you want every month partition to be split into two subpartitions? That doesn’t seem like you’d have any processes that would benefit from that scheme. – Andrew Sayer Nov 17 '20 at 10:47
  • 1
    When your column data type is `sys_entry_date` then you should also use `SYSTIMESTAMP` rather than `SYSDATE`, and also for the partition interval a `TIMESTAMP` would be better. – Wernfried Domscheit Nov 17 '20 at 12:51
  • 1
    You could use an interval of `INTERVAL '2' DAY`. Then with a 2-daily job merge older daily partitions into a yearly partition. – Wernfried Domscheit Nov 17 '20 at 12:55
  • @WernfriedDomscheit I have tried doing following: – Praveen Mishra Nov 17 '20 at 18:07
  • @WernfriedDomscheit I have tried below but don't think it will achieve what I mentioned in my question: partition by range(sysdate) interval(NUMTOYMINTERVAL(1,'YEAR')) subpartition by range(sysdate) (partition p01 values less than (to_date('31-DEC-2016','DD-MON-YYYY')) (subpartition sp01 values less than (to_date('15-NOV-2020','DD-MON-YYYY')))) – Praveen Mishra Nov 17 '20 at 18:09

0 Answers0