Re: The Index Clustering Factor Explained

From: Chris <christian.antognini_at_trivadis.com>
Date: 11 May 2004 15:20:02 -0700
Message-ID: <c283720b.0405111420.7d02f52@posting.google.com>

Hi Howard

I didn't have time to read your paper... anyway 4 years ago I wrote a small PL/SQL function that computes the clustering factor for a index (I wrote it when I was trying to understand what the clustering factor is...).

I just started it on 10g and it seams to work... i.e. the correct clustering factor is "predicted" for "normal" indexes (in the example below there are two exceptions... I didn't research why yet). It shows very well that it is really not important how the data is stored in the index!

Here a typical output (I gathered the stats to be sure that they are up-to-date...):

SQL> exec dbms_stats.gather_schema_stats('optw',cascade=>true)

PL/SQL procedure successfully completed.

SQL> SELECT index_name, clustering_factor,   2 clustering_factor(owner,index_name) my_clustering_factor   3 FROM dba_indexes
  4 WHERE index_type = 'NORMAL'
  5 AND owner = 'OPTW'
  6 AND pct_direct_access IS NULL -- remove IOT   7 AND partitioned = 'NO';

INDEX_NAME                     CLUSTERING_FACTOR MY_CLUSTERING_FACTOR
------------------------------ ----------------- --------------------
ADR_PRS_ID                                250232               250232
ADR_CITY                                   23047                50600
ADR_STREET                                232248               239911
ADR_CITY$ZIP$STREET                        50600                50600
ADR_ATP_ID                                  6137                 6137
ADR_PK                                    254918               254918
ADR_CTR_CODE                                7980                 7980
ATP_LABEL                                      1                    1
ATP_PK                                         1                    1
FOA_PK                                         1                    1
MLG_PK                                         1                    1
PRS_STATUS                                  1982                 1982
PRS_LASTNAME                              152390               152390
PRS_PK                                     19279                19279
RCT_REC_ID                                   774                  774
RCT_PK                                       434                  434
REC_MLG_ID                                  1585                 1585
REC_PK                                      4585                 4585
REC_ADR_ID$MLG_ID                         403164               403164

And here the function:

CREATE OR REPLACE FUNCTION clustering_factor(   p_index_owner IN VARCHAR2, p_index_name IN VARCHAR2) RETURN NUMBER AS
  TYPE t_ref_cursor IS REF CURSOR;

  l_table_owner        VARCHAR2(30);
  l_table_name         VARCHAR2(30);
  l_order_by           VARCHAR2(1000);
  l_clustering_factor  BINARY_INTEGER := 0;
  l_bnr                BINARY_INTEGER;
  l_fnr                BINARY_INTEGER;
  l_previous_bnr       BINARY_INTEGER := 0;
  l_previous_fnr       BINARY_INTEGER := 0;
  l_cursor             t_ref_cursor;

BEGIN
  SELECT table_owner, table_name
  INTO l_table_owner, l_table_name
  FROM all_indexes
  WHERE owner = p_index_owner AND index_name = p_index_name;

  FOR c IN (SELECT column_name, column_position

            FROM all_ind_columns
            WHERE index_owner = p_index_owner AND index_name =
p_index_name
            ORDER BY column_position)

  LOOP
    IF c.column_position = 1
    THEN
      l_order_by := c.column_name;
    ELSE
      l_order_by := l_order_by || ',' || c.column_name;     END IF;
  END LOOP;      OPEN l_cursor FOR
'SELECT dbms_rowid.rowid_block_number(rowid), '||
' dbms_rowid.rowid_to_absolute_fno(rowid,
'''||l_table_owner||''','''||l_table_name||''') '||

'FROM '||l_table_owner||'.'||l_table_name||' ORDER BY
'||l_order_by;   

  LOOP
    FETCH l_cursor INTO l_bnr, l_fnr;
    EXIT WHEN l_cursor%NOTFOUND;     

    IF (l_previous_bnr <> l_bnr OR l_previous_fnr <> l_fnr)     THEN
      l_clustering_factor := l_clustering_factor + 1;     END IF;          l_previous_bnr := l_bnr;
    l_previous_fnr := l_fnr;
  END LOOP;      CLOSE l_cursor;   

  RETURN l_clustering_factor;
END; Chris Received on Tue May 11 2004 - 17:20:02 CDT