ORA-01654 Delete puis insert table avec index bitmap

Bonjour,

Nous avons dans notre base une table sur laquelle ont été définis des index bitmap. Jusque ce jour la mise à jour de cette table se fait 2 fois par mois : une fois par un calcul provisoire qui insère des données, une seconde par un calcul définitif consistant à supprimer les données provisoire du mois correspondant puis à les recalculer. Ce fonctionnement ne pose pas de problème.
Nous mettons en place un mécanisme de purge basé sur un critère de date. Cette purge au premier lancement, supprime les données sur plusieurs années. Une fois la purge jouée dans l'environnement de recette, la procédure de suppression puis ré-insertion de données s'est traduite par une erreur :

ORA-01654 : impossible d'étendre l'index ... dans le tablespace ...

Afin de libérer de la place nous avons reconstruit l'ensemble des index bitmap par la commande

alter index ... rebuild.

L'espace libre passe alors de 2,6% à 73%.
Hélas, la relance de l'insertion provoque à nouveau la même erreur ORA-01654.
Sur notre serveur de développement nous observons le même comportement : l'insertion entraine un accroissement exponentiel des index bitmap (avec ou non l'erreur ORA-01654 selon le mois de données choisi).

Pourquoi une opération d'insertion de données qui fonctionnait avant la purge massive et/ou le rebuild ne fonctionnerait plus ensuite ?

Quelles solutions pouvons-nous mettre en place ?

Bonjour,

Les index et tables sont sur les mêmes tablespaces ?

Sinon, je pense que ton problème porte un nom : high water mark

mais je me trompe peut être, à faire confirmer.

Cordialement. GM.

Les index et les tables sont dans des tablespaces distincts.

ce topic peut t'aider je pense :

par ici

cordialement, GM

J'ai supprimé puis reconstruit les index bitmap et j'ai toujours le même problème.
Pour info :
notre procédure insère les nouveaux enregistrements ligne par ligne (et effectue aussi des update sur ceux-ci).
Un test d'insertion en bloc à partir d'une table de sauvegarde

Code:

---

insert into table1 select * from table2 where condition

---

ne provoque pas d'extension abusive des index bitmap. La condition porte sur les données d'un mois de façon à avoir un volume de données du même ordre de grandeur que celui inséré par la procédure de calcul.

Suite à mes tests quelques informations supplémentaires :
j'ai effectué dans notre environnement de développement un truncate de la table puis ré-inséré des données en jouant en boucle la procédure de calcul qui alimente la table : aucun problème de saturation. J'ai effectué différentes purges suivies de calcul et n'observe qu'une très légére augmentation de la taille des index.
J'ai alors effectué un rebuild de l'index : suite à ce rebuild une insertion ligne par ligne par la procédure est très ralentie par rapport à la même insertion avant rebuild et la taille des index croit exponentiellement.

On observe donc qu'un rebuild d'index au lieu d'améliorer le comportement le dégrade complétement.

Citation:

---

On observe donc qu'un rebuild d'index au lieu d'améliorer le comportement le dégrade complétement.

---

Un rebuild d'un index recalcule les statistiques automatiquement. Il fallait peut-être enregistrer les stats avant rebuild et les remettre après le rebuild pour voir si vous retrouvez votre temps d'insertion d'avant rebuild.

Citation:

---

Envoyé par Mohamed.Houri

Un rebuild d'un index recalcule les statistiques automatiquement.

---

J'ignorais ça, merci pour l'info :ccool:

Citation:

---

Envoyé par Mohamed.Houri

Un rebuild d'un index recalcule les statistiques automatiquement.

---

J'observe un comportement légèrement différent si je met ou non l'option compute statistics à la commande rebuild.
Avec l'option compute statistics la taille des index augmente moins vite lors de la réinsertion de données (j'obtient le message ORA-01654 après 3 lancement de la procédure d'alimentation de la table au lieu d'un seul)
Pour info, chaque lancement de la procédure insère un nombre équivalent de lignes (mais pour un mois différent).

Je cite ici Tom Kyte :

Citation:

---

Bitmaps in fact do update rather quickly. However, they also tend to SERIALIZE and do not respond well to slow by slow (row by row) updates. They are suitable for read only/read mostly data that is modified in BULK (not row by row)

---

Si vous avez des procédures qui font du ligne à ligne "massif", il faudrait envisager :
- de passer à une gestion ensembliste Bulk
- ou sinon de dropper les index bitmaps au début de la procédure et de les re-créer à la fin.

Le tout nécessitant des mesures pour s'assurer que l'effet produit est bien le bon.

Citation:

---

Envoyé par Rei Ichido

Si vous avez des procédures qui font du ligne à ligne "massif", il faudrait envisager :
- de passer à une gestion ensembliste Bulk
- ou sinon de dropper les index bitmaps au début de la procédure et de les re-créer à la fin.

Le tout nécessitant des mesures pour s'assurer que l'effet produit est bien le bon.

---

Une gestion Bulk ne semble pas adapté à la procédure de calcul qui alimente notre table.
Nous avions proposé à notre client de dropper les index au début de la procédure et de les re-créer à la fin. Notre client ne tient pas à modifier la procédure de calcul même à la marge.
Nous avons contourné le problème en remplaçant les index bitmap par des index b-tree sans constater pour le moment de dégradation notable dans les vitesse d'exécution des requêtes de sélection dans la table.

C'est plutôt inquiétant sur la pertinence du choix des index bitmaps en premier lieu ...
À quoi ressemble votre table, et combien de colonnes étaient indexées en bitmap ? Et à quoi ressemblent vos requêtes ?
Il est intéressant d'avoir des index bitmaps quand on a beaucoup de colonnes avec des facteurs qui, chacun pris séparément, est peu discriminant et avec idéalement peu de valeurs différentes, et qu'on s'attend à ce que toute combinaison de ces colonnes puisse être utilisée en filtre.

Il y avait 12 index bitmap sur la table.
Seuls 11 index ont été reconstruit en b-tree, le 12ème correspondait à une colonne qui ne contient désormais plus qu'une seule valeur.
Effectivement la plupart de ces index contenaient peu de valeurs distinctes.
Par contre l'essentiel des requêtes de sélection s'appuie en premier sur des critères de dates, les autres colonnes intervenant pour filtrer plus précisément les données.
Je ne travaillait pas encore sur cette application au moment du choix des index bitmap, je ne sais donc pas avec précision pourquoi ils ont été choisis pour cette table.
Dans notre application les index bitmap sont aussi utilisés - de façon pertinente - sur la table la plus volumineuse de notre base - la seule a être partitionnée. La purge de cette dernière table se traduisant par la suppression d'une partition (et des partitions d'index correspondante), elle ne pose aucun problème.