Archives du mot-clé index

12c: Index partiel sur tables partitionnées

Publié le par
Répondre

On n’a pas toujours besoin d’indexer toutes les partitions. Dans une table qui garde tout l’historique, on peut avoir le besoin de n’indexer que la partition courante, accédées en transactionnel. Les anciennes partitions ont toujours les données, pour des besoins de reporting, mais sans devoir stocker tous les index utiles à la partition courante.
Avant la 12c, on peut faire ̤a avec des index locaux en les rendant unusable (et skip_unusable_indexes=true). Mais pas de solution pour les index globaux Рsinon archiver les anciennes lignes dans une autre table et faire une vue UNION ALL.

En 12c, on peut peut avoir des index partiels:

CREATE INDEX ... GLOBAL INDEXING PARTIAL
CREATE INDEX ... LOCAL INDEXING PARTIAL

Et on précise au niveau de la tables les partitions qui ne seront pas indexées par les Partial Index:

ALTER TABLE ... MODIFY PARTITION ... INDEXING OFF

Le résultat:

  • Les Partial Local Index auront leur partitions en ‘indexing off’ au status UNUSABLE (donc garder la valeur par défaut skip_unusable_indexes=true)
  • Les Partial Global Index n’auront pas d’entrée pour les lignes des partitions en ‘indexing off’. S’il y en avait auparavant, elles deviendront orphelines

L’opération inverse elle devra reconstruire les partitions locales, et réindexer les index globaux pour ces partitions.

C’est transparent: les SELECT qui utilisent l’index feront un UNION ALL pour aller chercher les données indexées via index, et les autres via partition full scan.
Par exemple, lorsque la partition 1 est en ‘indexing off’, la partition 2 en ‘indexing on’, et l’index global en ‘indexing partial':

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |         |     1 |     8 |    32   (0)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |    32   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                  |         |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TEST    |     1 |     8 |     2   (0)| 00:00:01 |     2 |     2 |
|*  4 |     INDEX RANGE SCAN                         | TEST_N  |     1 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION LIST SINGLE                     |         |     1 |     8 |    30   (0)| 00:00:01 |     1 |     1 |
|*  6 |     TABLE ACCESS FULL                        | TEST    |     1 |     8 |    30   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------------

L’exemple complet dans la demo

12c: Indexer les mêmes colonnes avec différents types d’index

Publié le par
Répondre

La 11g avait apporté les index invisibles: on peut créer des index qui sont maintenus, mais ne sont pas utilisés par les requêtes. Pour pouvoir les tester par exemple.
Mais il était toujours impossible de tester un différent type d’index sur des colonnes déjà indexées (pour le passer en bitmap, en reverse, le compresser,…).
L’erreur était:

ORA-01408:  such column list already indexed
Cause:  A CREATE INDEX statement specified a column that is already indexed. A single column may be indexed only once. Additional indexes may be created on the column if it is used as a portion of a concatenated index, that is, if the index consists of multiple columns.
Action: Do not attempt to re-index the column, as it is unnecessary. To create a concatenated key, specify one or more additional columns in the CREATE INDEX statement.

En 12c, cette restriction est levée dans le cas où l’index que l’on crée est invisible.
Par exemple, je n’ai pas d’erreur en exécutant ceci:

create unique index TEST1 on TEST(num) ;
create unique index TEST2 on TEST(num) reverse invisible;

On a donc 2 index sur les mêmes colonnes. Ils sont maintenus (donc utilisables) mais un seul n’est utilisé: celui qui est visible.
Il est facile de passer de l’un à l’autre en changeant leur visibilité: c’est une opération online (pas de verrou).

Deux gros avantage:

  • créer un index peut être long. On peut le faire online, mais le fait de devoir supprimer l’ancien avant nous laissait un certain temps dans index.
  • si on ne le crée pas online, la création du nouvel index peut être très rapide car elle utilise l’ancien.

On a donc le choix: maximiser la disponibilité de l’application en le créant online, ou minimiser le temps de création de l’index.

Si l’index supporte une contrainte d’intégrité, c’est un peu plus complexe.
Le détail dans la demo avec une introduction sur de nouvelles opérations online apportées par la 12c (drop index et drop constraint).