How to create sub partition in already interval partitioned table in oracle-CodePudding

I have a table which is interval partitioned on date column. I want to sub partition on these already created partition into list partitioning.

How can I do this in Oracle?

Suppose there is a table in which there exists a date column and a region column along with the other columns.

Table is already interval partitioned with this date column.

Now I want these interval partition to sub partition on the basis on the region column.

How will it be possible?

CodePudding user response：

As you're on 19c, you can use alter table to change the partitioning strategy (this was added in 18c). This allows you to define subpartitions:

create table t (
  c1 date, c2 int
) partition by range ( c1 )
  interval ( interval '1' month ) (
    partition p0 values less than ( date'2022-03-01' )
  );
  
insert into t 
with rws as (
  select level x from dual
  connect by level <= 60
)
  select date'2022-03-01'   x, mod ( x, 3 ) from rws;
commit;

select partition_name, subpartition_name, high_value
from   user_tab_subpartitions
where  table_name = 'T';

no rows selected
  
alter table t
  modify partition by range ( c1 )
  interval ( interval '1' month )
  subpartition by list ( c2 )
  subpartition template (
    subpartition p1 values ( 1 ),
    subpartition p2 values ( 2 ),
    subpartition pdef values ( default )
  ) (
    partition p0 values less than ( date'2022-03-01' )
  )
  online ;
    
select partition_name, subpartition_name, high_value
from   user_tab_subpartitions
where  table_name = 'T';

PARTITION_NAME  SUBPARTITION_NA HIGH_VALUE    
--------------- --------------- -------------
P0              P0_P1           1            
P0              P0_P2           2            
P0              P0_PDEF         default      
SYS_P42745      SYS_SUBP42742   1            
SYS_P42745      SYS_SUBP42743   2            
SYS_P42745      SYS_SUBP42744   default      
SYS_P42749      SYS_SUBP42746   1            
SYS_P42749      SYS_SUBP42747   2            
SYS_P42749      SYS_SUBP42748   default

Note that interval or auto-list subpartitioning is not supported. You'll need to update the subpartition template to add new values as needed.