Nouveauté 11g: Interval partitioning

Un des inconvénients lorsqu'on utilise le partitioning est le fait d'avoir à ajouter manuellement les partitions lorsqu'on insère des données pour lesquelles aucune partition existante ne correspond.
Par exemple, pour une table partitionnée selon la date de mise à jour (une partition = 1 mois) il fallait soit pré-créer à l'avance des partitions pour les futurs mois, soit créer de nouvelles partitions au fur et à mesure qu'on avance dans le temps.

Oracle gomme ce soucis en proposant avec la 11g le partitioning par INTERVAL.
Il s'agit en fait d'une extension du partitioning BY RANGE. Avec ce type de partitioning si une ligne d'une table partitionnée BY RANGE selon une colonne DATE ne correspond pas à une partition existante, oracle créera automatiquement la partition manquante.

EXEMPLE

Tout d'abord je crée une table partitionnée BY RANGE avec l'option INTERVAL:

SQL> CREATE TABLE test_interval
2  (id number,
3  creation_date date default sysdate)
4  partition by range (creation_date)
5  interval (numtoyminterval(1,'MONTH'))
6  ( PARTITION p_jan2010 VALUES
7  LESS THAN (TO_DATE('01-02-2010','DD-MM-RRRR')))
8  /

Table crÚÚe.

La table est partitionnée selon la colonne CREATION_DATE. La première partition correspond aux données ayant une date inférieure au 01/02/2010. La fonction interval (numtoyminterval(1,'MONTH') indique que chaque partition correspond à un mois.

SQL> set lines 500
SQL> select partition_name, high_value from user_tab_partitions where
table_name='TEST_INTERVAL' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_JAN2010                      TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Si j'insère une ligne dans la table avec une date au mois de janvier 2010 la ligne ira dans la partition existante, par contre si j'insère une ligne avec une date au mois de février Oracle va créer la partition manquante et mettre la ligne dans cette nouvelle partition:

SQL> insert into TEST_INTERVAL values (1, '01-01-10');

1 ligne crÚÚe.

SQL> commit;

Validation effectuÚe.

SQL> select partition_name, high_value from user_tab_partitions where
table_name='TEST_INTERVAL' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_JAN2010                      TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> insert into TEST_INTERVAL values (2, '01-02-10');

1 ligne crÚÚe.

SQL> commit;

Validation effectuÚe.

SQL> select partition_name, high_value from user_tab_partitions where
table_name='TEST_INTERVAL' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_JAN2010                      TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P27                        TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

La partition SYS_P27 est la partition crée par Oracle. On perd donc le contrôle sur le nommage des partitions mais on n'a plus le soucis de créer à la main les partitions manquantes.

Si vous venez de migrer de la 10g vers la 11g et que vous avez des tables partitionnées BY RANGE vous pouvez appliquer l'Interval partitioning sur cette table sans avoir à recréer votre table. Il suffit d'utiliser la commande suivante:

SQL> alter table test_interval set interval (NUMTOYMINTERVAL(1, 'MONTH'));

Table modifiÚe.

Pour que ça fonctionne il ne faut pas que vous ayez de partition avec une MAXVALUE sinon vous tomberez sur l'erreur suivante: ORA-14759.

Il existe d'autres nouveautés en matière de partitioning avec la 11g mais ils feront l'objet d'un nouveau post.