3. LIST Particionisanje (9i +)
4. COMPOSITE
RANGE-HASH Particionisanje
5. COMPOSITE RANGE-LIST Particionisanje
(9i+)
CONVERT A PARTITION
INTO A STAND-ALONE TABLE
REBUILD LOCAL ALL
LOCAL INDEXES ON A TABLE
Particionisane tabele se prave deljenjem veoma velikih tabela u manje celine- particije, jer je jednostavnije raditi sa pojedinčnim particijama nego velikim tabelama.
Svaka particija mora imati iste kolone, tipove, constraint-e, ali svaka particija može imati različite atribute kao što su pctfree, pctused, tablespace...Particija može imati svoje particije (subparticije). Nad particionisam tabelama mogu postojati lokalni indeksi i globalni indeksi.
Svaka particija je
limitirana opsegom particije. Najčešće se za opseg uzima polje datum.
CREATE TABLE SIGNALING_CDRS
( AMG VARCHAR2(5 BYTE) NOT NULL,
ATF
NUMBER(14),
VREME_POCETKA DATE,
VREME_KRAJA DATE,
TRAJANJE NUMBER(8),
B_BROJ VARCHAR2(50 BYTE) NOT NULL,
FAJL_ID NUMBER(7),
CENTRALA_ID VARCHAR2(8 BYTE),
A_DODATAK VARCHAR2(11 BYTE),
B_PREFIKS VARCHAR2(10 BYTE),
REDIRECTINGNUM VARCHAR2(26 BYTE)
)TABLESPACE
CDR_SPC_01
PCTUSED 90
PCTFREE 10
INITRANS 12
MAXTRANS 255
STORAGE (INITIAL 500M
NEXT 200M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 50
FREELISTS 8
)
PARTITION BY RANGE (VREME_KRAJA)
(
PARTITION P23 VALUES LESS THAN (TO_DATE('
2006-04-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE CDR_SPC_21
PCTUSED
90
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 350M
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P24 VALUES LESS THAN (TO_DATE('
2006-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE CDR_SPC_27
PCTUSED
90
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 350M
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
Po datumu:
CREATE TABLE table1 (
col1 NUMBER(10),
col2 NUMBER(10) NOT
NULL,
col3 NUMBER(10) NOT NULL,
col4 DATE NOT NULL,
col5 VARCHAR2(2000))
PARTITION BY RANGE (col4) (
PARTITION p1
VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
TABLESPACE <tablespace_name>,
PARTITION p2
VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
TABLESPACE <tablespace_name>,
PARTITION p3
VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
TABLESPACE <tablespace_name>,
PARTITION p4
VALUES LESS THAN (MAXVALUE)
TABLESPACE <tablespace_name>);
Po abecedi:
CREATE TABLE table2 (
col1 NUMBER(6),
col2 VARCHAR2(25),
col3 VARCHAR2(25),
primary key (col1))
PARTITION BY RANGE (col3)
(PARTITION p1 VALUES LESS THAN ('F%')
TABLESPACE <tablespace_name>,
PARTITION p2 VALUES LESS THAN ('M%')
TABLESPACE <tablespace_name>,
PARTITION p3 VALUES LESS THAN ('S%')
TABLESPACE <tablespace_name>,
PARTITION p3 VALUES LESS THAN (MAXVALUE)
TABLESPACE <tablespace_name>);
Baziran na hash vrednostima kolone koja se koristi za particionisanje.
Hash particionisanje nema logički značaj kao range particionisanje.
Za pregled brojeva koji se koristi startovati upit:
select sql_hash_value, prev_hash_value from g v$session;
CREATE TABLE table1 (
col1 NUMBER(10),
col2 NUMBER(10) NOT NULL,
col3 NUMBER(10) NOT NULL,
col4 DATE NOT NULL,
col5 VARCHAR2(2000))
PARTITION BY HASH (col1)
PARTITIONS 3
STORE IN (part1, part2, part3);
Particionisanje se zasniva na listi diskretnih vrednosti ključa za
particionisanje za svaku particiju.
CREATE TABLE table1 (
col1 NUMBER(10),
col2 VARCHAR2(20),
col3 NUMBER(10,2),
col4 VARCHAR2(2))
PARTITION BY LIST (col4) (
PARTITION p1 VALUES (<value1>, <value2>)
TABLESPACE <tablespace_name1>,
PARTITION p2 VALUES (<value3>, <value4>, <value5>)
TABLESPACE <tablespace_name2>,
PARTITION p3 VALUES (<value6>, <value7>,<value8>,<value9>)
TABLESPACE <tablespace_name3>);
Kombinacija range i hash particionisanja- u
prvom koraku se podaci podele po range particionisanju a onda se dalje svaka
range particija opet particioniše po principu hash particionisanja.
CREATE TABLE table1 (
col1 NUMBER(5),
col2 VARCHAR2(30),
col3 NUMBER(10),
col4 DATE)
PARTITION BY RANGE(col4)
SUBPARTITION BY HASH(col1)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE <tablespace_name1>,
SUBPARTITION sp2 TABLESPACE <tablespace_name2>,
SUBPARTITION sp3 TABLESPACE <tablespace_name3>,
SUBPARTITION sp4 TABLESPACE <tablespace_name4>)
(PARTITION p1
VALUES LESS
THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION p2
VALUES LESS
THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION p3
VALUES LESS
THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION p4
VALUES LESS
THAN(TO_DATE('05/01/2000','DD/MM/YYYY')),
PARTITION p5
VALUES LESS
THAN(TO_DATE('06/01/2000','DD/MM/YYYY')),
PARTITION p6
VALUES LESS THAN(MAXVALUE));
Kombinacija Range i List particionisanja- u
prvom koraku se podaci podele po range particionisanju a onda se dalje svaka
range particija opet particioniše po principu list particionisanja.
ALTER TABLE <table_name>
MOVE PARTITION <partition_name>
TABLESPACE <tablespace_name>;
ALTER TABLE <table_name>
EXCHANGE PARTITION <partition_name>
WITH TABLE <new_table_name>
<including | excluding> INDEXES
<with | without> VALIDATION
EXCEPTIONS INTO <schema.table_name>;
ALTER TABLE <table_name>
RENAME PARTITION <existing_partition_name>
TO <new_partition_name>;
ALTER TABLE <table_name>
SPLIT PARTITION <partition_name>
AT <range_definition>
INTO (PARTITION <first_partition>, PARTITION <second_partition>)
UPDATE GLOBAL INDEXES;
ALTER
TABLE TABLE1
SPLIT
PARTITION P1 AT
(TO_DATE(' 2006-05-03 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
INTO (PARTITION P2,
PARTITION
P1);
ALTER
TABLE TRP.DNEVNA_RAZLIKA_SANDRA
MERGE PARTITIONS P26, P27
INTO
PARTITION P28
NOLOGGING
NOCOMPRESS
TABLESPACE CDR_SPC_18
PCTUSED
95
PCTFREE
0
INITRANS
12
MAXTRANS
255
STORAGE
(
INITIAL 350M
MINEXTENTS 1
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
);
ALTER TABLE table1
TRUNCATE PARTITION <p1>
DROP STORAGE;
ALTER TABLE <table_name>
MODIFY PARTITION <partition_name>
REBUILD UNUSABLE LOCAL INDEXES;
ALTER TABLE DROP PARTITION <partition_name>
UPDATE GLOBAL INDEXES;
Sandra Tomić & Darko Jelisavčić