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

This article introduces the knowledge of "how to understand the Oracle cluster factor". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Concept

The cluster factor (ClusteringFactor) is the number of blocks of table that need to be accessed if a table is scanned through an index. Measure how big the physical read is when the table is scanned through the index and returned to the table through ROWID.

The cluster factor is a measure of ordering derived from the comparison of the index with the table on which it is based, which is used to check the cost of table lookups performed after index access (the cost of the operation can be obtained by multiplying the cluster factor by selectivity). The cluster factor records the number of blocks that will be read when scanning the index.

If you are using an index with a large clustering factor, you must access more table data blocks to get rows in each index block (because adjacent rows are in different blocks).

If the cluster factor is close to the number of blocks in the table, the index is properly sorted; however, if the cluster factor is close to the number of rows in the table, the index is not sorted properly

two。 Calculation method of Cluster Factor

(1) scan an index

(2) compare the ROWID of a row with the ROWID of the previous row. If the two ROWID do not belong to the same data block, then the ClusteringFactor increases by 1.

(3) after the entire index is scanned, the ClusteringFactor of the index is obtained.

3. Meaning

If the ClusteringFactor is close to the number of blocks stored in the table, the table is stored in the order of the index fields.

If the cluster factor is close to the number of rows, the table is not stored in the order of index fields.

The cluster factor is useful when calculating index access costs. Clustering Factor multiplied by the optional parameter (selectivity) is the cost of accessing the index.

If this statistic does not reflect the reality of the index, it may cause the optimizer to choose the execution plan incorrectly. In addition, if most of the accesses on a table are scanned by an index, reorganizing the data of the table in the order of the index fields can improve the access performance of the table.

The cluster factor has an impact on the SQL statements that perform the range scan. If the cluster factor is close to the number of data blocks, the number of data blocks that meet the query requirements can be much less, which also increases the possibility that the data blocks already exist in memory. Compared with the cluster factor with a much larger number of blocks, the range query based on index columns needs to scan more blocks.

We know that the corresponding block number of the record can be found through dbms_rowid.rowid_block_number (rowid). Rowid is recorded in the index, so oracle can determine whether the record is in the same block based on the rowid in the index.

For example, if there are five records in the index, first compare whether the block b is in the same block, if not in the same block, then Clustering Factor+1, and then continue to compare the same theory with the bjournal c, and if the Clustering Factor+1 is not in the same block, go on like this until all the records are compared.

According to the algorithm, we can know that the value of clustering factor is between the number of block and the number of table rows. If the clustering factor is close to the block number, it means that the storage of the table is sorted close to the index storage, that is, the records in the table are very orderly, so that when you do index range scan, you can read a small amount of data block to get the data we want, at a relatively low cost. If the clustering factor is close to the number of table records, the storage and index sorting of the table are very different, and multiple block will be read when doing index range scan, because the table records are scattered and the cost is high.

4. Related table

The Clustering_factor column is a column in the user_indexes,dba_indexes view that reflects whether the data appears orderly relative to the indexed column.

SQL > select index_name,CLUSTERING_FACTOR from user_indexes

Experimental reference

Https://blog.csdn.net/zhaoyangjian724/article/details/71082379

This is the end of "how to understand the Oracle Cluster Factor". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.