OracleSQL programming and optimization: notes on clustering Factors

The clustering factor can be viewed as the number of logical I/O operations performed on the table when the entire table is read through the index. That is to say, clustering factors indicate the order of the table to the index itself. When limit L

The clustering factor can be viewed as the number of logical I/O operations performed on the table when the entire table is read through the index. That is to say, clustering factors indicate the order of the table to the index itself. When limit L

1. Clustering factor)

The storage order of the index row is similar to that of the row in the table.

When the similarity is high, these data rows will be stored in a relatively small number of data blocks, which is a good condition for clustering factors. 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 entire table data is accessed.

A: If this value is close to the number of blocks, it indicates that the table is quite ordered and has A good structure. In this case, the index entries in the same leaf block may point to rows in the same data block. (Blotering_factor = blocks)

B: If the value is close to the number of rows, the order of the table may be very random. In this case, the index entries on the same leaf block are unlikely to point to rows on the same data block. (Clustering_factor = num_rows is not good)

The clustering factor can be viewed as the number of logical I/O operations performed on the table when the entire table is read through the index. That is to say, clustering factors indicate the order of the table to the index itself. When oracle performs a range scan on the index structure, if it finds that the next row in the index is on the same data block as the previous row, no more I/O will be executed to obtain the table block from the buffer cache. It already has a table block handle. You only need to use it directly. However, if the next row is not on the same block, the current block is released, and another physical I/O is executed to store the next block to be processed in the buffer cache.

Remember: for a table, only one index can have a suitable clustering factor! The rows in the table may be sorted in one way.

We can use the following SQL statement to view the details.

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.

2. Prepare lab conditions

Code: