drop table DEMO1 purge;

create table DEMO1(
	 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 P_2024_03 values less than (date'2024-04-01')
     ,partition P_2024_04 values less than (date'2024-05-01')
    )
;

create index IDX_VAL on DEMO1(val) local;

insert /*+ APPEND */ into DEMO1 (day,code,val)
select
       DATE'2024-01-01' + trunc(DBMS_RANDOM.VALUE(1,30*4))
      ,DECODE(trunc(DBMS_RANDOM.VALUE(1,10)),
             1,  'UK'
            ,2,  'UK'
            ,3,  'UK'
            ,4,  'UK'
            ,5,  'UK'
            ,6,  'IT'
            ,7,  'IT'
            ,8,  'FR'
            ,9,  'FR'
            ,10, 'FR'
      )
      ,trunc(DBMS_RANDOM.VALUE(1,10000))
from 
      xmltable('1 to 4000000')
;
commit;

-- create a copy of the table

drop table DEMO2 purge;

create table DEMO2(
       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 P_2024_03 values less than (date'2024-04-01')
     ,partition P_2024_04 values less than (date'2024-05-01')
    )
;

create index IDX_VAL2 on DEMO2(val) local;

insert /*+ APPEND */ into DEMO2 (day,code,val) select day,code,val from DEMO1;
commit;


exec dbms_stats.gather_table_stats(user,'DEMO1');

exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO2', pname=>'INCREMENTAL', pvalue=>'TRUE');
exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO2', pname=>'GRANULARITY', pvalue=>'PARTITION');
exec dbms_stats.gather_table_stats(user,'DEMO2');

-- initial stats on DEM02 are faster because of incremental aggregation of GLOBAL stats instead basic calculation
-- repeating gather table stats is much faster on DEMO2 (last_analyzed is nut increased)
            (test if inserting a couple of lines in every partition change someting)


-- convert tables to INTERVAL
alter table DEMO1 set interval(NUMTOYMINTERVAL(1, 'MONTH'));
alter table DEMO2 set interval(NUMTOYMINTERVAL(1, 'MONTH'));

-- insert a lot of lines in 1-st partition
drop table DEMOAUX purge;
create table DEMOAUX as select * from DEMO1 where 1=0;
alter table DEMOAUX drop column ID;

insert /*+ APPEND */ into DEMOAUX (day,code,val)
select
       DATE'2024-01-01' + trunc(DBMS_RANDOM.VALUE(1,30*1))
      ,DECODE(trunc(DBMS_RANDOM.VALUE(1,10)),
             1,  'UK'
            ,2,  'UK'
            ,3,  'UK'
            ,4,  'UK'
            ,5,  'UK'
            ,6,  'IT'
            ,7,  'IT'
            ,8,  'FR'
            ,9,  'FR'
            ,10, 'FR'
      )
      ,trunc(DBMS_RANDOM.VALUE(1,10000))
from 
      xmltable('1 to 1000000')
;
commit;


insert /*+ APPEND */ into DEMO1 (day,code,val) select day,code,val from DEMOAUX;
commit;

insert /*+ APPEND */ into DEMO2 (day,code,val) select day,code,val from DEMOAUX;
commit;


-- stats on DEMO2 will be faster
exec dbms_stats.gather_table_stats(user,'DEMO1');
exec dbms_stats.gather_table_stats(user,'DEMO2');


=> check why GLOBAL stats on DEMO2 are staled



-- insert 1 line in a new partition
insert into DEMO1 (day,code,val) values (date'2024-05-09','UK',1005);
insert into DEMO2 (day,code,val) values (date'2024-05-09','UK',1005);
commit;