Database Reference
In-Depth Information
THEN
l_clustering_factor := l_clustering_factor + 1;
END IF;
l_previous_block_nr := l_block_nr;
l_previous_file_nr := l_file_nr;
END LOOP;
CLOSE l_cursor;
RETURN l_clustering_factor;
END;
Notice how the values it generates match the statistics stored in the data dictionary:
SQL> SELECT i.index_name, i.clustering_factor,
2 clustering_factor(user, i.table_name, ic.column_name) AS my_clus_fact
3 FROM user_indexes i, user_ind_columns ic
4 WHERE i.table_name = 'T'
5 AND i.index_name = ic.index_name
6 ORDER BY i.index_name;
INDEX_NAME CLUSTERING_FACTOR MY_CLUS_FACT
---------- ----------------- ------------
T_PK 990 990
T_VAL_I 77 77
the CLUSterING FaCtOr COMpUtatION IS peSSIMIStIC
the algorithm used by the
dbms_stats
package for computing the clustering factor is rather pessimistic.
In fact, the algorithm considers that during an index range scan, only the block referenced by the previous index
key stays in the cache. In practice, several of the previous blocks likely remain in the cache. as a result, it's not
uncommon that the clustering factor doesn't accurately describe the real data distribution.
to prevent or solve problems related to this pessimistic computation, as of version 11.2.0.4 (or when a patch
implementing the enhancement associated to bug 13262857 is installed) the
table_cached_blocks
preference
can be set through the
dbms_stats
package. Values between 1 and 255 specify how many blocks are expected
to be cached. the default value is 1. even though advising a value is always very difficult, it's likely that all values
larger than 1 lead to more reliable clustering factors. In fact, the default value is way too pessimistic. In addition,
the value
dbms_stats.auto_table_cached_blocks
specifies to use either 255, 1% of the table blocks or 0.1%
of the buffer cache, whichever is less.
From a performance point of view, you should avoid row-based processing. As discussed previously in this
chapter, the database engine, thanks to row prefetching, also tries to avoid it as much as possible. In fact, when
several rows have to be extracted from the same block, instead of accessing the block (that is, doing a logical read)
once per row, all rows are extracted in a single access. To emphasize this point, let's look at an example based on the
clustering_factor.sql
script. The following is the test table created by the script:
SQL> CREATE TABLE t (
2 id NUMBER,
3 val NUMBER,
