Oracle Clustering factor)

Clustering factor is one of the parameters used to calculate cost in the CBO optimizer mode in Oracle statistics. It determines whether the current SQL statement is indexed, or full table scan and nested External table connection. In this case, what is a clustering factor? In those cases, it will affect the clustering factor and how to improve the clustering factor? This article will describe this.

1. Storage of heap tables

The most common database system in Oralce is the heap table.

The data storage mode of the heap table is unordered, that is, any DML operation may make the current data block available free space.

In consideration of space saving, the available free space on the block will be filled by the newly inserted rows instead of the last block to be used.

The above operations lead to the generation of data with no sequence.

When an index is created, the index block is filled according to the specified columns in order. By default, the index is in ascending order.

When creating or recreating an index, the order of the index column is ordered, and the order of the table is unordered, that is, there is a difference, that is, the result is the clustering factor.

2. What is a clustering factor (clustering factor/CF)

Clustering factor is based on the value in the index column of the table. Each index has a clustering factor.

It is used to describe the degree of similarity between the index block and the data stored on the table block, that is, whether the storage order of the data rows on the table is consistent with that on the index column.

In a full index scan, the CF value is basically the same as the number of physical I/O or block shards. If the same block is read consecutively, Oracle considers that only one physical I/O is required.

A good CF value is close to the number of blocks in the table, while a poor CF value is close to the number of rows in the table.

When creating an index, clustering factors are obtained through the calculation of existing rows and index blocks in the table.

3. How Does Oracle calculate clustering factors?

Perform or estimate a full index scan.

Check the value of each rowid on the index block and check whether the value of the previous rowid is the same as that of the last data block, if it points to different data blocks, the value of CF increases by 1.

When each rowid on the index block is checked, the final CF value is obtained.

4. Clustering factor Diagram

A. Good indexes and clustering Factors

B. Good indexes and poor clustering Factors

C. Poor indexing and poor clustering Factors

1 2 3 4 Next Page