Interval partitioning: zašto i kako?
Saturday, 27.10.2012 – DejanZašto?
Slagaću ako kažem, da nisam početkom skoro svake godine nailazio na grešku “ORA-14400: inserted partition key does not map to any partition“. Uvijek, ali uvijek! neko od programera zaboravi kreirati particiju za MAXVALUE ili particije i za narednu godinu, pa poslije Nove godine bude frka i panika, kad aplikacija ne radi, a telefon zvoni, dok preko E-Maila šalju screenshotove sa greškom… Na svu sreću, Oracle je tom problemu izašao u susret i omogućio opciju “Interval partitioning“, pomoću koje se nove particije automatski kreiraju.
Naravno kao i uvijek ću na praktičnom primjeru pokazati i objasniti, kako se interval partitioning podešava, kao i osnovne naredbe za rad sa particijama i subparticijama u tom slučaju.
Kako?
Kreirajmo fiktivnu tabelu HISTORY, sa 12 particija (po jednu za svaki mjesec u godini), u koju se podaci unose svakodnevno i nakon određenog vremena brišu ukoliko su označeni sa tipom “D” (“DROP”). Podaci, koji se moraju sačuvati, označeni su sa tipom “K” (“KEEP”).
CREATE TABLE history
(
hist_date DATE DEFAULT SYSDATE NOT NULL,
hist_type varchar2(1) default 'K'
CONSTRAINT ck_hist_type CHECK (hist_type in('K', 'D')) not null,
hist_message varchar2(128)
)
PARTITION BY RANGE (hist_date)
SUBPARTITION BY LIST (hist_type)
(
PARTITION P201201 VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201201_K VALUES ('K')
, SUBPARTITION SP201201_D VALUES ('D')
),
PARTITION P201202 VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201202_K VALUES ('K')
, SUBPARTITION SP201202_D VALUES ('D')
),
PARTITION P201203 VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201203_K VALUES ('K')
, SUBPARTITION SP201203_D VALUES ('D')
),
PARTITION P201204 VALUES LESS THAN (TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201204_K VALUES ('K')
, SUBPARTITION SP201204_D VALUES ('D')
),
PARTITION P201205 VALUES LESS THAN (TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201205_K VALUES ('K')
, SUBPARTITION SP201205_D VALUES ('D')
),
PARTITION P201206 VALUES LESS THAN (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201206_K VALUES ('K')
, SUBPARTITION SP201206_D VALUES ('D')
),
PARTITION P201207 VALUES LESS THAN (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201207_K VALUES ('K')
, SUBPARTITION SP201207_D VALUES ('D')
),
PARTITION P201208 VALUES LESS THAN (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201208_K VALUES ('K')
, SUBPARTITION SP201208_D VALUES ('D')
),
PARTITION P201209 VALUES LESS THAN (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201209_K VALUES ('K')
, SUBPARTITION SP201209_D VALUES ('D')
),
PARTITION P201210 VALUES LESS THAN (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201210_K VALUES ('K')
, SUBPARTITION SP201210_D VALUES ('D')
),
PARTITION P201211 VALUES LESS THAN (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201211_K VALUES ('K')
, SUBPARTITION SP201211_D VALUES ('D')
),
PARTITION P201212 VALUES LESS THAN (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
( SUBPARTITION SP201212_K VALUES ('K')
, SUBPARTITION SP201212_D VALUES ('D')
),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
( SUBPARTITION SPMAX_K VALUES ('K')
, SUBPARTITION SPMAX_D VALUES ('D')
)
)
ENABLE ROW MOVEMENT
COMPRESS FOR ALL OPERATIONS;
insert /*+ APPEND */ into HISTORY
select TO_DATE('01.01.2012', 'DD.MM.RRRR') + dbms_random.value(1,365) as hist_date,
case when mod(rownum, 5) = 0 then 'D'
else 'K'
end as hist_type,
dbms_random.string('A', 50) as hist_message
from dual connect by level <=500;
commit;
insert into HISTORY
values (to_date('01.01.2013', 'DD.MM.YYYY'), 'K', dbms_random.string('A', 50));
commit;
Pogledajmo, koje particije i subparticije su kreirane:
col partition_name for a10
col tblspc for a10
select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc
from user_tab_partitions
where table_name = 'HISTORY'
order by partition_position;
P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC
---------- ---------- ---------- -------------------------------------------------------------------------------- ----------
1 P201201 2 TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
2 P201202 2 TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
3 P201203 2 TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
4 P201204 2 TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
5 P201205 2 TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
6 P201206 2 TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
7 P201207 2 TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
8 P201208 2 TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
9 P201209 2 TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
10 P201210 2 TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
11 P201211 2 TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
12 P201212 2 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
13 P_MAX 2 MAXVALUE USERS
13 rows selected.
col partition_name for a10
col hv for a5
col tblspc for a10
select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc
from user_tab_subpartitions
where table_name = 'HISTORY'
order by partition_name, subpartition_position;
SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC
---------- ---------- ------------------------------ ----- ----------
1 P201201 SP201201_K 'K' USERS
2 P201201 SP201201_D 'D' USERS
1 P201202 SP201202_K 'K' USERS
2 P201202 SP201202_D 'D' USERS
1 P201203 SP201203_K 'K' USERS
2 P201203 SP201203_D 'D' USERS
1 P201204 SP201204_K 'K' USERS
2 P201204 SP201204_D 'D' USERS
1 P201205 SP201205_K 'K' USERS
2 P201205 SP201205_D 'D' USERS
1 P201206 SP201206_K 'K' USERS
2 P201206 SP201206_D 'D' USERS
1 P201207 SP201207_K 'K' USERS
2 P201207 SP201207_D 'D' USERS
1 P201208 SP201208_K 'K' USERS
2 P201208 SP201208_D 'D' USERS
1 P201209 SP201209_K 'K' USERS
2 P201209 SP201209_D 'D' USERS
1 P201210 SP201210_K 'K' USERS
2 P201210 SP201210_D 'D' USERS
1 P201211 SP201211_K 'K' USERS
2 P201211 SP201211_D 'D' USERS
1 P201212 SP201212_K 'K' USERS
2 P201212 SP201212_D 'D' USERS
1 P_MAX SPMAX_K 'K' USERS
2 P_MAX SPMAX_D 'D' USERS
26 rows selected.
Dakle, da se ne bismo brinuli, hoće li u budućnosti odgovarajuća particija biti kreirana ili ne, prebacimo particije na automatsko kreiranje sa intervalom od jednog mjeseca.
SQL> alter table HISTORY 2 SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH')); alter table HISTORY * ERROR at line 1: ORA-14759: SET INTERVAL is not legal on this table.
E, ne može tek tako lako 😉 Naime, ukoliko imate neku tabelu sa MAXVALUE particijom, onda za tu tabelu ne možete podesiti da koristi interval partitioning. Morate podijeliti tu particiju na dva dijela i onda obrisati MAXVALUE particiju.
select TO_CHAR(TRUNC(add_months(max(hist_date), +1), 'MONTH'), 'RRRR-MM-DD HH24:MI:SS')
as split_at
from HISTORY partition (p_max);
ALTER TABLE HISTORY SPLIT PARTITION P_MAX
AT (TIMESTAMP'2013-02-01 00:00:00') INTO (PARTITION P201301, PARTITION P_MAX);
ALTER TABLE HISTORY drop PARTITION p_max;
Pored toga postoji par ograničenja zbog kojih se ne može koristiti interval partitioning, a to su: – interval partitioning se ne može koristiti za IOT (Index Organized Tables) – partitioning key mora biti tipa DATE ili NUMBER – ne može se kreirati “domain index” nad tabelama sa interval partitioningom – interval partitioning ne može koristiti na nivou subparticija Nakon što je MAXVALUE particija obrisana, možemo aktivirati interval partitioning:
alter table HISTORY
SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));
alter table HISTORY
set SUBPARTITION TEMPLATE
(SUBPARTITION SP_K VALUES ('K'),
SUBPARTITION SP_D VALUES ('D')
);
Radi povećanja I/O efikasnosti, kreirajmo tablespace za svaku particiju pojedinačno. Svaka particija će prema round-robin sistemu koristiti odgovarajući tablespace.
-- as SYS create tablespace TBLSPC_01 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_01.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_02 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_02.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_03 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_03.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_04 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_04.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_05 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_05.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_06 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_06.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_07 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_07.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_08 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_08.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_09 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_09.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_10 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_10.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_11 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_11.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; create tablespace TBLSPC_12 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_12.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto; alter user dejan quota 1500M on TBLSPC_01 quota 1500M on TBLSPC_02 quota 1500M on TBLSPC_03 quota 1500M on TBLSPC_04 quota 1500M on TBLSPC_05 quota 1500M on TBLSPC_06 quota 1500M on TBLSPC_07 quota 1500M on TBLSPC_08 quota 1500M on TBLSPC_09 quota 1500M on TBLSPC_10 quota 1500M on TBLSPC_11 quota 1500M on TBLSPC_12;
pa onda izmijenimo “storage” postavke u tabeli:
alter table HISTORY set STORE IN (TBLSPC_01, TBLSPC_02, TBLSPC_03, TBLSPC_04,
TBLSPC_05, TBLSPC_06, TBLSPC_07, TBLSPC_08,
TBLSPC_09, TBLSPC_10, TBLSPC_11, TBLSPC_12);
E, sad unesimo neke podatke za 2013. godinu i provjerimo, da li je nova particija automatski kreirana:
insert into HISTORY
values(TO_DATE('15.01.2013', 'DD.MM.RRRR'), 'K', dbms_random.string('A', 50));
insert into HISTORY
values(TO_DATE('15.02.2013', 'DD.MM.RRRR'), 'D', dbms_random.string('A', 50));
commit;
col partition_name for a10
col tblspc for a10
select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc
from user_tab_partitions
where table_name = 'HISTORY'
order by partition_position;
P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC
---------- ---------- ---------- -------------------------------------------------------------------------------- ----------
1 P201201 2 TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
2 P201202 2 TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
3 P201203 2 TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
4 P201204 2 TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
5 P201205 2 TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
6 P201206 2 TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
7 P201207 2 TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
8 P201208 2 TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
9 P201209 2 TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
10 P201210 2 TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
11 P201211 2 TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
12 P201212 2 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
13 P201301 2 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
14 SYS_P855 2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_02
14 rows selected.
col partition_name for a10
col hv for a5
col tblspc for a10
select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc
from user_tab_subpartitions
where table_name = 'HISTORY'
order by partition_name, subpartition_position;
SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC
---------- ---------- ------------------------------ ----- ----------
1 P201201 SP201201_K 'K' USERS
2 P201201 SP201201_D 'D' USERS
1 P201202 SP201202_K 'K' USERS
2 P201202 SP201202_D 'D' USERS
1 P201203 SP201203_K 'K' USERS
2 P201203 SP201203_D 'D' USERS
1 P201204 SP201204_K 'K' USERS
2 P201204 SP201204_D 'D' USERS
1 P201205 SP201205_K 'K' USERS
2 P201205 SP201205_D 'D' USERS
1 P201206 SP201206_K 'K' USERS
2 P201206 SP201206_D 'D' USERS
1 P201207 SP201207_K 'K' USERS
2 P201207 SP201207_D 'D' USERS
1 P201208 SP201208_K 'K' USERS
2 P201208 SP201208_D 'D' USERS
1 P201209 SP201209_K 'K' USERS
2 P201209 SP201209_D 'D' USERS
1 P201210 SP201210_K 'K' USERS
2 P201210 SP201210_D 'D' USERS
1 P201211 SP201211_K 'K' USERS
2 P201211 SP201211_D 'D' USERS
1 P201212 SP201212_K 'K' USERS
2 P201212 SP201212_D 'D' USERS
1 P201301 SYS_SUBP851 'K' USERS
2 P201301 SYS_SUBP852 'D' USERS
1 SYS_P855 SYS_SUBP853 'K' TBLSPC_02
2 SYS_P855 SYS_SUBP854 'D' TBLSPC_02
insert into HISTORY
select ADD_MONTHS(TO_DATE('01.11.2012', 'DD.MM.RRRR'), LEVEL), 'K', dbms_random.string('A', 50)
from dual
connect by LEVEL <= 24;
commit;
col partition_name for a10
col tblspc for a10
select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc
from user_tab_partitions
where table_name = 'HISTORY'
order by partition_position;
P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC
---------- ---------- ---------- -------------------------------------------------------------------------------- ----------
1 P201201 2 TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
2 P201202 2 TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
3 P201203 2 TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
4 P201204 2 TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
5 P201205 2 TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
6 P201206 2 TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
7 P201207 2 TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
8 P201208 2 TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
9 P201209 2 TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
10 P201210 2 TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
11 P201211 2 TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
12 P201212 2 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
13 P201301 2 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
14 SYS_P855 2 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_02
15 SYS_P858 2 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_03
16 SYS_P861 2 TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_04
17 SYS_P864 2 TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_05
18 SYS_P867 2 TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_06
19 SYS_P870 2 TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_07
20 SYS_P873 2 TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_08
21 SYS_P876 2 TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_09
22 SYS_P879 2 TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_10
23 SYS_P882 2 TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_11
24 SYS_P885 2 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_12
25 SYS_P888 2 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_01
26 SYS_P891 2 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_02
27 SYS_P894 2 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_03
P# PARTITION_ SUBCNT HIGH_VALUE TBLSPC
---------- ---------- ---------- -------------------------------------------------------------------------------- ----------
28 SYS_P897 2 TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_04
29 SYS_P900 2 TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_05
30 SYS_P903 2 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_06
31 SYS_P906 2 TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_07
32 SYS_P909 2 TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_08
33 SYS_P912 2 TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_09
34 SYS_P915 2 TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_10
35 SYS_P918 2 TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TBLSPC_11
35 rows selected.
col partition_name for a10
col hv for a5
col tblspc for a10
select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc
from user_tab_subpartitions
where table_name = 'HISTORY'
order by partition_name, subpartition_position;
SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC
---------- ---------- ------------------------------ ----- ----------
1 P201201 SP201201_K 'K' USERS
2 P201201 SP201201_D 'D' USERS
1 P201202 SP201202_K 'K' USERS
2 P201202 SP201202_D 'D' USERS
1 P201203 SP201203_K 'K' USERS
2 P201203 SP201203_D 'D' USERS
1 P201204 SP201204_K 'K' USERS
2 P201204 SP201204_D 'D' USERS
1 P201205 SP201205_K 'K' USERS
2 P201205 SP201205_D 'D' USERS
1 P201206 SP201206_K 'K' USERS
2 P201206 SP201206_D 'D' USERS
1 P201207 SP201207_K 'K' USERS
2 P201207 SP201207_D 'D' USERS
1 P201208 SP201208_K 'K' USERS
2 P201208 SP201208_D 'D' USERS
1 P201209 SP201209_K 'K' USERS
2 P201209 SP201209_D 'D' USERS
1 P201210 SP201210_K 'K' USERS
2 P201210 SP201210_D 'D' USERS
1 P201211 SP201211_K 'K' USERS
2 P201211 SP201211_D 'D' USERS
1 P201212 SP201212_K 'K' USERS
2 P201212 SP201212_D 'D' USERS
1 P201301 SYS_SUBP851 'K' USERS
2 P201301 SYS_SUBP852 'D' USERS
1 SYS_P855 SYS_SUBP853 'K' TBLSPC_02
SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC
---------- ---------- ------------------------------ ----- ----------
2 SYS_P855 SYS_SUBP854 'D' TBLSPC_02
1 SYS_P858 SYS_SUBP856 'K' TBLSPC_03
2 SYS_P858 SYS_SUBP857 'D' TBLSPC_03
1 SYS_P861 SYS_SUBP859 'K' TBLSPC_04
2 SYS_P861 SYS_SUBP860 'D' TBLSPC_04
1 SYS_P864 SYS_SUBP862 'K' TBLSPC_05
2 SYS_P864 SYS_SUBP863 'D' TBLSPC_05
1 SYS_P867 SYS_SUBP865 'K' TBLSPC_06
2 SYS_P867 SYS_SUBP866 'D' TBLSPC_06
1 SYS_P870 SYS_SUBP868 'K' TBLSPC_07
2 SYS_P870 SYS_SUBP869 'D' TBLSPC_07
1 SYS_P873 SYS_SUBP871 'K' TBLSPC_08
2 SYS_P873 SYS_SUBP872 'D' TBLSPC_08
1 SYS_P876 SYS_SUBP874 'K' TBLSPC_09
2 SYS_P876 SYS_SUBP875 'D' TBLSPC_09
1 SYS_P879 SYS_SUBP877 'K' TBLSPC_10
2 SYS_P879 SYS_SUBP878 'D' TBLSPC_10
1 SYS_P882 SYS_SUBP880 'K' TBLSPC_11
2 SYS_P882 SYS_SUBP881 'D' TBLSPC_11
1 SYS_P885 SYS_SUBP883 'K' TBLSPC_12
2 SYS_P885 SYS_SUBP884 'D' TBLSPC_12
1 SYS_P888 SYS_SUBP886 'K' TBLSPC_01
2 SYS_P888 SYS_SUBP887 'D' TBLSPC_01
1 SYS_P891 SYS_SUBP889 'K' TBLSPC_02
2 SYS_P891 SYS_SUBP890 'D' TBLSPC_02
1 SYS_P894 SYS_SUBP892 'K' TBLSPC_03
2 SYS_P894 SYS_SUBP893 'D' TBLSPC_03
SUB# PARTITION_ SUBPARTITION_NAME HV TBLSPC
---------- ---------- ------------------------------ ----- ----------
1 SYS_P897 SYS_SUBP895 'K' TBLSPC_04
2 SYS_P897 SYS_SUBP896 'D' TBLSPC_04
1 SYS_P900 SYS_SUBP898 'K' TBLSPC_05
2 SYS_P900 SYS_SUBP899 'D' TBLSPC_05
1 SYS_P903 SYS_SUBP901 'K' TBLSPC_06
2 SYS_P903 SYS_SUBP902 'D' TBLSPC_06
1 SYS_P906 SYS_SUBP904 'K' TBLSPC_07
2 SYS_P906 SYS_SUBP905 'D' TBLSPC_07
1 SYS_P909 SYS_SUBP907 'K' TBLSPC_08
2 SYS_P909 SYS_SUBP908 'D' TBLSPC_08
1 SYS_P912 SYS_SUBP910 'K' TBLSPC_09
2 SYS_P912 SYS_SUBP911 'D' TBLSPC_09
1 SYS_P915 SYS_SUBP913 'K' TBLSPC_10
2 SYS_P915 SYS_SUBP914 'D' TBLSPC_10
1 SYS_P918 SYS_SUBP916 'K' TBLSPC_11
2 SYS_P918 SYS_SUBP917 'D' TBLSPC_11
70 rows selected.
Sad dolaze na red osnovne operacije nad particijama i subparticijama. Pošto sam na početku primjera naveo da se podaci sa tipom “D” mogu nakon određenog vremena obrisati, pogledajmo kako se dinamički mogu obrisati željeni podaci, odnosno particije ili subparticije. Ne moramo znati ime željene particije ili subparticije – samo datum!
alter table HISTORY drop partition for (to_date('01.01.2012', 'DD.MM.YYYY'));
alter table HISTORY drop subpartition for (to_date('01.02.2012', 'DD.MM.YYYY'), 'D');
A šta sa globalnim indeksima? Ništa. Tu je rješenje i za njih:
create index idx_hist_type on HISTORY(hist_type);
SELECT index_name, status
from user_indexes
where index_name='IDX_HIST_TYPE';
INDEX_NAME STATUS
------------------------------ --------
IDX_HIST_TYPE VALID
alter table HISTORY
drop subpartition for (to_date('01.03.2012', 'DD.MM.YYYY'), 'D') ;
SELECT index_name, status
from user_indexes
where index_name='IDX_HIST_TYPE';
INDEX_NAME STATUS
------------------------------ --------
IDX_HIST_TYPE UNUSABLE
alter index idx_hist_type rebuild online;
SELECT index_name, status
from user_indexes
where index_name='IDX_HIST_TYPE';
INDEX_NAME STATUS
------------------------------ --------
IDX_HIST_TYPE VALID
alter table HISTORY
drop subpartition for (to_date('01.04.2012', 'DD.MM.YYYY'), 'D')
UPDATE GLOBAL INDEXES;
SELECT index_name, status
from user_indexes
where index_name='IDX_HIST_TYPE';
INDEX_NAME STATUS
------------------------------ --------
IDX_HIST_TYPE VALID
Zaključak – interval partitioning uveliko olakšava administracijski dio oko održavanja particija i subparticija! Zamislite samo koliko bi vremena morali potrošiti da koristite interval NUMTODSINTERVAL(7,’DAY’) – koliko biste onda morali kreirati particija … I za kraj jedno upozorenje – koliko god ovo zvučalo korisno i obećavajuće, postoji jedna začkoljica prilikom brisanja particija i podataka koristeći opciju “for (TO_DATE(…))”. Izvršite slijedeće naredbe nekoliko puta zaredom i provjerite, koje su particije obrisane. 😉
alter table HISTORY drop partition for (to_date('01.01.2012', 'DD.MM.YYYY'));
alter table HISTORY drop subpartition for (to_date('01.02.2012', 'DD.MM.YYYY'), 'D');
col partition_name for a10
col tblspc for a10
select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc
from user_tab_partitions
where table_name = 'HISTORY'
order by partition_position;
col partition_name for a10
col hv for a5
col tblspc for a10
select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc
from user_tab_subpartitions
where table_name = 'HISTORY'
order by partition_name, subpartition_position;
Kompletan source code možete skinuti ovdje, a rezultat jednog izvršenja ovdje.
3 Responses to “Interval partitioning: zašto i kako?”
Blago Vama koji imate love za partitionig.
Mi imamo klijente koji nemaju niti za place …
🙂
By Damir Vadas on Oct 28, 2012
@damir
ne možete od klijenta očekivati da kupi oracle, jer nemaju svi para.
Znam neke firme koriste oracle forms,ali weblogic je preskup
za bilo koju privatnu firmu u hr, koja nije banka ili telekom, a oracle forms je alat koji
nije ni dokoljena visual studio-u.
Apex je malo bolji, ali nista specijalno.
Znam sada oracle nudi integraciju oracle forms + Java,
ali onda bi radije uzeo čistu javu, ili pogotovo asp.net.
By Patuljak on Oct 31, 2012
@Damir: znas onu staru kambodzansku (da ne kazem od Druga Tita 😉 ): “Zivi u miru, a spremaj se kao da ce sutra rat!” ? 😉 Dakle, vjezbaj u development bazi, pa ukoliko budes imao klijente, koji koriste partitioning, bices spreman! 🙂
@Patuljak: komentar ti je potpuno promasio poentu i temu teksta o partitioningu…
By Dejan on Nov 1, 2012