Discussion :

[jeremzzz]

Bonjour à tous.

J'ai le tablespace sur lequel tous mes indexes sont stockés qui explosent parfois, et je suis obligé de lui mettre plus de taille, sachant que je suis bientôt au maximum de la capacité disque du serveur base de données.

J'ai quelques grosses tables possédant plusieurs indexes, et je suis sûr de pouvoir en supprimer sans affecter des performances quelconques.
Mais je ne connais pas bien le principe des indexes et leur fonctionnement, ni comment optimiser tout ça.

Je veux donc bien quelques explications à partir d'un exemple sur ma table de ligne de commandes (qui n'a pas de PK d'ailleurs, mais un index unique avec les champs déclarés en not null .. pas le plus opti et j'ai crisé quand j'ai vu ça) :

Index unique (équivalent PK...) : societe, commande, num lig
Index non unique :
1) article, societe, solde
2) societe, article
3) societe, commande, magasin
4) societe, commande, article
5) societe, commande
6) societe, numero article client

Le 6, c'est moi qui l'ait créé aujourd'hui et c'est en le faisant que j'ai vu le reste .. mais il m'a fait passer une requête de 7 sec à 0,1 donc il est parfait.

Le code société est censé être présent dans 100% de nos requêtes, ça explique sa présence dans tous mes indexes.

Merci d'avance.

[Ikebukuro]

Houlala, pas simple...

Une table en production sans PK... c'est extrêmement rare, pas une aberration mais quand même...
"index unique avec les champs déclarés en not null" : pas de pb, mais est-il possible justement de recréer cette contrainte UNIQUE en PK, ne serait-ce que pour avoir une table plus propre?

Il n'y a que des index composés, pourquoi? Le sujet n'est pas évident mais pourquoi pas N index simples?
Le problème est qu'une colonne dans un index voit ses valeurs doublonnées par rapport à la table puis triplées etc etc sur le disque dur : la colonne SOCIETE est présente 7 fois, à chaque fois c'est de l'espace disque en plus

Le seul bon conseil que je peux te donner pour gérer tes index est de partir des ordres SQL lancés par les utilsateurs et voir, si possible, quels index sont utilisés.

Sinon, pour monitorer tes index, un petit lien vers mon blog : http://dbaoraclesql.canalblog.com/ar.../35263875.html
Si c'est trop pointu, utilise le monitoring de base d'Oracle sur une période de quelques semaines et déjà tu auras une idée des index utilisés ou non mais pas de leur fréquence d'utilisation.

[McM]

Le principe basique d'un index, c'est d'optimiser les accès aux données. Plutôt que de lire toute la table à chaque fois, Oracle va lire l'index le plus adéquat par rapport à ta requête (moins de lectures à faire), puis lire la table en fonction des lignes de l'index lu. L'index est maintenu automatiquement à chaque modification de données.

Au bout d'un moment, à force d'insert, de delete et d'update, la taille des indexes augmente un peu trop et une "reconstruction" peut s'avérer nécessaire

Un shrink (préférable)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER INDEX index_name SHRINK SPACE

ou un rebuild (plus long, crée un nouvel index (donc besoin d'avoir autant d'espace libre dans ton tablespace, mais tu peux aussi rebuilder dans un autre tablespace)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER INDEX index_name REBUILD ONLINE

Mieux vaut passer ces ordre quand la base n'est pas chargée, lock de la table le temps de reconstruction (pour le Rebuild), pour le shrink je ne sais plus.

[jeremzzz]

@McM : J'ai pas de souci à ce niveau-là, je n'ai aucune purge de mes lignes de commande, donc j'ai un historique plutôt conséquent.
Et je connais le rebuild et le shrink (qui ne locke pas) ainsi que les variantes du shrink comme le space compact et autre (mais certaines sont accessibles qu'en enterprise)

@Ike : Oui je confirme que c'est rare, en 7 ans dans ma boite c'est la seule table où j'ai vu ça .. mais c'est en production comme ça depuis des années et des années donc je préfère ne pas y toucher pour le moment.

En fait le soft est censé être multi société (dans la théorie, dans la pratique j'ai qu'un seul client qui en a plusieurs)
Du coup la société fait parti de la PK de toutes mes tables.

Autant dire qu'un index sur cette colonne, dans la pratique, stocke absolument toute la table.
Mais bon, c'est nécessaire au cas-où un jour le client passe en multi soc, vu que le soft est prévu pour le gérer.

Donc si je te comprends bien, tu me dis qu'un index sur la colonne soc, et un autre index sur la colonne commande aura le même résultat et les mêmes perfs qu'un index sur les 2 colonnes en même temps ? Sauf que du coup, si j'enlève la soc de tous mes index pour ne la garder que sur un seul (et je peux faire pareil pourquoi pas avec la commande), je gagne une place monstrueuse.

Autre question aussi, si je prends en compte ces 3 indexes-là, le dernier ne sert à rien en fait ? Parce que si je ne me trompe pas, si je fais une requête SQL sur soc/commande, ça prendra soit le 3 soit le 4 si le 5 n'existait pas, et j'aurais les même perf ?
3) societe, commande, magasin
4) societe, commande, article
5) societe, commande

Merci pour le lien, je vais jeter un coup d’œil.

[McM]

Donc si je te comprends bien, tu me dis qu'un index sur la colonne soc, et un autre index sur la colonne commande aura le même résultat et les mêmes perfs qu'un index sur les 2 colonnes en même temps ? Sauf que du coup, si j'enlève la soc de tous mes index pour ne la garder que sur un seul (et je peux faire pareil pourquoi pas avec la commande), je gagne une place monstrueuse.

Houla .. non .. attention tu n'es pas en Index BITMAP, Oracle va prendre 1 seul index, pas plusieurs et les combiner
Admettons que tu crées un index sur "Commande", et un sur "Ste", un select where ste = x and commande = y, Oracle va prendre l'index Commande uniquement (parce que l'index ste ne servira à rien, pas assez restrictif).

[skuatamad]

Donc si je te comprends bien, tu me dis qu'un index sur la colonne soc, et un autre index sur la colonne commande aura le même résultat et les mêmes perfs qu'un index sur les 2 colonnes en même temps ?

Non, pas du tout.

Sauf que du coup, si j'enlève la soc de tous mes index pour ne la garder que sur un seul (et je peux faire pareil pourquoi pas avec la commande), je gagne une place monstrueuse.

C'est également possible de compresser l'index :
https://asktom.oracle.com/pls/apex/a...74600346960063

Le code société est censé être présent dans 100% de nos requêtes, ça explique sa présence dans tous mes indexes.

Il n'est pas présent dans le 1er index :
1) article, societe, solde

Autre question aussi, si je prends en compte ces 3 indexes-là, le dernier ne sert à rien en fait ? Parce que si je ne me trompe pas, si je fais une requête SQL sur soc/commande, ça prendra soit le 3 soit le 4 si le 5 n'existait pas, et j'aurais les même perf ?

Peut être pas exactement les mêmes perfs, mais je suis également d'avis que l'index 5 est redondant est un bon candidat pour être supprimé.

McM, Il existe potentiellement la jointure entre index, mais ce n'est évidemment pas une raison pour supprimer les index multi colonnes :
Opération INDEX JOIN (jointure entre indexes)

[pachot]

Bonjour,
A lire: https://use-the-index-luke.com/fr (le site et le livre).
Cordialement,
Franck.