Interval partitioning: zašto i kako?

Saturday, 27.10.2012 – Dejan

Zaš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.

  1. 3 Responses to “Interval partitioning: zašto i kako?”

  2. Blago Vama koji imate love za partitionig.
    Mi imamo klijente koji nemaju niti za place …
    🙂

    By Damir Vadas on Oct 28, 2012

  3. @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

  4. @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

Post a Comment