127 lines
3.9 KiB
Plaintext
127 lines
3.9 KiB
Plaintext
-- 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;
|
|
|
|
|