Le partitionnement par référence (Reference Partitioning) est une nouveauté 11g, venue avec la version 11. Il permet un équi-partitionnement (même schéma de partitionnement entre une table parent et une table enfant). La table enfant est partitionnée selon le schéma de partitionnent de la table parent sans avoir à dupliquer la colonne clé de partitionnement. En revanche, les opérations de maintenance des partitions sont seulement permises sur la table parent, aucune sur la table enfant.
Un exemple :
SQL> CREATE TABLE parent_tab ( 2 id NUMBER NOT NULL, 3 code VARCHAR2(10) NOT NULL, 4 created_date DATE, 5 CONSTRAINT parent_tab_pk PRIMARY KEY (id) 6 ) 7 PARTITION BY RANGE (created_date) 8 ( 9 PARTITION part_2012 VALUES LESS THAN (TO_DATE(’01-01-2013′,’DD-MM-YYYY’)), 10 PARTITION part_2013 VALUES LESS THAN (TO_DATE(’01-01-2014′,’DD-MM-YYYY’)), 11 PARTITION part_2014 VALUES LESS THAN (TO_DATE(’01-01-2015′,’DD-MM-YYYY’)), 12 PARTITION part_2015 VALUES LESS THAN (TO_DATE(’01-01-2016′,’DD-MM-YYYY’)) 13 );
Table créée.
SQL> CREATE TABLE child_tab ( 2 id NUMBER NOT NULL, 3 parent_tab_id NUMBER NOT NULL, 4 code VARCHAR2(10), 5 –created_date DATE, 6 CONSTRAINT child_tab_pk PRIMARY KEY (id), 7 CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id) REFERENCES parent_tab (id) on delete cascade 8 ) 9 PARTITION BY REFERENCE (child_parent_tab_fk);
Table créée.
On retrouve les quatre partitions dans la table enfant sans ajouter la colonne created_date :
SQL> select table_name, partition_name from user_tab_partitions where table_name =’PARENT_TAB'; TABLE_NAME PARTITION_NAME —————————— —————————— PARENT_TAB PART_2012 PARENT_TAB PART_2013 PARENT_TAB PART_2014 PARENT_TAB PART_2015
SQL> select table_name, partition_name from user_tab_partitions where table_name=’CHILD_TAB'; TABLE_NAME PARTITION_NAME —————————— —————————— CHILD_TAB PART_2012 CHILD_TAB PART_2013 CHILD_TAB PART_2014 CHILD_TAB PART_2015
En insérant des données dans les deux tables, on constate que celles-ci sont bien réparties selon de schéma de partitionnement de la table parent.
On va insérer 4 lignes dans la table PARENT_TAB avec une ligne par partition et 4 lignes dans la table enfant dont 1 enregistrement qui référence l’id 1 de la table parent, 3 enregistrements qui référencent l’id 3 de la table parent.
I
NSERT INTO parent_tab VALUES (1, ‘ONE’, to_date(’20-03-2012′,’DD-MM-YYYY’)); INSERT INTO parent_tab VALUES (2, ‘TWO’, to_date(’31-12-2013′,’DD-MM-YYYY’)); INSERT INTO parent_tab VALUES (3, ‘THREE’, to_date(’25-08-2014′,’DD-MM-YYYY’)); INSERT INTO parent_tab VALUES (4, ‘FOUR’, SYSDATE); INSERT INTO child_tab VALUES (1, 1, ‘ONE’); INSERT INTO child_tab VALUES (2, 3, ‘TWO’); INSERT INTO child_tab VALUES (3, 3, ‘THREE’); INSERT INTO child_tab VALUES (4, 3, ‘FOUR’); COMMIT; SQL> exec dbms_stats.gather_table_stats(ownname =>user,tabname=>’PARENT_TAB’); Procédure PL/SQL terminée avec succès. SQL> exec dbms_stats.gather_table_stats(ownname =>user,tabname=>’CHILD_TAB’); Procédure PL/SQL terminée avec succès.
Le résultat :
SQL> select table_name, partition_name, num_rows from user_tab_partitions where table_name in (‘PARENT_TAB’,’CHILD_TAB’); TABLE_NAME PARTITION_NAME NUM_ROWS —————————— —————————— ———- PARENT_TAB PART_2012 1 PARENT_TAB PART_2013 1 PARENT_TAB PART_2014 1 PARENT_TAB PART_2015 1 CHILD_TAB PART_2012 1 CHILD_TAB PART_2013 0 CHILD_TAB PART_2014 3 CHILD_TAB PART_2015 0
8 lignes sélectionnées.
Les partitions PART_2013 et PART_2015 ne contiennent aucun enregistrement car aucune ligne de la table CHILD_TAB n’a pour parent_tab_id 2 ou 4.
Pour les versions antérieures à la 11.1, il est impossible de supprimer une partition d’une table parent sans au préalable désactiver les contraintes foreign key (type ‘R’), au risque de déclencher
L’erreur ORA-02266.
Avec le partitionnement par référence, la suppression d’une partition dans la table parent devient possible, sans désactiver les foreign keys.
SQL> alter table parent_tab drop partition part_2012; Table modifiée.
On peut vérifier la suppression de la partition correspondante dans la table CHILD_TAB aussi.
SQL> select table_name, partition_name from user_tab_partitions where table_name =’PARENT_TAB'; TABLE_NAME PARTITION_NAME —————————— —————————— PARENT_TAB PART_2013 PARENT_TAB PART_2014 PARENT_TAB PART_2015 SQL> select table_name, partition_name from user_tab_partitions where table_name =’CHILD_TAB'; TABLE_NAME PARTITION_NAME —————————— —————————— CHILD_TAB PART_2013 CHILD_TAB PART_2014 CHILD_TAB PART_2015
En revanche, cette opérations n’est pas permise sur la table CHILD_TAB
SQL> alter table CHILD_TAB drop partition part_2013; alter table CHILD_TAB drop partition part_2013 * ERREUR à la ligne 1 : ORA-14255: la table n’est pas partitionnée selon la méthode Range, List, Composite Range ou Composite List
Il existe toutefois une limite avec le TRUNCATE d’une partition de la table parent qui n’est possible que si la partition correspondante de la table enfant est vide.
SQL> alter table parent_tab truncate partition part_2014; alter table parent_tab truncate partition part_2014 * ERREUR à la ligne 1 : ORA-02266: les clés primaires/uniques de la table sont référencées par des clés étrangères
Même avec l’option CASCADE :
SQL> alter table parent_tab truncate partition part_2014 cascade; alter table parent_tab truncate partition part_2014 cascade * ERREUR à la ligne 1 : ORA-14054: option ALTER TABLE TRUNCATE PARTITION non valide.
Nouveautés 12c.
Avec la 12c, le TRUNCATE est possible avec l’option cascade :
SQL> alter table parent_tab truncate partition part_2012; alter table parent_tab truncate partition part_2012 * ERREUR à la ligne 1 : ORA-02266: les clés primaires/uniques de la table sont référencées par des clés étrangères
Avec l’option cascade :
SQL> alter table parent_tab truncate partition part_2012 cascade; Table tronquée.
On peut se poser la question suivante, le partitionnement par référence est-il possible quelque le type de partitionnement de la table parent ?
Cela marche avec le partitionnement par HASH et par LIST et par RANGE évidemment.
L’exemple utilisé ci-dessus utilise une table parent partitionnée par « RANGE ».
Qu’en est-il du partitionnement par « INTERVAL » qui dérive de la partition par « RANGE » ?
Sur une base 11g2 :
SQL> CREATE TABLE parent_tab ( 2 id NUMBER NOT NULL, 3 code VARCHAR2(10) NOT NULL, 4 created_date DATE, 5 CONSTRAINT parent_tab_pk PRIMARY KEY (id) 6 ) 7 PARTITION BY RANGE (created_date) INTERVAL (NUMTOYMINTERVAL(1, ‘YEAR’)) 8 ( 9 PARTITION part_2012 VALUES LESS THAN (TO_DATE(’01-01-2013′,’DD-MM-YYYY’)), 10 PARTITION part_2013 VALUES LESS THAN (TO_DATE(’01-01-2014′,’DD-MM-YYYY’)), 11 PARTITION part_2014 VALUES LESS THAN (TO_DATE(’01-01-2015′,’DD-MM-YYYY’)), 12 PARTITION part_2015 VALUES LESS THAN (TO_DATE(’01-01-2016′,’DD-MM-YYYY’)) 13 );
Table créée.
SQL> CREATE TABLE child_tab ( 2 id NUMBER NOT NULL, 3 parent_tab_id NUMBER NOT NULL, 4 code VARCHAR2(10), 5 –created_date DATE, 6 CONSTRAINT child_tab_pk PRIMARY KEY (id), 7 CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id) REFERENCES parent_tab (id) on delete cascade 8 ) 9 PARTITION BY REFERENCE (child_parent_tab_fk); CREATE TABLE child_tab ( * ERREUR à la ligne 1 : ORA-14659: La méthode de partitionnement de la table parent n’est pas prise en Charge
Donc pas possible avec une table parent partitionnée par interval.
Cela devient possible avec la 12c
SQL> CREATE TABLE parent_tab ( 2 id NUMBER NOT NULL, 3 code VARCHAR2(10) NOT NULL, 4 created_date DATE, 5 CONSTRAINT parent_tab_pk PRIMARY KEY (id) 6 ) 7 PARTITION BY RANGE (created_date) INTERVAL (NUMTOYMINTERVAL(1, ‘YEAR’)) 8 ( 9 PARTITION part_2012 VALUES LESS THAN (TO_DATE(’01-01-2013′,’DD-MM-YYYY’)), 10 PARTITION part_2013 VALUES LESS THAN (TO_DATE(’01-01-2014′,’DD-MM-YYYY’)), 11 PARTITION part_2014 VALUES LESS THAN (TO_DATE(’01-01-2015′,’DD-MM-YYYY’)), 12 PARTITION part_2015 VALUES LESS THAN (TO_DATE(’01-01-2016′,’DD-MM-YYYY’)) 13 ); Table créée. SQL> CREATE TABLE child_tab ( 2 id NUMBER NOT NULL, 3 parent_tab_id NUMBER NOT NULL, 4 code VARCHAR2(10), 5 –created_date DATE, 6 CONSTRAINT child_tab_pk PRIMARY KEY (id), 7 CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id) REFERENCES parent_tab (id) on delete cascade 8 ) 9 PARTITION BY REFERENCE (child_parent_tab_fk); Table créée.
