Re: How many blocks are read from Disk to Buffer Cache?

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3fb19c2f$0$13984$afc38c87_at_news.optusnet.com.au...
> When you retrieve blocks via a full table scan, you do so by reading
> multiple blocks at one time. But since an index tells you precisely which
> block(s) it is that contains the required row(s), those reads are always
> done one block at a time. Even if there are lots of blocks to fetch
because
> you're selecting lots of rows: if Oracle uses an index, it reads one block
> at a time (which is why, incidentally, if you really are selecting lots of
> rows as a proportion of the total number of rows, the optimiser is most
> unlikely to want to go via the index, and will opt to do full table scans
> instead. The cut-off point where Oracle decides to give up on indexes is

An exception is fast full index scan, which is used when all the columns referenced in the query are contained in the index, and at least one of the index columns has a NOT NULL constraint. In this case, the index is read using multiblock reads, like in a full table scan. Another similarity between fast full index scan and full table scan is that both read the blocks sequentially.

HTH,
Dave Received on Wed Nov 12 2003 - 00:38:00 CST