Research on clustering factors of column values in Oracle

Column value selection, Set cluster, and column chart
Remember, the optimizer understands many important features of column data in a table. The most notable is the column value selectivity and the clustering factor of the column.
For example, we can see a query that uses column values to form a result set:
Select
Customer_name
From
Customer
Where
Customer_state = 'randisland ';
In this example, whether to use the index or full table scan is affected by the proportion of customers in the same region. If the proportion of customers in the island is very small and the number set is in the data block, the index scan may be the fastest execution plan for this query.
Many Oracle developers are confused when they only search for a small number of row optimizers to select full table scans, and are not aware that the optimizer considers the set clusters of column values in the table.
Oracle provides a clustering_factor column in The dba_indexes view to notify the optimizer about table row and index synchronization. When the set clustering factor is close to the number of data blocks, the table's rows are synchronized with the index.
The column value selection, db_block_size, avg_row_len, and set base all work collaboratively. It helps the optimizer decide whether to use an index or full table scan. If the data column has a high selectivity and a low clustering_factor, index scanning is usually the fastest way to execute it (see the figure ).

If most SQL statements reference columns with high clustering_factor, large db_block_size, and small avg_row_len, DBA sometimes sorts rows cyclically or uses a single table set cluster to maintain the order of rows. In this way, all adjacent rows are placed in the same data block, eliminating full table scanning and increasing the query speed by up to 30 times.
On the contrary, the value of high clustering_factor reaches the number of rows in the table (num_rows), indicating that the order of these rows is different from the order in the index, and the index range scanning requires additional I/O. Because clustering_factor reaches the number of rows in the table, these rows are not synchronized with the index.

However, even if the column is highly selective, the high clustering_factor and the small avg_row_len indicate that the column values are randomly distributed in the table, and obtaining these rows requires additional I/O. In this case, index range scanning will cause a large number of unnecessary I/O (see the figure); Full table scanning will be much more efficient.

All in all, clustering_factor, db_block_size, and avg_row_len all affect the optimizer's decision to perform full table or index range scans. It is important to understand how the optimizer uses these statistics.

We have noticed that each new version of optimizer has been improved, and the latest enhancements provided by Oracle Database 10g will consider the external impact when determining the execution plan. Oracle calls this feature external costing and includes an evaluation of CPU and I/O costs.

1 2 3 Next Page