Follow Us on Twitter

Oracle 11g feature van de maand: partitioning

Januari 2008 - In deze editie van de Oracle 11g feature van de maand kijken we naar een aantal nieuwe partitioning-features. Partitioning werd reeds geïntroduceerd met de Oracle database versie 8 en is sindsdien veel gebruikt voor het vergemakkelijken van beheer en availability van de database, en het gebruik ervan kan ook nog eens een performance-winst opleveren.

Door gebruik te maken van de partitioning-optie van de database wordt het mogelijk om tabellen en indexen op te splitsen in kleinere, beheersbare delen. Elke partitie kan afzonderlijk benaderd worden voor bijvoorbeeld backup en recovery, waardoor onderhoud aan tabellen met veel data gemakkelijker wordt. Daarnaast mag een partitie andere storage-karakteristieken hebben: historische data kan opgeslagen worden op een minder kostbaar medium dan recente, actieve data.

Partitioning vormt dan ook de basis voor ILM, Information Lifecycle Management: een strategie voor waardebepaling, ouderdom en gebruiksfrequentie van data voor bijvoorbeeld het terugdringen van opslagkosten of compliance met wet- en regelgeving.

Naast het beheersaspect kan het partitioneren van tabellen ook een performancewinst opleveren. Door “partition pruning” wordt aan de hand van selectiecriteria bepaald in welke partitie de op te vragen data zich bevindt, waardoor niet de hele tabel doorlopen hoeft te worden. Dit kan uiteraard alleen wanneer de zogenaamde “partition key”, de definitie waar de partitie-indeling door wordt bepaald, gebruikt wordt als criterium.

Bij partitioning wordt in eerste instantie aan vaak DBA-gerelateerde werkzaamheden gedacht, maar als ontwikkelaar is het goed om de principes van partitioning te kennen en de nieuwe mogelijkheden te volgen. In dit Whitebook zullen we ingaan op de twee belangrijkste nieuwe partitioning-features van 11g : interval- en ref-partitioning.

Interval partitioning

Met de introductie van interval partitioning lost Oracle 11g een beheersprobleem op. Als een range-partitioned table op een datum of nummer werd gebruikt, dan moeten periodiek de nieuwe partities aangemaakt om de data voor bijvoorbeeld een nieuwe periode op de juiste plaats te zetten. Indien er een record toegevoegd wordt waar nog geen partitie voor bestaat dan volgt de bekende melding : “ORA-14400: Inserted partition key does not map to any partition”. Een klein voorbeeld :

Connected to: 
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 

SQL> CREATE TABLE TRANSACTIES ( 
  2     ID           NUMBER PRIMARY KEY NOT NULL, 
  3     trans_datum  DATE NOT NULL, 
  4     trans_bedrag NUMBER 
  5  ) 
  6  PARTITION BY RANGE (trans_datum) 
  7  ( 
  8     PARTITION trans_1 VALUES LESS THAN (TO_DATE('01-01-2008','DD-MM-YYYY')) 
  9   , PARTITION trans_2 VALUES LESS THAN (TO_DATE('01-02-2008','DD-MM-YYYY')) 
 10   , PARTITION trans_3 VALUES LESS THAN (TO_DATE('01-03-2008','DD-MM-YYYY')) 
 11  ); 

Table created. 

SQL> INSERT INTO transacties 
  2              (ID, trans_datum, trans_bedrag 
  3              ) 
  4       VALUES (1, TO_DATE ('31-12-2007', 'DD-MM-YYYY'), 10 
  5              ) 
  6  ; 

1 row created. 

SQL> INSERT INTO transacties 
  2              (ID, trans_datum, trans_bedrag 
  3              ) 
  4       VALUES (2, TO_DATE ('01-03-2008', 'DD-MM-YYYY'), 50 
  5              ) 
  6  ; 
INSERT INTO transacties 
            * 
ERROR at line 1: 
ORA-14400: inserted partition key does not map to any partition 

Om het voorgaande probleem op te lossen kan nu een interval opgegeven worden bij de partition by-clausule.

SQL> CREATE TABLE TRANSACTIES ( 
  2     ID           NUMBER PRIMARY KEY NOT NULL, 
  3     trans_datum  DATE NOT NULL, 
  4     trans_bedrag NUMBER 
  5  ) 
  6  PARTITION BY RANGE (trans_datum) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
  7  ( 
  8     PARTITION trans_1 VALUES LESS THAN (TO_DATE('01-01-2008','DD-MM-YYYY')) 
  9  ); 

Minstens één partitie (trans_1) moet opgegeven worden. Dit is het zogenaamde transition point. Op basis van deze partitie worden de anderen aangemaakt. Indien initieel meerdere partities worden gedefinieerd zal de laatste als transition point gelden. De functie NUMTOYMINTERVAL(n, interval) converteert nummer n naar het type INTERVAL YEAR TO MONTH, voor het opgegeven interval ‘YEAR’ of ‘MONTH’ . Een interval van een half jaar wordt dan NUMTOYMINTERVAL(6, ‘MONTH’) , een interval van 2 jaar: NUMTOYMINTERVAL(2, ‘YEAR’).

Als we nu dezelfde data opvoeren zullen we de ORA-14400 melding niet meer krijgen :

SQL> INSERT INTO transacties 
  2              (ID, trans_datum, trans_bedrag 
  3              ) 
  4       VALUES (1, TO_DATE ('31-12-2007', 'DD-MM-YYYY'), 10 
  5              ) 
  6  ; 

1 row created. 

SQL> INSERT INTO transacties 
  2              (ID, trans_datum, trans_bedrag 
  3              ) 
  4       VALUES (2, TO_DATE ('01-03-2008', 'DD-MM-YYYY'), 50 
  5              ) 

1 row created.

Nu willen we bekijken welke partities er bestaan voor de tabel “transacties” :

SQL> SELECT   partition_name 
  2         , high_value 
  3      FROM user_tab_partitions 
  4     WHERE table_name = 'TRANSACTIES' 
  5  ORDER BY partition_position; 

PARTITION_NAME  HIGH_VALUE 
--------------  --------------
TRANS_1         TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', ...
SYS_P41         TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', ...

De uitkomst van het SQL-statement laat zien dat er een nieuwe partitie is gecreëerd met een door het systeem toegewezen naam. Ook zien we dat enkel de partitie is aangemaakt die nodig is geweest voor de toegevoegde data: tussenliggende partities worden niet zomaar aangemaakt. Indien we nu nog een derde record voor januari 2008 toevoegen zal ook hiervoor een nieuwe partitie worden gemaakt. De reeds bestaande HIGH_VALUE 2008-01-01 moet ook als LESS THAN worden geïnterpreteerd:

INSERT INTO transacties 
            (ID, trans_datum, trans_bedrag 
            ) 
     VALUES (3, TO_DATE ('01-01-2008', 'DD-MM-YYYY'), 10 
            ) 
/

Als we de partities van de transactietabel opnieuw bekijken zien we dat partitie SYS_P42 op de juiste plaats is toegevoegd (partition_position):

PARTITION_NAME  HIGH_VALUE 
--------------  --------------------------------------------------------- 
TRANS_1         TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', ...
SYS_P42         TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', ...
SYS_P41         TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', ...

Lastig is wel dat de namen van de partitie door oracle worden toegewezen. Het gebruiken van de partitienaam bij opvragen van data wordt nu een riskante zaak. Ook hier is rekening mee gehouden in Oracle 11g : door gebruik van de partition for-clausule wordt op basis van de opgegeven waarde de juiste partitie gebruikt. In het volgende voorbeeld wordt de partitie gebruikt waar normaliter een record met datum 6 januari 2008 aan toegewezen zou worden :

SQL> SELECT * FROM transacties PARTITION FOR (TO_DATE('06-01-2008','DD-MM-YYYY')); 

ID  TRANS_DAT  TRANS_BEDRAG 
3   01-JAN-08            10

Noemenswaardig is dat eenmaal een partitie is aangemaakt door een insert, een rollback-statement de bijbehorende partitie niet verwijdert.

Ref partitioning

Ref partitioning lost een vraagstuk op dat vaak voorkomt met partitionering op parent-child tabellen. Een voorbeeld is een transactie, waarin in de transactiehoofding een transactiedatum is opgenomen waarop we willen partitioneren, maar in de transactieregels niet. Een oplossing is een redundante kolom bij de transactieregels toegevoegd worden om op dezelfde manier te kunnen partitioneren, maar 11g biedt ook hier een nette oplossing : ref-partitioning.

We gaan dit eens testen middels het volgende datamodel :

 

 

SQL> CREATE TABLE TRANSACTIES ( 
  2     ID            NUMBER PRIMARY KEY NOT NULL, 
  3     trans_datum   DATE NOT NULL, 
  4     creatie_datum DATE 
  5  ) 
  6  PARTITION BY RANGE (trans_datum) 
  7  ( 
  8     PARTITION trans_1 VALUES LESS THAN (TO_DATE('01-02-2008','DD-MM-YYYY')) 
  9   , PARTITION trans_2 VALUES LESS THAN (TO_DATE('01-03-2008','DD-MM-YYYY')) 
 10   , PARTITION trans_3 VALUES LESS THAN (TO_DATE('01-04-2008','DD-MM-YYYY')) 
 11  ); 

Table created. 

SQL> 
SQL> CREATE TABLE transactie_regels
  2  ( ID            NUMBER PRIMARY KEY NOT NULL, 
  3    trans_id      NUMBER NOT NULL, 
  4    creatie_datum DATE NOT NULL, 
  5    bedrag        NUMBER(7,2), 
  6    CONSTRAINT TRN_TRG_FK 
  7      FOREIGN KEY (trans_id) REFERENCES transacties(ID) 
  8   ) 
  9  PARTITION BY REFERENCE (TRN_TRG_FK); 

Table created.

Wat erg jammer is, is dat reference-partitioning niet mag in combinatie met interval-partitioning in de parent-tabel. De melding “ORA-14659: Partitioning method of the parent table is not supported “ zal in dit geval verschijnen.

SQL> INSERT INTO transacties 
  2              (ID, trans_datum, creatie_datum) 
  3       VALUES (1, TO_DATE ('01-01-2008', 'DD-MM-YYYY'), SYSDATE); 

1 row created. 

SQL> INSERT INTO transactie_regels 
  2              (ID, trans_id, creatie_datum, bedrag) 
  3       VALUES (1, 1, SYSDATE, 10.99); 

1 row created. 

SQL> INSERT INTO transactie_regels 
  2              (ID, trans_id, creatie_datum, bedrag) 
  3       VALUES (2, 1, SYSDATE, 4.95); 

1 row created. 

SQL> INSERT INTO transacties 
  2              (ID, trans_datum, creatie_datum) 
  3       VALUES (2, TO_DATE ('15-03-2008', 'DD-MM-YYYY'), SYSDATE); 

1 row created. 

SQL> INSERT INTO transactie_regels 
  2              (ID, trans_id, creatie_datum, bedrag) 
  3       VALUES (3, 2, SYSDATE, 8.25); 

1 row created. 

SQL> INSERT INTO transactie_regels 
  2              (ID, trans_id, creatie_datum, bedrag) 
  3       VALUES (4, 2, SYSDATE, 98.95); 

1 row created. 

SQL> SELECT   partition_name 
  2         , high_value 
  3      FROM user_tab_partitions 
  4     WHERE table_name = 'TRANSACTIE_REGELS' 
  5  ORDER BY partition_position; 

PARTITION_NAME                 HIGH_VALUE 
------------------------------ ----------------- 
TRANS_1 
TRANS_2 
TRANS_3

Het resultaat van het uitvragen van user_tab_partitions laat dus zien dat de tabel transactie_regels wel gepartitioneerd is, maar de high_value is leeg. Om terug te vinden hoe een tabel is opgedeeld kan user_part_tables worden geraadpleegd :

SQL> SELECT table_name, partitioning_type, ref_ptn_constraint_name 
  2    FROM user_part_tables 
  3   WHERE table_name IN ('TRANSACTIES','TRANSACTIE_REGELS') 
  4  / 

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME 
------------------------------ --------- -------------------------- 
TRANSACTIES                    RANGE 
TRANSACTIE_REGELS              REFERENCE TRN_TRG_FK

Nu kunnen dus de transactieregels die horen bij transacties van maand 3 opgevraagd worden middels :

SQL> SELECT * FROM transactie_regels PARTITION (trans_3)
  2  ; 

        ID   TRANS_ID CREATIE_D     BEDRAG 
---------- ---------- --------- ---------- 
         3          2 14-JAN-08       8.25 
         4          2 14-JAN-08      98.95

Zoals gezegd kunnen operaties plaatsvinden op partitieniveau van de tabel. Bijvoorbeeld het afvoeren van gegevens kan snel en zonder veel undo- en redo-generatie in tegenstelling tot een delete-operatie. Het volgende statement koppelt de partitie af van de tabel met minimale performance-impact, alleen de data-dictionary wordt bijgewerkt:

ALTER TABLE transacties DROP PARTITION trans_3

Hiermee verdwijnen ook alle bijbehorende transactieregels :

SQL> select * from transactie_regels; 

        ID   TRANS_ID CREATIE_D     BEDRAG 
---------- ---------- --------- ---------- 
         1          1 14-JAN-08      10.99 
         2          1 14-JAN-08       4.95

Conclusie

Deze twee nieuwe partitioning-features zorgen ervoor dat het beheer van gepartitioneerde tabellen vereenvoudigd wordt.
Een punt van aandacht is echter wel dat door het gebruik van interval-partitionering de business rules onder de loep genomen moeten worden: waar vroeger een foutmelding ontstond wanneer een record niet aan een bestaande partitie kon worden toegewezen, zal deze, misschien foutieve, data nu automatisch opgeslagen worden in een nieuwe partitie. Een klein minpunt is de onmogelijke combinatie van ref- en interval-partitionering.

Echter, met deze nieuwe features van de 11g database laat Oracle zien dat ze zich ook nu weer comitteert aan het ontwikkelen van nieuwe partitioneringsmethoden die beter aansluiten bij business-requirements en Information Lifecycle, zonder dat er veel geïnvesteerd hoeft te worden in beheer of wijzigingen in applicaties.

Relevante links

Over de auteur

Maarten van Luijtelaar is Oracle consultant bij Whitehorses en heeft ruim 8 jaar ervaring in de IT. Hij houdt zich voornamelijk bezig met applicatieontwikkeling met Oracle technologie en heeft veel ervaring met PL/SQL, Forms, Application Express en houdt zich daarnaast bezig met nieuwe ontwikkelingen in technologie.

Waardering:
 

Reacties

N.a.v dit interessante artikel heb ik een vraag: Ik heb een tabel van ongeveer 1tb groot met meer dan 1000 partities (1 voor elke dag) deze tabel moet dmv een functie met als returnwaarde een ref cursor benaderd worden. Omdat de te retourneren waarden her en der in verschillende partities kunnen zitten, bestaat er ook een tabel waarin een koppeling gemaakt wordt tussen bijv. klantnr, factuurnr en partitienamen. Kan ik in dit geval alleen de partitiena(a)m(en) dynamisch maken of moet ik een volledig dynamische cursor maken, die dmv een union queries op verschillende partities aan elkaar plakt. Probleem met de laatste mogelijkheid (die overigens wel erg goed en snel werkt), is een 32k beperking van de sql-string die wordt opgebouwd. kan ik de partities op een eenvoudiger manier dynamisch opgeven en/of benaderen. Dank

Nieuwe reactie inzenden

De inhoud van dit veld is privé en zal niet openbaar worden gemaakt.

Meer informatie over formaatmogelijkheden

CAPTCHA
Deze vraag is om te testen of u een persoon bent en om spam te voorkomen
Image CAPTCHA
Enter the characters shown in the image.