Influence of Oracle clustering factor on query Io

In Oracle, for the same query statement, it is sometimes completed quickly, but sometimes very slow, but the table structure is completely consistent, and the data in the table is also completely consistent. What is the specific reason, let's start with the details in the index.

In a special view in Oracle, user_indexes has a special column named clustering_factor. The value indicates how many database Io operations will occur if the whole table data is accessed. We can use the following SQL statement to view the details.

Select
A. index_name,
B. num_rows,
B. blocks,
A. clustering_factor
From
User_indexes,
User_tables B
Where
A. index_name =?
And a. table_name = B. table_name

In this SQL statement ,? The name of the index to be retrieved. The data in the table is sometimes unordered. clustering_factor is close to num_rows at this time, which means that if you scan the entire table, you must read the rowid of the corresponding row based on the index each time, at this time, there are many I/O operations, and the natural retrieval time will be relatively long. If the data is ordered, clustering_factor is close to blocks, indicating that the adjacent data is in a block, which reduces the number of Io operations and greatly reduces the natural retrieval time.

 

The following section describes clustering_factor in the Oracle manual:

Indicates the amount of Order of the rows in the table based on the values of the index.

• If the value is near the number of blocks, then the table is very well ordered. in this case, the index entries in a single leaf block tend to point to rows in the same data blocks.

• If the value is near the number of rows, then the table is very randomly ordered. in this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.