mardi 3 août 2010

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.





6 commentaires:

  1. Si vous vous demandez comment créer des partitions "par jour" il suffit d'utiliser la commande " interval (NUMTODSINTERVAL(1,'day'))"

    RépondreSupprimer
  2. Bonjour Ahmed,

    Sais-tu si on peut définir une convention de nommage pour le nom des partitions ?

    Merci.

    RépondreSupprimer
    Réponses
    1. Salut Christophe,
      Malheureusement c'est l'inconvénient: on ne peut pas avoir une convention de nommage des partitions

      Supprimer
    2. Salut,

      Merci de ton retour. J'ai pas mal cherché de mon côté et effectivement on ne peut. Dommage.
      Merci néanmoins.

      Supprimer
  3. Si je n'ai pas accès au nom, comment savoir quelle partition dropper ? Typiquement lors du chargement quotidien d'une base décisionnelle, on supprime le mois, le jour ou l'année en cours. Sur une table de plusieurs millions de lignes, un DROP PARTITION est bien plus rapide qu'un DELETE partiel, donc c'est critique.
    Je vois bien une combine avec une recherche de la dernière valeur dans user_tab_partitions, mais bon...

    RépondreSupprimer
    Réponses
    1. Il faut te faire un script qui se base sur XXX_TAB_PARTITIONS et le HIGH_VALUE.
      Par exemple, pour un de mes clients j'avais utilisé le script suivant pour dropper les partitions de moins de 2 mois:

      DECLARE
      v_sql varchar2(500);
      v_date date;

      BEGIN
      FOR cur_rec in (select TABLE_NAME,PARTITION_NAME, HIGH_VALUE
      from user_tab_partitions where table_name='&table_name' and PARTITION_NAME not like 'P1_%')
      LOOP
      v_date := to_date(SUBSTR(cur_rec.high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
      IF v_date < (sysdate - 60) then
      v_sql := 'alter table '|| cur_rec.table_name || ' drop partition ' || cur_rec.partition_name;
      execute immediate v_sql;
      --DBMS_OUTPUT.PUT_LINE(v_sql);
      END IF;
      END LOOP;
      END;
      /

      Supprimer