Partitionnement des Index et la nouveauté 12c pour les index globaux

Comme pour les tables, les index peuvent être partitionnés.

Il existe deux méthodes pour cela :

  • Equi-partition de l’index avec la table : C’est l’index local.
    Chaque partition de la table est indexée par une partition et une seule de l’index. Toutes les entrées d’une partition d’index donnée pointent sur une seule partition de table et toutes les lignes d’une partition de tables sont représentées dans une partition d’index.
  • Partition de l’index différente de celle la table : C’est l’index global.
    L’index global ne peut être partitionné que par « RANGE » ou par « HASH » (Hash possible seulement depuis la 10g) et une partition peut pointer sur n’importe quelle partition de la table ou toutes les partitions de la table. Le nombre de partitions peut être différent du nombre de partitions de la table.

Les Index locaux

On a deux types d’index locaux :

  • L’index local préfixé : on retrouve la clé de partitionnement de la table en tête de la liste des colonnes de l’index.
  • L’index local non préfixé : la clé de partitionnement de la table n’est pas en tête de la liste des colonnes de l’index ou n’y figure pas du tout.

Exemple :

SQL> Drop table tab_partitionnee purge;
Drop table tab_partitionnee purge
*
ERREUR à la ligne 1 :
ORA-00942: Table ou vue inexistante


SQL> Create table tab_partitionnee
2 (a number,
3 b number,
4 Data varchar2(20)
5 )
6 Partition by range(a)
7 (
8 Partition part_1 values less than (10) tablespace users,
9 Partition part_2 values less than (20) tablespace users
10 )
11 /

Table créée.

Et on peut créer les index locaux :

  • Index local préfixé :

SQL> Create index local_prefixed on tab_partitionnee (a,b) local ;
Index créé.

  • Index local non préfixé :

SQL> Create index local_nonprefixed on tab_partitionnee (b) local;
Index créé.

Ou encore :

SQL> Create index local_nonprefixed2 on tab_partitionnee (b ,a) local;

Index créé.

 

Les index globaux

Les index globaux utilisent un schéma de partitionnement différent de celui de la table. Et contrairement aux index locaux, on n’a qu’une seule classe d’index global partitionné : Index global préfixé.
L’index global non préfixé n’est pas supporté.

SQL> Create table tab_partitionnee
2 (timestamp date,
3 Id number
4 )
5 PARTITION BY RANGE (timestamp)
6 (
7 Partition p1 values less than (to_date('01-01-2015','dd-mm-yyyy')),
8 Partition p2 values less than (to_date('01-01-2016','dd-mm-yyyy'))
9 )
10 /
Table créée.

Et un index global préfixé :

SQL> Create index index_part on tab_partitionnee (id)
2 GLOBAL
3 Partition by range(id)
4 (
5 Partition p1 values less than (1000),
6 Partition p2 values less than (2000),
7 Partition p3 values less than (MAXVALUE)
8 )
9 /

Index créé.

L’index non préfixé n’est pas supporté :

SQL> Create index index_part2 on tab_partitionnee (timestamp, id)
2 GLOBAL
3 Partition by range (id)
4 (
5 Partition p1 values less than (1000),
6 Partition p2 values less than (MAXVALUE)
7 )
8 /

Partition by range (id)
*
ERREUR à la ligne 3 :
ORA-14038: un index GLOBAL partitionné doit avoir un préfixe

 

Maintenance des Index partitionnés

 

Les index locaux

Il n’y a aucune maintenance particulière à faire (pas besoin de rebuild pour mettre l’index à jour et le rendre valide) sur les index locaux suite à des opérations maintenance sur les tables.
 

Maintenance des index globaux

Contrairement aux index locaux, les index globaux doivent être reconstruits après les opérations de DDL (drop, split, truncate,…) sur les tables. On peut faire une opération de rebuild après intervention sur la table ou ajouter la clause « UPDATE INDEXES » dans l’opération DDL pour mettre les index globaux à jour.

Exemple :
Reprenons notre table TAB_PARTITIONNEE créée précédemment en y insérant des données :

SQL> Drop table tab_partitionnee purge;
Table supprimée.

SQL> Create table tab_partitionnee
2 (timestamp date,
3 Id number
4 )
5 PARTITION BY RANGE (timestamp)
6 (
7 Partition p1 values less than (to_date('01-01-2015','dd-mm-yyyy')),
8 Partition p2 values less than (to_date('01-01-2016','dd-mm-yyyy')),
9 Partition p3 values less than (MAXVALUE)
10 )
11 /
Table créée.

SQL> Create index index_part on tab_partitionnee (id)
2 GLOBAL
3 Partition by range(id)
4 (
5 Partition p1 values less than (1000),
6 Partition p2 values less than (2000),
7 Partition p3 values less than (MAXVALUE)
8 )
9 /
Index créé.

SQL> Insert into tab_partitionnee
2 Select to_date('31-12-2012','dd-mm-yyyy'), object_id from all_objects where object_id <2000 ;
1896 lignes créées.

SQL> Insert into tab_partitionnee
2 Select to_date('31-12-2015','dd-mm-yyyy'), object_id from all_objects where object_id between 2000 and 5000 ;
3001 lignes créées.

SQL> Insert into tab_partitionnee
2 Select to_date('31-12-2017','dd-mm-yyyy'), object_id from all_objects where object_id >5000 ;
85291 lignes créées.

SQL> exec dbms_stats.gather_table_stats(user,'TAB_PARTITIONNEE');
Procédure PL/SQL terminée avec succés.

L’utilisation de l’index est visible au travers de ce plan d’exécution

SQL> explain plan for
2 select * from tab_partitionnee where id <2000;

En supprimant une partition sans une maintenance de l’index, Oracle oublie l’index (skip unsuable indexes)

C’est parce que l’index a un status « UNUSABLE » et Oracle l’ignore automatiquement

Si nous essayons de suggérer l’utilisation de l’index au travers d’un hint, la requête sort en erreur :

Il aurait fallu ajouter la clause UPDATE INDEXES à l’opération de « DROP » pour mettre à jour l’index en live lors de la  suppression de la partition.
Ou alors faire un « rebuild » de l’index partition par partition.

Alter index INDEX_PART rebuild partition p1;
Alter index INDEX_PART rebuild partition p2;
Alter index INDEX_PART rebuild partition p3;

Ce qui rend l’index utilisable et les performances retrouvées :

Nous allons reprendre notre table et vérifier que les index sont mis jours avec la clause UPDATE INDEXES.

 

La nouveauté 12c : La maintenance asynchrone de l’index

Avec la 12c, la clause UPDATE INDEXES maintient l’index valide et utilisable mais il n’est pas reconstruit. La maintenance de l’index est remise à plus tard, ce qui permet dans le cas des grosses volumétries de gagner du temps.
La nouvelle colonne de la vue USER_IND_PARTITIONS montre bien qu’il existe des entrées orphelines pour cet index :

Ce qui peut faire gagner énormément de temps quand on a des index volumineux.

L’index est mis à jour par le job PMO_DEFRRED_GIDX_MAINT_JOB à 02:00 tous les jours (par défaut).

Pour mettre l’index à jour manuellement, on peut tout simplement exécuter le job :

Et l’index est mis à jour réellement.

Nous aurions pu mettre à jour les partitions de l’index avec un rebuild ou utiliser aussi la procédure DBMS_PART.CLEANUP_GIDX.

Attention, cette maintenance asynchrone ne marche pas sur les tables du schéma SYS et n’est valable que pour les tables de type HEAP.
 

About Fousseynou Ndiaye

Fousseynou Ndiaye has written 5 post in this blog.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

%d blogueurs aiment cette page :