Onderhoud van grote tabellen en indexen kan op den duur erg veel tijd en resource gaan kosten. Ook kan de data toegang performance drastisch omlaag gaan voor deze objecten. Het partitioneren van de tabellen en indexen kan de performance en het onderhoud op verschillende manieren ten gunste beïnvloeden:

· Partitie onafhankelijkheid betekend dat backup en recovery operaties op individuele partities kunnen worden uitgevoerd en de andere partities beschikbaar blijven.

· Query performance kan verbeterd worden omdat toegang tot relevante partities kan worden gelimiteerd.

· Er is een grotere mogelijkheid voor parallellisme met meerdere partities..

Al de voorbeelden boven gebruiken de users tablespace voor alle partities. In een echte situatie zullen de partities over verschillende tablespaces verspreid kunnen worden om i/o contentie te voorkomen.

· Range Partitioning Tables
· Hash Partitioning Tables
· Composite Partitioning Tables
· Partitioning Indexes
· Local Prefixed Indexes
· Local Non-Prefixed Indexes
· Global Prefixed Indexes
· Global Non-Prefixed Indexes
· Partitioning Existing Tables

Range Partitioning Tables

Range partitioning is handig als je duidelijke ranges van data hebt die je bij elkaar wilt plaatsen. Een klassiek voorbeeld hiervan is het gebruik van datum. Het partitioneren van een table met gebruik van datum ranges maakt het mogelijk om data van een bepaalde leeftijd in een zelfde partitie op te. Als historische data niet meer nodig is, kan de hele partitie verwijderd worden. Als de tabel op de juiste manier is geïndexeerd dan kunnen zoek criteria hun zoektocht limitteren tot de partitie die de data van de juiste leeftijd bevat:

SQL>CREATE TABLE rekeningen 
(rekening_no NUMBER NOT NULL, 
rekening_date DATE NOT NULL, 
comments VARCHAR2(500)) 
PARTITION BY RANGE (rekening_date) 
(PARTITION rekeningen_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users, 
PARTITION rekeningen_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users, 
PARTITION rekeningen_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users, 
PARTITION rekeningen_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users); 

Hash Partitioning Tables

SQL>CREATE TABLE rekeningen 
(rekening_no NUMBER NOT NULL, 
rekening_date DATE NOT NULL, 
comments VARCHAR2(500)) 
PARTITION BY HASH (rekening_no) 
PARTITIONS 4 
STORE IN (users, users, users, users); 

Of apart gedefinieerd:

SQL>CREATE TABLE rekeningen 
(rekening_no NUMBER NOT NULL, 
rekening_date DATE NOT NULL, 
comments VARCHAR2(500)) 
PARTITION BY HASH (rekening_no) 
(PARTITION rekeningen_q1 TABLESPACE users, 
PARTITION rekeningen_q2 TABLESPACE users, 
PARTITION rekeningen_q3 TABLESPACE users, 
PARTITION rekeningen_q4 TABLESPACE users); 

Samengestelde(Composite) Partitioning Tables

Composite partitionering maakt het mogelijk om subpartitionering op een verschillende keys toe te passen op range partities. Hoe groter het aantal partities, hoe groter de mogelijkheid voor parallelisme en daarmee een vermindering van de kans op contentie. Het volgende voorbeeld zal de tabel rekeningen op rekening_date partitioneren en de tabel subpartioneren op rekening_no, zodat er 32 subpartities onstaan:

SQL>CREATE TABLE rekeningen 
(rekening_no NUMBER NOT NULL, 
rekening_date DATE NOT NULL, 
comments VARCHAR2(500)) 
PARTITION BY RANGE (rekening_date) 
SUBPARTITION BY HASH (rekening_no) 
SUBPARTITIONS 8 
(PARTITION rekeningen_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')), 
PARTITION rekeningen_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')), 
PARTITION rekeningen_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')), 
PARTITION rekeningen_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')); 

Gepartitioneerde Indexen

Local - Alle index entries in een enkele partitie zullen overeenkomen met een enkele tabel partitie (equipartitioned). Ze worden gemaakt met het the LOCAL keyword en ondersteunen partitie onafhankelijkheid. Equipartioning stelt Oracle in staat om efficienter om te gaan met het bedenken van query plans.

Global - Index in een enkele partitie kan overeenkomen met meerdere tabel partities. Ze worden gemaakt met het GLOBAL keyword en ondersteunen de partitie onafhankelijkheid niet. Global indexen kunnen alleen range gapartitioneerd zijn en mogen zo geaprtitioneerd dat het lijkt of ze equipartitioned zijn, maar Oracle zal niet profiteren van deze structuur.

Beide types kunnen nog verder onderverdeeld worden:

Prefixed - De partition key is de meest linker kolom van de index. Onderzeok van dit soort index is goedkoper.Als een query de partition key in de where clause specificeerd, is partitie vermindering (pruning) mogelijk met andere woorden niet alle partities zullen bezocht worden.

Non-Prefixed - Ondersteund niet partition pruning, maar is effictief in het benaderen van data dat meerdere partities overspant. Vaak gebruikt voor het indexeren van een kolom die niet de partition key van de tabel is, als je de index op dezelfde key wil partitioneren als op de key van de onderliggende tabel.

Local Prefixed Indexes

Aangenomen dat de REKENINGEN tabel range gepartitioneerd is op REKENING_DATE, dan volgt hier voorbeelden van local prefixed indexes:

SQL>CREATE INDEX rekeningen_idx ON rekeningen (rekening_date) LOCAL; 
SQL>CREATE INDEX rekeningen_idx ON rekeningen (rekening_date) LOCAL 
(PARTITION rekeningen_q1 TABLESPACE users, 
PARTITION rekeningen_q2 TABLESPACE users, 
PARTITION rekeningen_q3 TABLESPACE users, 
PARTITION rekeningen_q4 TABLESPACE users);

Oracle zal de partitienamen genereren en de partities bouwen in de default tablespace met de default size tenzij anders aangegeven.

Local Non-Prefixed Indexes

Aangenomen dat de REKENINGEN tabel range gepartitioneerd is op REKENING_DATE dan volgt hier een voorbeeld van een local non-prefixed index. De indexed kolom komt niet overeen met de partition key:

SQL>CREATE INDEX rekeningen_idx ON rekeningen (rekening_no) LOCAL 
(PARTITION rekeningen_q1 TABLESPACE users, 
PARTITION rekeningen_q2 TABLESPACE users, 
PARTITION rekeningen_q3 TABLESPACE users, 
PARTITION rekeningen_q4 TABLESPACE users); 

Global Prefixed Indexes

Aangenomen dat de REKENINGEN tabel range gepartitioneerd is op REKENING_DATE dan volgt hier een voorbeeld van een global prefixed index:

SQL>CREATE INDEX rekeningen_idx ON rekeningen (rekening_date) 
GLOBAL PARTITION BY RANGE (rekening_date) 
(PARTITION rekeningen_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users, 
PARTITION rekeningen_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users, 
PARTITION rekeningen_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users, 
PARTITION rekeningen_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users); 

De partition range waarden moeten gespecificeerd worden. Het GLOBAL keyword betekent dat Oracle niet kan aannemen dat de partition key idezelfde is als die van de onderliggende tabel.

Global Non-Prefixed Indexes

Oracle support geen Global Non Prefixed indexes.

Partitioning Existing Tables

De ALTER TABLE ... EXCHANGE PARTITION ... syntax kan gebruikt worden om een bestaande tabel te partitioneren als volgt:

Eerst een non-partitioned tabel maken:.

SQL>CREATE TABLE my_table ( 
id NUMBER, 
description VARCHAR2(50) 
); SQL>INSERT INTO my_table (id, description) VALUES (1, 'One'); 
SQL>INSERT INTO my_table (id, description) VALUES (2, 'Two'); 
SQL>INSERT INTO my_table (id, description) VALUES (3, 'Three'); 
SQ>INSERT INTO my_table (id, description) VALUES (4, 'Four'); 
SQL>COMMIT; 

Vervolgens maken we een nieuw gepartitioneerde tabel met een enkele partitie die al onze bestemmings tabel zal fungeren.

SQL>CREATE TABLE my_table_2 ( 
id NUMBER, 
description VARCHAR2(50) 
) 
PARTITION BY RANGE (id) 
(PARTITION my_table_part VALUES LESS THAN (MAXVALUE)); 

Vervolgens verruilen we het originele tabel segment met het partitie segment

SQL>ALTER TABLE my_table_2 
EXCHANGE PARTITION my_table_part 
WITH TABLE my_table 
WITHOUT VALIDATION; 

Nu kunnen we de originele tabel droppen en de gepartitioneerde tabel hernoemen

SQL>DROP TABLE my_table; 
SQL>RENAME my_table_2 TO my_table; 

Tenslotte kunnen we de gepartitioneerde tabel opdelen in het aantal partities dat nodig is en statistiekenverzamelen:

SQL>ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3) 
INTO (PARTITION my_table_part_1, 
PARTITION my_table_part_2); 

SQL>EXEC DBMS_STATS.gather_table_stats(USER, 'MY_TABLE', cascade => TRUE); 

De volgende query laat zien dat het partitionering proces klaar is:

SQL>COLUMN high_value FORMAT A20 
SQL>SELECT table_name, 
partition_name, 
high_value, 
num_rows 
FROM user_tab_partitions 
ORDER BY table_name, partition_name; 

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS 
------------------------------ ------------------------------ -------------------- ---------- 
MY_TABLE MY_TABLE_PART_1 3 2 
MY_TABLE MY_TABLE_PART_2 MAXVALUE 2 

2 rows selected. 

ZIE OOK

PARTITIONEREN MET EXCHANGE PARTITION
PARTITIONEREN MET DBMS_REDEFINITION