Lors d’une des précédentes versions applicatives chez notre client l’un des développeurs nous sollicite la validation de la livraison SQL associée.
En regardant de plus près, nous avons remarqué que l’une des tables à créer disposerait d’un index associé à une PK qui sera situé sur la deuxième colonne de la table.
Au delà, de toute critique sur « l’usage connu » de mise en place d’une PK et de son index, la pertinence de l’index associé à cette PK nous amène à nous interroger sur l’impact du CLUSTERING FACTOR pour la future table.
— le CLUSTERING FACTOR–
Nous pouvons définir cette notion comme étant le Facteur de Rapprochement entre les feuilles de l’index et l’ordre des enregistrements dans la table.
A partir de cette brève description deux cas se présentent à nous:
– Premier cas:
La valeur du CLUSTERING FACTOR est proche du nombre total de blocs de la table :
Dans ce cas nous disposons des feuilles de l’index triées selon le même ordre que les données insérées dans la table.
Résultat — Via cet index, le moteur oracle parcourra un nombre de blocs minimum (moindre coût) pour balayer la totalité de la table
– Deuxième cas:
La valeur du CLUSTERING FACTOR est proche du nombre total de lignes de la table :
A l’inverse dans ce cas nous disposons d’un index dont les feuilles ne respecte pas vraiment l’ordre des données insérées dans la table
Résultat — Via cet index, le moteur oracle aura plutôt tendance à parcourir un nombre plus important de blocs (coût plus élevé).
Afin de mesurer l’impact du CLUSTERING FACTOR nous pouvons illustrer cela par l’exemple suivant :
Création d’une table:
create table table_list_display_patterns ( list_id number not null, display_pattern_id varchar(1000) not null );
Insertion des données ordonnées selon la colonne indexée:
begin
for i in 1..100 loop
insert into table_list_display_patterns select i, lpad('x',1000,'x')
from dba_objects where rownum < 35 order by 1;
end loop;
end;
/
Création de l’index sur la table « ordonnée »:
create index list_display_patterns_idx on table_list_display_patterns(list_id);
Création d’une deuxième table avec des données non-ordonnée selon l’index:
create table table_list_display_rand as select * from table_list_display_patterns order by dbms_random.random;
Création de l’index sur la table « non-ordonnées »:
create index list_display_rand_idx on table_list_display_rand(list_id);
Récolter les statistiques sur les deux tables, une opération essentielle afin de déterminer les CLUSTURING FACTOR respectifs:
exec dbms_stats.gather_table_stats('USERS', 'table_list_display_patterns', estimate_percent => 100, cascade=>TRUE);
exec dbms_stats.gather_table_stats('USERS', 'table_list_display_rand', estimate_percent => 100, cascade=>TRUE);
Détermination du CLUSTURING FACTOR pour chaque index des deux tables:
select di.index_name,di.blevel,di.leaf_blocks,di.distinct_keys,di.clustering_factor,di.num_rows, dt.blocks
from dba_indexes di, dba_tables dt where di.num_rows=dt.num_rows and index_name in ('LIST_DISPLAY_PATTERNS_IDX','LIST_DISPLAY_RAND_IDX')
order by 1;
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS BLOCKS
------------------------------ ---------- ----------- ------------- ----------------- ---------- ----------
LIST_DISPLAY_PATTERNS_IDX 1 7 100 548 3400 496
LIST_DISPLAY_RAND_IDX 1 7 100 3290 3400 502
Le CLUSTERING FACTOR pour la table TABLE_LIST_DISPLAY_PATTERNS_IDX est plus proche du nombre total de blocs de la table que celui du nombre total de lignes
On peut donc conclure que l’ordonnancement des données dans les feuilles de l’index est satisfaisant.
En effet, l’idéal est d’avoir un CLUSTERING FACTOR le plus proche possible du nombre de total de blocs d’une table.
En revanche, le CLUSTERING FACTOR pour l’index LIST_DISPLAY_RAND_IDX est quasiment égal au nombre total de lignes de la table, ce qui indique que les feuilles de l’index pointeront vers une disposition éparse des données dans la table.
L’optimiseur oracle pourrait générer un coût plus important pour la requête ou effectuer un full scan de la table en se détournant de l’utilisation de cet index.
select norm.list_id, norm.cnt normanized_blocks , random.cnt randomanized_blocks from (select list_id, count(distinct(dbms_rowid.ROWID_BLOCK_NUMBER(rowid))) cnt from TABLE_LIST_DISPLAY_PATTERNS group by list_id)norm , ( select list_id, count(distinct(dbms_rowid.ROWID_BLOCK_NUMBER(rowid))) cnt from TABLE_LIST_DISPLAY_RAND group by list_id) random where norm.list_id = random.list_id order by list_id; LIST_ID NORMANIZED_BLOCKS RANDOMANIZED_BLOCKS ---------- ----------------- ------------------- 1 5 33 2 6 33 3 6 34 4 6 32 5 6 33 6 6 32 .... 93 6 31 94 6 33 95 6 33 96 6 32 97 6 34 98 5 32 99 5 33 100 6 33
Effectivement nous remarquons que les enregistrements sont correctement groupés (CLUSTERED) alors que sur la deuxième table les enregistrements sont plutôt éparses (SCATERRED):
set autotrace traceonly explain select /*+ index(LIST_DISPLAY_PATTERNS_IDX) */count(list_id) from USERS.TABLE_LIST_DISPLAY_PATTERNS where list_id=list_id; set autotrace off EXPLAIN ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 980 | 496 (0)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 980 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TABLE_LIST_DISPLAY_PATTERNS| 3400 | 4007K| 496 (0)| 00:00:04 | |* 3 | INDEX FAST FULL SCAN | LIST_DISPLAY_PATTERNS_IDX | 3400 | | 8 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------
set autotrace traceonly explain select /*+ index(LIST_DISPLAY_RAND_IDX) */ count(list_id) from USERS.TABLE_LIST_DISPLAY_RAND; set autotrace off EXPLAIN ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 980| 3308 (0)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 980| | | | 2 | TABLE ACCESS BY INDEX ROWID| TABLE_LIST_DISPLAY_RAND | 3400 | 4007K| 3308 (0)| 00:00:04 | |* 3 | INDEX FAST FULL SCAN | LIST_DISPLAY_RAND_IDX | 3400 | | 8 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------
Conclusion:
L’impact d’un mauvais CLUSTERING FACTOR se révèle donc essentiel sur les performances en se traduisant par un coût plus important, conduisant l’optimiseur oracle à préférer (à juste titre) un scan de table, si cela et combiner avec une sélectivité de l’index et/ou des critères de prédicats faible.
Les mauvaises performances sont autant valables pour les SELECT avec TRI que pour les INSERT.
Trouver une méthode afin d’améliorer le CLUSTURING FACTOR n’est pas toujours chose aisée :
– En version 11g (ou inférieur), il est toujours possible d’essayer une réorganisation de la table avec reconstruction de son index en espérant un bon résultat.
– Plus sympas, avec l’arrivée de la 12c la fonctionnalité « ATTRIBUTE CLUSTERING » permet d’ordonner les données dans des zones physiques proches en fonction du contenu d’une colonne spécifique de la table
Remarques:
– Comme attendu le « REBUILD » d’index n’apporte pas d’amélioration au CLUSTERING FACTOR
– Le CLUSTERING FACTOR peut être inférieur à la valeur du nombre total de blocs de la table si cette dernière contient des blocs vides se situant sous le HWM
et/ou combiné à cela beaucoup de valeur NULL pour la colonne indexée
– Le CLUSTERING FACTOR ne peut être supérieur au nombre total des lignes de la table.
