94 lines
2.5 KiB
Plaintext
94 lines
2.5 KiB
Plaintext
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 P_2024_03 values less than (date'2024-04-01')
|
|
,partition P_2024_04 values less than (date'2024-05-01')
|
|
,partition P_2024_05 values less than (date'2024-06-01')
|
|
,partition P_2024_06 values less than (date'2024-07-01')
|
|
,partition INFINITY values less than (MAXVALUE)
|
|
)
|
|
;
|
|
|
|
create index IDX_VAL on DEMO(val) local;
|
|
|
|
insert /*+ APPEND */ into DEMO (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;
|
|
|
|
|
|
exec dbms_stats.gather_table_stats(user,'DEMO');
|
|
exec dbms_stats.delete_table_stats(user,'DEMO');
|
|
|
|
exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO', pname=>'INCREMENTAL', pvalue=>'TRUE');
|
|
exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO', pname=>'GRANULARITY', pvalue=>'PARTITION');
|
|
|
|
exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO', pname=>'INCREMENTAL', pvalue=>'FALSE');
|
|
exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO', pname=>'GRANULARITY', pvalue=>'AUTO');
|
|
|
|
|
|
execute dbms_stats.gather_table_stats(ownname=>'POC',tabname=>'DEMO', partname=>'P_2024_01');
|
|
|
|
execute dbms_stats.delete_table_stats(ownname=>'POC',tabname=>'DEMO', partname=>'P_2024_06');
|
|
execute dbms_stats.gather_table_stats(ownname=>'POC',tabname=>'DEMO', partname=>'P_2024_06');
|
|
execute dbms_stats.gather_table_stats(ownname=>'POC',tabname=>'DEMO', partname=>'P_2024_06',granularity=>'PARTITION');
|
|
|
|
|
|
alter table POC.DEMO drop partition P_2024_05 update global indexes;
|
|
alter table POC.DEMO drop partition P_2024_06 update global indexes;
|
|
alter table POC.DEMO drop partition INFINITY update global indexes;
|
|
|
|
alter table DEMO set interval(NUMTOYMINTERVAL(1, 'MONTH'));
|
|
|
|
insert /*+ APPEND */ into DEMO (day,code,val)
|
|
select
|
|
DATE'2024-07-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;
|
|
|
|
|
|
|
|
|