Discussion :

[superfly]

Bonjour,
Voila je souhaites reconstruire les index d'une table, mais je ne connais pas la commande.
Actuellement je détruis l'index et je le reconstruis, mais je pense qu'il existe une autre méthode?

Pour clarifier la question comment faites vous pour reconstruire un index?

Merci

[superfly]

En fait dans la doc pour la reconstruction d'index :

Code :

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

 ALTER INDEX index_name REBUILD

Par contre je n'ai pas bien compris dans la doc s'il était préférable de detruire puis de construire ou bien de reconstruire directement avec (Alter Index...).

Quelqu'un saurait-il ce qui est plus performant.
Merci

[ghostlord79]

selon moi il est préférable de détruire et de reconstruire

[orafrance]

selon moi c'est exactement pareil

[Marc Musette]

Compared to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.

en direct de la doc (Administrator guide / Rebuilding an Existing Index au chap 16)

[superfly]

Oui Marc c'est exactement ce que j'ai lu dans la doc Oracle, mais
je ne comprends pas avec certitude ce que cela veux dire.
Je sais mon anglais est limite
Peux-tu me le faire en french merci

[Marc Musette]

et bien il est préférable de reconstruire plutôt que DROP+CREATE



au fait, pourquoi reconstruis-tu ton index?

[superfly]

Merci Marc
Je reconstruis car j'ai chargé ma table avec 1.6 millions de ligne supplémentaire pour des tests.
Je me pose d'ailleurs une question sur les perfs d'une maniere général, n'est-il pas intérressant de reconstruire les index de toutes les tables d'une base dans un souci de plan de maintenance?

[orafrance]

et bien il est préférable de reconstruire plutôt que DROP+CREATE



au fait, pourquoi reconstruis-tu ton index?

faux

ce n'est pas de la traduction mais de l'interprétation là

Moi je comprends que DROP+CREATE est plus rapide que REBUILD mais certainement pas que c'est mieux, je dirais que ça dépend du contexte. Si l'appli ne tourne pas (on est seul sur la base) un DROP+CREATE est préférable mais pour garder l'index en ligne (attention parce que ça ne marche pas super si la table est très chargée) alors le REBUILD est mieux.

Le rebuild (ou create/drop) sert à "défragmenter" l'index ainsi la base est plus rapide pour trouver les données dont elle a besoin, donc c'est à faire régulièrement sur les tables qui sont souvent updatées ou deletées

Je pense aussi qu'il est préférable de recalculer les statistiques après un rebuild

[Marc Musette]

cela dépend

reconstruire de manière automatique (tous les six mois, une fois par an) les indexes n'est pas réellement fondé : beaucoup de tracas pour rien ou vraiment pas grand chose (et cela reste à démontrer)

si effectivement problème de perfomance il y a, alors mieux vaut analyser le problème et reconstruire l'index si celui-ci est en cause

mais dans 99% des cas, ce n'est pas utile

dans le cas d'un énorme chargement (mais cela n'arrive qu'en test / pas en prod je suppose), le gain de temps peut-être mesurable mais j'ignore réellement si cela se mesure en secondes, minutes ou heures ?

ce serait intéressant que tu le testes?

[orafrance]

Si si ça peut être très intéressant particuilèrement dans Oracle Application où pas mal de purge existe et donc libèrent de la place.

Dans d'autres applications le gain peut être remarquable, pas obligatoirement en perf (j'ai rarement vu de gros gain) mais en espace disque ce qui est intéressant pour les sauvegardes à froid notament

[superfly]

Ok je vois j'ai fais les tests pour infos :
Rebuild temps d'execution 141,075
Create 197.274 second

J'ai aussi trouvé ceci dans la doc oracle :

Rebuilding Global Index Partitions
You can rebuild global index partitions in two ways:

Rebuild each partition by issuing the ALTER INDEX...REBUILD PARTITION statement (you can run the rebuilds concurrently).

Drop the index and re-create it.

Note:
This second method is more efficient because the table is scanned only once.

Qui croire à votre avis :

[orafrance]

je répéte

Le create drop est plus performant parce qu'il ne scanne la table qu'une fois alors que le rebuild scanne la table en plusieurs fois puisque la table reste accessible.

Néanmoins, le résultat est rigoureusement identique une fois l'opération finie.

Donc tout dépend du contexte dans lequel l'opération est effectué : disponibilité importante des tables (mais avec des locks généré par le rebuild) ou non accessibilité de la table envisageable (du moins la table reste accessible mais les performances sont nettement dégradées en attendant la création de l'index).

[Marc Musette]

Si si ça peut être très intéressant particuilèrement dans Oracle Application où pas mal de purge existe et donc libèrent de la place.

beaucoup de purge... pourquoi pas des partitions ? et pourquoi pas un COALESCE plutôt qu'un rebuild

Dans d'autres applications le gain peut être remarquable, pas obligatoirement en perf (j'ai rarement vu de gros gain) mais en espace disque ce qui est intéressant pour les sauvegardes à froid notament

pas en perf effectivement (question initiale de notre ami...)
en espace disque --> oui on gagne de la place mais généralement pas pour longtemps ... et pourquoi pas un COALESCE

ce n'est pas de la traduction mais de l'interprétation là

pour ce qui est de mon anglais, il faudra certainement que l'on fasse appel à un linguiste car je continue à défendre ma traduction

Le create drop est plus performant

bizarre le test de superfly montre le contraire ... on est pas dans le cas d'une table partitionnée ici [/quote]

[orafrance]

This second method is more efficient because the table is scanned only once la seconde méthode (drop create) est plus efficace parce qu'elle scanne la table qu'un seul fois

plus efficace oui, mais pour rebuilder seulement, c'est bien durant cette opération qu'Oracle scanne la table

Pour ce qui est du COALESCE, c'est effectivement une méthode intéressante mais pas aussi efficace qu'un rebuild

REBUILD -> recréation de l'index -> utilise seulement l'espace utile -> le tablespace retrouve de l'espace libre (aucun extent en fin de tablespace ce qui permet de retailler le tablespace)

COALESCE -> Fusionne les blocs libres contigus, c'est à dire que X extents libres devient un gros extent, donc le gain est beaucoup moins intéressant. Il est nul quand à l'espace réellement récupérer puisque les extent sont toujours là et tu ne pourra pas redimensionner le tablespace.
L'inconvénient de cette méthode, c'est que si l'espace libéré entre deux espace plein n'est pas suffisant pour les lignes qui seront insérés par la suite, on va continuer de fragmenter l'espace dans le tablespace.

Dans le cas des tablespaces des data, le rebuild n'existe pas, il faut donc déplacer les tables dans un tablespace temporaire, droper/créer le tablespace d'origine et ramener les tables pour faire une défragmentation efficace.

J'espère ne pas avoir dit de bétise et avoir été assez clair

[Marc Musette]

This second method is more efficient because the table is scanned only once la seconde méthode (drop create) est plus efficace parce qu'elle scanne la table qu'un seul fois

oui lorsque la table est partitionnée ...

comment expliquerais-tu la différence de temps d'exécution de Superfly alors ?

Pour ce qui est du COALESCE, c'est effectivement une méthode intéressante mais pas aussi efficace qu'un rebuild

cela dépend il y a un tableau (16-1) dans l'administrator guide qui compare les deux méthodes

COALESCE -> Fusionne les blocs libres contigus, c'est à dire que X extents libres devient un gros extent

toujours la figure 16-1 à mon secours ... "... Before performing the operation, the first two leaf blocks are 50% full. This means you have an opportunity to reduce fragmentation and completely fill the first block, while freeing up the second" ... je ne te fais pas la traduction

il ne me semble pas qu'on change la taille des extents !? et les autres extents devenus libres gardent leur taille initiale ... je ne suis donc pas d'accord avec ton explication

[orafrance]

je te conseille de lire le sujet suivant : http://asktom.oracle.com/pls/ask/f?p...7149039425561, qui est très instructif sur le sujet, c'est là que j'ai vu cette démo :

UUUFFFUFFFFFFFFFFFFFFFFFFFFU

(u=used, f=free) as extents, coalesce would result in:


UUUF..UF...................U

with the ..... being part of one extent. that one used extent at the end will
stop you from shrinking the file.

Mais bon... c'est pas de la doc Oracle non plus

Effectivement, COALESCE permet de fusionner l'espace libre et ainsi tu peux le réutiliser plus facilement, mais ça ne récupère en aucun cas de l'espace disque... c'est une gestion de l'espace au sain du tablespace mais les extents ne sont pas supprimés. Alors que le rebuild ne génére que les extents nécessaire aux stockages et ainsi tu peux retailler le datafile pour récupérer de l'espace disque

Before performing the operation, the first two leaf blocks are 50% full. This means you have an opportunity to reduce fragmentation and completely fill the first block, while freeing up the second

Oui, tu fusionnes l'espace libre, de 2 extents à moitié plein t'en fait 1 complétement plein et un vide, tu fusionnes donc bien les espace contigus mais que d'extents en extents. Si l'extent libéré suffit à acceuillir les nouvelles données très bien mais si les lignes sont plus lourdes tu auras peut-être besoin de plus d'un extent auquel cas le COALESCE n'aura servi à rien

[Marc Musette]

comme dit précédemment je suis d'accord avec toi sur le fait que le REBUILD te permet de gagner de l'espace mais pas pour très longtemps ... (je sais tu vas me donner un exemple où c'est effectivement le cas) --> planifier globalement et automatiquement sur tous les index un REBUILD n'est pas une bonne chose ; mieux vaut cerner les index qui doivent l'être

pour ce qui est du COALESCE, les explications pour l'index et le tablespace me semble un peu différente et surtout source de confusion

voici ce que dit la référence SQL pour les index

Specify COALESCE to instruct Oracle to merge the contents of index blocks where possible to free blocks for reuse.

pour un tablespace :

For each datafile in the tablespace, this clause combines all contiguous free extents into larger contiguous extents.

il faut avouer que selon moi ce n'est pas la même chose...

de toute façon et en pseudo guise de conclusion : oublions tout cela et utilisons des locally managed tablespace ! (ou du moins n'utilisons plus pctincrease mais bien des next extent multiple de l'initial)

enrichissant cette discussion

[orafrance]

Oui, en fait il n'y a pas de mieux ou moins bien, comme d'habitude quand au choix à adopter, tout est question du contexte et des besoins

c'est sûr que le PCTINCREASE à 0 est pour moi aussi un gage de moindre soucis

EDIT : au fait... tu vas peut être me trouver énervant mais je préconise plutôt un next extent = db_block_size et initial = taille des données insérées si elle est connue ou db_block_size

Si tu utilises des extents aussi petits que la taille du block, tu auras beaucoup d'extents (donc ce sera plus couteux mais c'est négligeable si on ne fait pas de gros chargements) mais une gestion de l'espace optimisée.
L'idéal étant de mettre un next_extent = poid moyen d'une ligne + 10%

[superfly]

les gars cette discussion est des plus interessante , en faite cela deux trois postes ou je commence à pas tout comprendre
ça c'est dû a mon faible niveau en administration oracle.
Du coup je vais faire un autre post, au sujet d'un petit bouquin histoire de ne pas tout melanger...

Bonne continuation