Rebuild index does definitely not improve clustering factor

A mes débuts dans Oracle, je lisais plein de conneries sur l'optimisation.
Parmi les gros mythes, il y avait : "rebuild un index améliore le clustering factor".

C'est bien entendu absurde :
- Le clustering factor se calcule comme le nombre de changement de bloc sur les lignes de la table que tu effectues en parcourant séquentiellement les feuilles de l'index.
- Les feuilles sont triées par valeur de la clef, donc le fait de le recontruire ne change pas l'ordre des feuilles

Puis je me disais : peut-être que pour les valeurs de clef ex-aequo l'ordre d'insertion pourrait augmenter artificiellement le nombre de changement de blocs, permettant ainsi au rebuild de faire décroître le clustering factor.

Essayons !

CREATE TABLE t (i NUMBER, j NUMBER);

CREATE INDEX idxt ON t(i);

INSERT INTO t
SELECT level, level
FROM DUAL
CONNECT BY level <= 1000;

commit;

SELECT dbms_rowid.rowid_block_number(rowid) blk, count(*) cnt, min(i) mini, max(i) maxi
FROM t
GROUP BY dbms_rowid.rowid_block_number(rowid);

       BLK        CNT       MINI       MAXI
---------- ---------- ---------- ----------
   1452237        574          1        574
   1452238        426        575       1000

De i = 1 à 574, on est dans le premier bloc. Les autres sont dans le deuxième bloc.

Un petit coup de stats...

exec dbms_stats.gather_table_stats(null, 'T', cascade => true)

SELECT clustering_factor
FROM dba_indexes
WHERE index_name = 'IDXT';

CLUSTERING_FACTOR
-----------------
                2

... et ça fait bien 2, qui correspond au passage de i=574 à 575 où on passe au deuxième bloc.

Maintenant, on va séquentiellement updater i = 1 dans un bloc, puis dans l'autre.

UPDATE t
SET i = 1
WHERE i = 575;

commit;

exec dbms_stats.gather_table_stats(null, 'T', cascade => true)

SELECT clustering_factor
FROM dba_indexes
WHERE index_name = 'IDXT';

CLUSTERING_FACTOR
-----------------
                4
 

=> Lors du parcours de l'index, pour i = 1, on switche sur le deuxième bloc, puis on revient au premier bloc pour i = 2 : ça augmente donc le clustering factor de 2
Jusque là, pas de surprise :)

UPDATE t
SET i = 1
WHERE i = 2;

commit;

exec dbms_stats.gather_table_stats(null, 'T', cascade => true)

SELECT clustering_factor
FROM dba_indexes
WHERE index_name = 'IDXT';

CLUSTERING_FACTOR
-----------------
                4

Cette fois, je me serais dit que j'insère une feuille i = 1 après celles qui existent, et donc je devrais ajouter 2 au clustering factor... mais non !

SELECT /*+INDEX(t idxt)*/ i, dbms_rowid.rowid_block_number(rowid)
FROM t
WHERE i = 1;

        I DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
--------- ------------------------------------
        1                              1452237
        1                              1452237
        1                              1452238
        1                              1452238

En parcourant l'index, on voit (même si cette requête n'est pas vraiment une preuve fiable) que les blocs sont bien lus "ensemble", et non pèle-mêle !

Ah ben ouais, les feuilles sont triées sur le "couple" (clefs, rowid) !

http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#i5671

Leaf Blocks
All leaf blocks are at the same depth from the root branch block. Leaf blocks store the following:
    *      The complete key value for every row
    *      ROWIDs of the table rows
All key and ROWID pairs are linked to their left and right siblings. They are sorted by (key, ROWID).

La vie est belle... Bonne dinde à tous !