-- http://www.oraclefindings.com/2017/07/23/switching-range-interval-partitioning/

drop table DEMO purge;

create table DEMO(
	 id INTEGER generated always as identity
	,day DATE not null
	,code VARCHAR2(2) not null
	,val NUMBER not null
	,PRIMARY KEY(id)
) 
partition by range(day)(
      partition P_2024_01 values less than (date'2024-02-01')
     ,partition P_2024_02 values less than (date'2024-03-01')
     ,partition INFINITY  values less than (MAXVALUE)
    )
;

create index IDX_VAL on DEMO(val) local;


insert into DEMO (day,code,val) values (date'2024-01-09','UK',1005);
insert into DEMO (day,code,val) values (date'2024-01-10','IT',900);
insert into DEMO (day,code,val) values (date'2024-01-11','IT',400);
insert into DEMO (day,code,val) values (date'2024-01-11','FR',400);
insert into DEMO (day,code,val) values (date'2024-01-12','UK',400);
insert into DEMO (day,code,val) values (date'2024-01-12','IT',500);

insert into DEMO (day,code,val) values (date'2024-02-07','UK',765);
insert into DEMO (day,code,val) values (date'2024-02-09','IT',551);
insert into DEMO (day,code,val) values (date'2024-02-09','IT',90);
insert into DEMO (day,code,val) values (date'2024-02-09','FR',407);
insert into DEMO (day,code,val) values (date'2024-02-09','UK',101);
insert into DEMO (day,code,val) values (date'2024-02-10','IT',505);
insert into DEMO (day,code,val) values (date'2024-02-10','FR',2000);

commit;


exec dbms_stats.gather_table_stats(user,'DEMO');
exec dbms_stats.delete_table_stats(user,'DEMO');

-- IMPORTANT: the table should NOT have a MAXVALUE partition
-- ALTER TABLE… SET INTERVAL fails with: ORA-14759: SET INTERVAL is not legal on this table. (Doc ID 2926948.1)

select count(*) from DEMO partition (INFINITY);
-- Drop the MAXVALUE partition.
alter table POC.DEMO drop partition INFINITY;

alter table DEMO set interval(NUMTOYMINTERVAL(1, 'MONTH'));

insert into DEMO (day,code,val) values (date'2024-04-01','IT',50);
insert into DEMO (day,code,val) values (date'2024-05-12','FR',60);
insert into DEMO (day,code,val) values (date'2024-05-14','UK',70);
commit;


-------------------------------------------------------------

drop table DEMO purge;

create table DEMO(
	 id INTEGER generated always as identity
	,day DATE not null
	,code VARCHAR2(2) not null
	,val NUMBER not null
	,PRIMARY KEY(id)
) 
partition by range(day) subpartition by list (code)(
      partition P_2024_01 values less than (date'2024-02-01')
      	(
      		 subpartition P_2024_01_UK values ('UK')    
  			,subpartition P_2024_01_IT values ('IT')    
  			,subpartition P_2024_01_FR values ('FR')    
  		)    
     ,partition P_2024_02 values less than (date'2024-03-01')
      	(
      		 subpartition P_2024_02_UK values ('UK')    
  			,subpartition P_2024_02_IT values ('IT')    
  			,subpartition P_2024_02_FR values ('FR')    
  		)    
     ,partition INFINITY  values less than (MAXVALUE)
      	(
      		 subpartition INFINITY_UK values ('UK')    
  			,subpartition INFINITY_IT values ('IT')    
  			,subpartition INFINITY_FR values ('FR')    
  		)    
    )
;

create index IDX_VAL on DEMO(val) local;

alter table POC.DEMO drop partition INFINITY;
alter table DEMO set interval(NUMTOYMINTERVAL(1, 'MONTH'));

alter index POC.SYS_C007367 rebuild;


ALTER TABLE DEMO SPLIT SUBPARTITION SYS_SUBP3241
  VALUES ('UK') INTO (
	SUBPARTITION SYS_SUBP3241_UK,
    SUBPARTITION SYS_SUBP3241_DIFF
  ) 
  ONLINE;

ALTER TABLE DEMO SPLIT SUBPARTITION SYS_SUBP3241_DIFF
  VALUES ('IT') INTO (
	SUBPARTITION SYS_SUBP3241_IT,
    SUBPARTITION SYS_SUBP3241_FR
  ) 
  ONLINE;

-- because wrong previous subpart name
alter table POC.DEMO rename subpartition SYS_SUBP3241_FR to SYS_SUBP3241_DIFF; 

ALTER TABLE DEMO SPLIT SUBPARTITION SYS_SUBP3241_DIFF
  VALUES ('FR') INTO (
	SUBPARTITION SYS_SUBP3241_FR,
    SUBPARTITION SYS_SUBP3241_OTHER
  ) 
  ONLINE;

select count(*) from DEMO subpartition(SYS_SUBP3241_OTHER);
alter table DEMO drop subpartition SYS_SUBP3241_OTHER;