How to add Range-interval partition to existing table in Oracle?

32 Views Asked by At

I am currently struggling with performance issues in an old system with badly defined partitions. The offending table is defined like this:

create table Ver
(
   Ver_id             number (15) not null,
   Vmo_enddate        date null,
   Vmo_til_version    number (6) not null
)
partition by range (Vmo_til_version)
   interval ( 1 )
   subpartition by range
   (Vmo_enddate)
   subpartition template (
      subpartition
         Y2024
         values less than
            (to_date (' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
      subpartition Future values less than (Maxvalue))
(
   partition
      Ver1
      values less than (2)
      (
         subpartition
            Ver1_y2024
            values less than
               (to_date (' 2024-01-01 00:00:00',
                         'SYYYY-MM-DD HH24:MI:SS',
                         'NLS_CALENDAR=GREGORIAN')),
         subpartition Ver1_future values less than (Maxvalue)),
   partition
      values less than (100000)
      (
         subpartition
            values less than
               (to_date (' 2024-01-01 00:00:00',
                         'SYYYY-MM-DD HH24:MI:SS',
                         'NLS_CALENDAR=GREGORIAN')),
         subpartition values less than (Maxvalue)));

There are many more subpartitions and hundreds of main partitions, but neither have been expanded since the person responsible for the system retired a few years ago. Therefore, all data since 2020 lands in the maxvalue partition, which now contains billions of rows instead of millions per month and it's killing performance.

Oracle is not exactly my strongest skill. I have figured out how to expand the subpartition template, but i cant for the life of me figure out how to add new main partitions. My thought is that "simply" adding partitions for the next several years is the cheapest solution for now, until resources are freed up to solve it for real. Is it possible to do to an existing table or do i need to remake it as a new table?

0

There are 0 best solutions below