Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: The Index Clustering Factor Explained
> in the example below there are two exceptions
The difference is caused by the NULL values not present in the index. I added a WHERE clause and now everything goes fine...
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 23047 ADR_STREET 232248 232248 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 code...
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_where VARCHAR2(1000); 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;
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)
l_order_by := c.column_name; l_where := c.column_name || ' IS NOT NULL '; ELSE l_order_by := l_order_by || ',' || c.column_name; l_where := l_where || ' OR ' || c.column_name || ' IS NOT NULL';
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||' WHERE '||l_where||' 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 Thu May 13 2004 - 03:01:39 CDT