132 lines
3.5 KiB
Plaintext
132 lines
3.5 KiB
Plaintext
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;
|
|
|
|
|
|
|
|
|