A classic Oracle question!

Multiblock read is a feature in Oracle that can occur when there are multiple blocks on disk that need to be read and processed by the database. It’s not necessarily related to index scans, but rather to the physical layout of data on disk.

In your original example, the table DISTRICT was clustered on the first column (D_ID) which caused a multiblock read. This is because the data in that table was stored contiguously on disk, making it faster to access and scan the entire block.

When you un-clustered the DISTRICT table, the physical layout of the data changed. The rows are now scattered across multiple blocks on disk, which causes a different query plan to be chosen. In this case, the multiblock read is avoided because the optimizer can determine that it’s not necessary to scan an entire block of data.

To summarize:

• Multiblock read occurs when there are multiple physical blocks on disk that need to be read and processed.
• Index scans do not typically cause multiblock reads, as they usually involve a sequential scan of a single block of data.
• The physical layout of the table (e.g., clustering or indexing) can affect the choice of query plan and whether multiblock read occurs.

In your updated example, the multiblock read has indeed been avoided after un-clustering the DISTRICT table.

Last modified on 2024-07-16