Re: Does it read empty blocks into buffer cache ?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Tue, 28 Jan 2003 23:18:22 +1000
Message-ID: <oguZ9.35139$jM5.89987@newsfeeds.bigpond.com>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:jZXY9.33684$jM5.86178_at_newsfeeds.bigpond.com...
> news.blueyonder.co.uk wrote:
> > There's a couple of answers to this question:
> >
> > 1. An index exists on the table.
> > if an index exists and the optimiser recognises the index can be used,
then
> > no full table scan would be made.
> >
> > 2. The table has been analysed.
> > if the table has been analysed the optimiser will recognise there are no
> > rows in the table and therefore no full table scan will be necessary.
>
>
> Now this one is interesting. And wrong. The rule is that 'BLOCKS' in
> DBA_TABLES tells Oracle what to read, not NUM_ROWS. And since a delete
> doesn't adjust the HWM, even an analysis will indicate that there are
> lots of blocks to read. And they will accordingly be read:

Yeah, but if you set the
_I_PROMISE_NOT_TO_INSERT_ANY_FURTHER_ROWS_HONEST_GOV=TRUE parameter, so that the optimizer knows that since you last analyzed the table on the 13 JUNE 1997, there are still no rows, then Oracle will clearly not have to read in the empty blocks.

Covered in chapter 4 of my book coming out soon ...

Richard Received on Tue Jan 28 2003 - 07:18:22 CST