Le Clustering factor : j'ai bien compris?

Version imprimable

23/02/2016, 15h50
Ikebukuro

Le Clustering factor : j'ai bien compris?

Bonjour,

Je pensais avoir compris comment fonctionnait le Clustering Factor en lisant plusieurs sites et voila que j'ai un doute en regardant ma base de production.

J'ai une table de nom CABN_CURRENT_ACCDATE_SECBAL avec 1 992 109 enregistrements.

TOAD me donne un avg_row_len de 79 dans l'onglet des stats, un paramètre Size de 168 Mb (taille de la table sur disque dur?) et un paramètre Blocks de 21 248 (mon db_block_size est de 8Ko).

Cette table a une PK sur le champ CAB_ID NUMBER(12) et dessus un index appelé PK_CABN.

Pour cet index Toad me donne les infos suivantes :
- Leaf blocks : 4 681
- Distinct keys : 1 992 038
- Clustering facor : 1 552 312

Ce clustering factor est très élevé par rapport au nombre de feuilles de mon index.

Ce que je comprends c'est que si je devais parcourir en entier l'index pour récupérer chaque ligne de ma table, Oracle devrait effectuer 1 552 312 lecture (donc lire 1 552 312 blocs de données?) pour lire mes 1 992 109 enregistrements. C'est là que je doute car le nombre de blocs à lire serait largement supérieur au nombre de blocs de ma table : 21 248 blocs :aie:

Ce que je comprends c'est que Oracle va lire N fois le même bloc mais pas au même instant pour récupérer les données car un Clustering factor élevé signifie que la table n'est pas ordonnée comme l'index. Donc dans mes blocs d'index j'ai à la suite "que" des ROWID correspondant à des blocs de données différents donc pour un enregistrement dans mon index j'ai une lecture d'un bloc de données.

Désolé si ce n'est pas très clair, c'est pour cela que je pose la question ;)

Etes-vous d'accord avec mon interprétation ou bien je me suis fourvoyé?

Autre question : dans quels cas on a un Clustering Factor = au nombre de feuilles dans l'index? Si j'ai bien compris c'est l'objectif d'un bon CF : être proche du nombre de feuilles.
Par exemple j'ai une table avec un champ et un seul qui est un ID et que je créé un index sur ce champ en respectant l'ordre de la table?
23/02/2016, 19h23
Pomalaix

Le facteur de regroupement d'un index quantifie le degré de désordre des lignes de la table vis à vis de cet index.
Cet indicateur permettra à l'optimiseur de juger s'il est pertinent de recourir à cet index lorsqu'il s'agit de ramener potentiellement plusieurs lignes (INDEX RANGE SCAN).

Schématiquement, cette valeur est calculée en déterminant combien de fois on doit changer de bloc en parcourant la table complète lorsqu'on essaye de lire ses lignes dans l'ordre fourni par l'index.

Dans le cas le plus favorable, les données sont rangées dans la table dans le même ordre que dans l'index. On va donc simplement parcourir les blocs de la table successivement, sans jamais revenir à un bloc déjà lu. Le facteur de regroupement est donc égal au nombre de blocs de la table.

Dans le cas le plus défavorable, on doit changer de bloc à chaque lecture d'une nouvelle ligne, avec des passages plus ou moins nombreux dans des blocs déjà parcourus. Le facteur de regroupement est donc égal au nombre de lignes de la table.
Si on a une table de 2 blocs, avec 100 lignes par bloc, on pourrait donc avoir un facteur de 200.

Cet algorithme naïf a tendance à ignorer le bénéfice du cache. La relecture d'un bloc n'est peut-être pas si désastreuse, si le bloc dans lequel on repasse est encore en cache.
C'est pourquoi il existe un paramètre non documenté qui permet à cet algorithme de mieux estimer le nombre de blocs différents qu'il devra parcourir.

Le facteur de regroupement n'a pas "d'objectif" ; il se contente de faire le constat du degré de désordre de la table vis à vis de l'index.
Si par exemple les données d'une table sont triées selon la clé primaire (cas d'un IOT), elles seront forcément plus ou moins en vrac relativement aux autres index.
On peut donc éventuellement favoriser un index, (grâce au partitionnement par exemple, ou en recréant une table statique en triant ses données selon un certain critère), mais si le facteur de l'un est amélioré, il sera immanquablement dégradé pour les autres.
En effet, si je range mes livres en les classant par ordre alphabétique d'auteur, ils ne pourront pas être simultanément classés par date de parution, tous livres confondus.
Il faut choisir le critère que l'on veut favoriser.
23/02/2016, 20h09
Ikebukuro

Merci pour ta réponse Pomalaix !

Je note que :
- le CF d'un index sur la table T1 va, du mieux, au nombre de blocs de la table T1, au pire, au nombre de rows dans la table T1 répertoriés dans l'index
- les blocs de la table T1 peuvent être lus plusieurs fois lors du parcours d'un index
- j'avais compris que si on a un CF excellent sur un index, il sera plus mauvais pour un autre index puisqu'une table, comme tu le rappelles, ne peut être triée complètement que sur une colonne

Petite précision, pour voir si j'ai bien compris : on va prendre le cas d'un index sur une colonne.
- si la colonne indexée est NOT NULL, le nombre de rows de l'index = nb rows de la table
- au CF le mieux on parcourt une seule fois chaque bloc de la table, au CF le pire on parcourt un nb de blocs de la table = nb de rows de la table
- si la colonne indexée est NULLABLE alors le nb de rows de l'index <= nb rows de la table
- au CF le mieux on parcourt AU PLUS le nb de blocs de la table, au CF le pire on parcourt AU PLUS un nb de blocs de la table = nb de rows de la table

Un gros merci pour les infos.
24/02/2016, 09h46
mnitu

Citation:

Envoyé par Ikebukuro

...
j'avais compris que si on a un CF excellent sur un index, il sera plus mauvais pour un autre index puisqu'une table, comme tu le rappelles, ne peut être triée complètement que sur une colonne
....

En théorie oui. Mais en pratique les colonnes parfois ne sont pas vraiment indépendantes c'est-à-dire que parfois les données contenues dans disons deux colonnes sont corrélées. Dans ces cas deux indexes sur ces colonnes peuvent avoir tous les deux des bonnes CF.
Ensuite il ne pas de tout nécessaire qu'un index à son meilleur CF pour être utile.
Et, de mémoire, l'algorithme de calcul du CF a été revu (amélioré) en 12c.
24/02/2016, 12h48
Ikebukuro

Citation:

Envoyé par mnitu

Et, de mémoire, l'algorithme de calcul du CF a été revu (amélioré) en 12c.

Et on peut trouver des infos là-dessus ou bien c'est juste Oracle qui le mentionne dans son argumentaire de vente de la 12C mais n'explique pas plus ce qu'il a amélioré?