Shulou(Shulou.com)05/31 Report--

This article mainly introduces "What is Clustering Factor". In daily operation, I believe many people have doubts about what Clustering Factor is. Xiaobian consulted all kinds of materials and sorted out simple and easy operation methods. I hope to help you answer the doubts of "What is Clustering Factor"! Next, please follow the small series to learn together!

What is a clustering factor?

Clustering Factor (CF), which means the clustering degree of an indexed table, is a measure used to compare the degree of order of an index with the degree of clutter of a table.

The cluster factor is used to determine the number of physical I/O consumed by indexing back to the table.

CF is the calculated number of table scans required to accompany an index scan, assuming that memory size can only load one block.

More precisely, it indicates the number of times the values representing bits 1 - 15 of the block number along the leaf block of the index are replaced after comparing them with the previous ROWID.

For example:

With an index of five blocks and a table of five blocks, and four rows in one block, the total number of rows is 20.

Sequential scanning of the index while reading the corresponding table, there may be two extreme cases:

1. CF is the lowest

If an index block contains ROWIDs that are contained in a table block, then when scanning the table through the index, only scan the index 5 times and the table 5 times, you can get the desired data.

CF=5 (the number of scans of the table block), and the minimum value of CF is the same as the number of table blocks

2. CF at its highest

If the ROWIDs contained in an index block are contained in different table blocks, then when scanning the table through the index,

It is necessary to scan 20 times [4(number of index blocks)+4 (number of index blocks) = 20 times] to obtain the desired data.

CF=20 (number of scans of the table block), CF maximum value is the same as the number of rows of the table

II. Calculation process of clustering factor

The approximate order of calculation of clustering factors is as follows:

1. Perform an index full scan

2. Check the rowid information in the index block. Compare whether the rowid points to the same data block as the previous rowid. If different, the clustering factor is increased by 1.

3. When the entire index scan is completed, the cluster factor value of the index is obtained.

calculation process

SELECT *FROM (SELECT ID ,ROWID FROM TMP_CF ORDER BY ID)WHERE ROWNUM set autot traceonly/* Executes the query if ID select * from tmp_cf where id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.