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

"Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message news:czksb.232$FE2.47_at_newssvr24.news.prodigy.com...
> "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
>
>

Thanks Howard and Dave for the help and information. Now I understand it better.

Thanks again
TG Received on Wed Nov 12 2003 - 08:17:03 CST