RE: Operations that perform multiblock I/O and cluster factor

Sounds like a slightly twisted restatement of the fact that some things that could logically be done as an index range scan are converted to a FFS because the optimizer determines it will be cheaper to read the whole thing than to walk the tree for the required range. Thus having been converted to a FFS, you'll see scattered reads when you thought it might be doing sequential reads to service what is logically a range scan.  

If your co-worker has a case where what you reported was contended can be shown, I'd like to see it. I hope there is more light than heat in this debate. I'm not clear how Oracle would guess which blocks to multiblock read to perform an index range scan, unless it reads them all, which it should have reported as a FFS to begin with.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Josh Collier
Sent: Monday, December 10, 2007 2:15 PM
To: oracle-l_at_freelists.org
Subject: Operations that perform multiblock I/O and cluster factor  

Hi,  

Having a bit of a debate with a peer. Perhaps you can help me.  

Its my understanding that the only i/o operations that trigger multiblock I/O, i.e. db file scattered read i/o that is influenced by the multi block read count init parameter, are as follows

1. full table scan
2. index fast full scan

All other block access paths use db file sequential read, aka single block reads from the disk.  

Are there any other access paths that are served by multiblock I/O? My co-worker contends: If the range scan is not a full scan, the query optimizer will decide whether to use a scattered read depending on the fraction of rows in the scan and the cluster factor of the index.  

I'm very curious about this because there isn't clear documentation anywhere about this.  

Thanks,  

Josh C.  

--
http://www.freelists.org/webpage/oracle-l