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;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Plan hash value: 3603812413
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1883 | 24479 | 15 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1883 | 24479 | 15 (0)| 00:00:01 | 1 | 2 |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TAB_PARTITIONNEE | 1883 | 24479 | 15 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | INDEX_PART | 1883 | | 10 (0)| 00:00:01 | 1 | 2 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<2000)
15 lignes sélectionnées.
Ecoulé : 00 :00 :00.19
En supprimant une partition sans une maintenance de l’index, Oracle oublie l’index (skip unsuable indexes)
SQL> alter table tab_partitionnee drop partition p1;
Table modifiée.
SQL> explain plan for
2 select * from tab_partitionnee where id <2000;
Explicité.
Ecoulé : 00 :00 :00.01
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Plan hash value: 3571423985
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1883 | 24479 | 52 (2)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 1883 | 24479 | 52 (2)| 00:00:01 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | TAB_PARTITIONNEE | 1883 | 24479 | 52 (2)| 00:00:01 | 1 | 2 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<2000)
14 lignes sélectionnées.
Ecoulé : 00 :00 :00.08
C’est parce que l’index a un status « UNUSABLE » et Oracle l’ignore automatiquement
SQL> select index_name, partition_name, status from user_ind_partitions where index_name='INDEX_PART'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- INDEX_PART P1 UNUSABLE INDEX_PART P2 UNUSABLE INDEX_PART P3 UNUSABLE 3 lignes sélectionnées.
Si nous essayons de suggérer l’utilisation de l’index au travers d’un hint, la requête sort en erreur :
SQL> select /*+ index (tab_partitionnee INDEX_PART) */ count(*) from tab_partitionnee where id <2000; select /*+ index (tab_partitionnee INDEX_PART) */ count(*) from tab_partitionnee where id <2000 * ERREUR à la ligne 1 : ORA-01502: l'index 'SYS.INDEX_PART' ou sa partition est inutilisable
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 :
SQL> explain plan for
2 select * from tab_partitionnee where id <2000;
Explicité.
Ecoulé : 00 :00 :00.01
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4172129126
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 11 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 13 | 11 (0)| 00:00:01 | 1 | 2 |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TAB_PARTITIONNEE | 1 | 13 | 11 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | INDEX_PART | 1741 | | 6 (0)| 00:00:01 | 1 | 2 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<2000)
15 lignes sélectionnées.
Ecoulé : 00 :00 :00.20
Nous allons reprendre notre table et vérifier que les index sont mis jours avec la clause UPDATE INDEXES.
SQL> alter table tab_partitionnee drop partition p1 2 UPDATE INDEXES; Table modifiée. Ecoulé : 00 :00 :00.05 SQL> select index_name, partition_name, status from user_ind_partitions where index_name='INDEX_PART'; INDEX_NAME PARTITION_ STATUS -------------------- ---------- -------- INDEX_PART P1 USABLE INDEX_PART P2 USABLE INDEX_PART P3 USABLE 3 lignes sélectionnées.
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 :
SQL> select index_name, partition_name,status, orphaned_entries from user_ind_partitions where index_name='INDEX_PART'; INDEX_NAME PARTITION_ STATUS ORP -------------------- ---------- -------- --- INDEX_PART P1 USABLE YES INDEX_PART P2 USABLE YES INDEX_PART P3 USABLE YES 3 lignes sélectionnées.
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).
SQL> select job_name, last_start_date, next_run_date from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB'; JOB_NAME LAST_START_DATE NEXT_RUN_DATE -------------------------------- ---------------------------------------- ---------------------------------------- PMO_DEFERRED_GIDX_MAINT_JOB 30/09/15 08:16:47,432000 US/CENTRAL 01/10/15 02:00:00,436000 US/CENTRAL
Pour mettre l’index à jour manuellement, on peut tout simplement exécuter le job :
SQL> conn / as sysdba
Connecté.
SQL> EXEC DBMS_SCHEDULER.RUN_JOB ('PMO_DEFERRED_GIDX_MAINT_JOB');
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.52
Et l’index est mis à jour réellement.
SQL> select index_name, partition_name,status, orphaned_entries from user_ind_partitions where index_name='INDEX_PART'; INDEX_NAME PARTITION_ STATUS ORP -------------------- ---------- -------- --- INDEX_PART P1 USABLE NO INDEX_PART P2 USABLE NO INDEX_PART P3 USABLE NO 3 rows selected.
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.
