Clustering Factor

Il clustering factor è la misura di come sono allineati i dati di una tabella in relazione al proprio indice. Inolte ci fornisce l’indicazione della quantità di I/O per leggere la tabella attraverso un full table scan.
Un valore di clustering factor vicino ai numeri di blocchi della tabella ci indica che i dati sono stati ordinati e scritti nei blocchi in modo ottimale in relazione all’indice.
Tuttavia nei casi dove il clustering factor si allontana notevolmente dai blocchi della tabella si avvicina al totale delle righe della tabella questo significa che Oracle ha bisogno di andarsi a leggere lo stesso blocco molte volte per ottenere un valore.
In questo ultimo caso l’indice perderà la sua efficenza incrementando il costo dello stesso per il suo utilizzo, rendendolo meno appetibile al CBO.
Il valore del clustering factor non subisce variazione durante la rebuid dell’indice visto che durante questa operazione non è possibile cambiare l’ordine dei dati.
Tuttavia esiste un’operazione di rebuild con clausola NOREVERSE/REVERSE che ne cambia l’ordine quindi in questo caso il valore del clustering factor viene modificato.
Adesso facciamo un esempio analizzo una tabella per verificare sia i blocchi sia il clustering factor (CF).

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ABE',TABNAME=>'ABC',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL);


select blocks from user_tables where table_name='ABC';
BLOCKS                 

---------------------- 

11123    



select clustering_factor from user_indexes where index_name='ABC_1';

CLUSTERING_FACTOR      

---------------------- 

121360  

Dove abbiamo un indice su questa tabella ABC_1 per i campi A,B,C.

Come si può notare questo è un pessimo esempio di CF essendo il valore molto lontano dai blocchi.

Rifacciamo lo stesso esempio creando la stessa tabella ma avendo un occhio di riguardo all’indice.

create table ABC_TEST as select * from ABC  order by A, B, C;

CREATE UNIQUE INDEX ABC_TEST_1 ON ABC_TEST( "A", "B", "C"  );

Ricalcoliamo le statistiche.

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ABE',TABNAME=>'ABC_TEST',CASCADE>TRUE,ESTIMATE_PERCENT=>NULL); 

select blocks from user_tables where table_name='ABC_TEST';

BLOCKS                 
---------------------- 
11189 

select clustering_factor from user_indexes where index_name='ABC_TEST_1';

CLUSTERING_FACTOR      
---------------------- 
11074  

In questo caso abbiamo un buon CF essendo molto vicino ai blocchi, il CBO attribuirà un costo basso all’utilizzo di questo indice in modo table da essere molto appetibile al CBO e quindi al suo utilizzo.

Nella maggior parti dei casi però non è così facile l’ottimizzazione del CF soprattutto se ci troviamo di fronte a sistemi di produzione con grandi mole di dati, e soprattutto con più indici per la stessa tabella, perchè l’aggiunta di un altro indice ci troveremo di fronte ad una situazione del genere:

CREATE UNIQUE ABC_TEST_2 ON ABC_TEST ( "D", "E", "F"  );

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ABE',TABNAME=>'ABC_TEST',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL);

select blocks from user_tables where table_name='ABC_TEST';

BLOCKS                 
---------------------- 
11189 

select clustering_factor from user_indexes where index_name='ABC_TEST_2';

CLUSTERING_FACTOR      
---------------------- 
139274 

Arghhhh...siamo ritornati con un CF molto alto per l'indice appena creato, la complessità della problematica sta nella maggior parte dei sistemi ERP dove esistono moltissime tabelle, con numerose colonne e di conseguenza indici, quindi ci sono molte variabili da aggiungere.
Anche se in questo esempio provassimo ad eseguire un rebuild con option NOREVERSE/REVERSE non avremo i miglioramenti desiderati:

alter index ABC_TEST_2 rebuild reverse;


EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ABE',TABNAME=>'ABC_TEST',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL);

select clustering_factor from user_indexes where index_name='ABC_TEST_2';


CLUSTERING_FACTOR      
---------------------- 
139274  

Normalmente le righe che crescono ad ogni inserimento in modo monotòno sono inserite nell’ordine della colonna di inserimento se si parla di tablespace MSSM, il discorso cambia invece per le tabelle su tablespace in ASSM dove per freelist o gruppi di freelist multipli, le insert vengono eseguite in modo concorrente per transazione , potrebbero essere dirottate in blocchi differenti.
In questi casi anche se i dati sono ben scritti nei blocchi potrebbero avere un CF non ottimale per il modo in cui viene calcolato dal CBO.

Conclusioni:

Il Fattore di clustering di un indice viene ottimizzato quando i dati vengono inseriti per l'ordine del proprio indice.
Altri importanti fattori che vanno ad influire il clustering factor sono:

• l'ordine delle colonne nell'indice
• Reverse Indexes
• Freelist e/o Freelist Group
• ASSM

Alberto