Re: Does it read empty blocks into buffer cache ?

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.

So your solutions is to TRUNCATE the table which will bring the high water mark down.

"Bass Chorng" <bchorng_at_yahoo.com> wrote in message news:bd9a9a76.0301201619.19d6d03f_at_posting.google.com...
> If I have a table with 0 rows but it has a very high highwater mark
> and if I do a select *, would Oracle scan all empty blocks into
> the buffer cache ?
>
> The known factor is Oracle will perform a full table scan to the
> highwater mark although the table is empty, and the question
> is,is Oracle going to flood the buffer cache with all the
> empty blocks it reads ?
>
> I don't see any reason why it wouldn't, as it is the same
> as if the table is loaded with rows. But then the state of
> x$bh would be 0 or 1 for those empty blocks that are read
> in ?
>
> I did a test and observed no state change. But I can not
> tell if it did not read the empty blocks in the first place
> or if it did, but since the blocks are empty, they are
> still marked as state 0.
>
> Wonder if any body knows the answer.
Received on Sun Jan 26 2003 - 14:18:40 CST