Database Reference
In-Depth Information
Figure 13-12.
Relationship between index blocks and data blocks
The following PL/SQL function, which is available in the
clustering_factor.sql
script, illustrates how it's
computed. Note that this function works only for single-column B-tree indexes:
CREATE OR REPLACE FUNCTION clustering_factor (
p_owner IN VARCHAR2,
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2
) RETURN NUMBER IS
l_cursor SYS_REFCURSOR;
l_clustering_factor BINARY_INTEGER := 0;
l_block_nr BINARY_INTEGER := 0;
l_previous_block_nr BINARY_INTEGER := 0;
l_file_nr BINARY_INTEGER := 0;
l_previous_file_nr BINARY_INTEGER := 0;
BEGIN
OPEN l_cursor FOR
'SELECT dbms_rowid.rowid_block_number(rowid) block_nr, '||
' dbms_rowid.rowid_to_absolute_fno(rowid, '''||
p_owner||''','''||
p_table_name||''') file_nr '||
'FROM '||p_owner||'.'||p_table_name||' '||
'WHERE '||p_column_name||' IS NOT NULL '||
'ORDER BY ' || p_column_name ||', rowid';
LOOP
FETCH l_cursor INTO l_block_nr, l_file_nr;
EXIT WHEN l_cursor%NOTFOUND;
