Hello Experts,
I have been reading Troubleshooting Oracle Performance by Christian Antognini for a while. In the part of optimizing joins, I came across a term that called "Block Prefetching". It is written the following thing about it.
"To improve the efficiency of nested loop joins, the database engine is able to take advantage of block prefetching. The goal of this optimization technique is to substitute several single-block physical reads performed on adjacent blocks, with one multiblock physical read. This is true for both indexes and tables."
In addition, He also mentions that looking at an access path cannot tell you whether the database engine will use prefetching.
As far as I know, multiblock I/O (as know as, the event db file scattered read) is used only for FULL TABLE SCAN and FAST FULL INDEX SCAN. The other types of reads should be single I/O (as known as, the event db file sequential read). So, for example, how come an index range scan can use multi block I/O?
I am just trying to understand the logic behind it. Can someone put light on this subject?
Regards
Charlie