Re: Does it read empty blocks into buffer cache ?

"tingl" <one4all_at_all4one.not> wrote in message news:NAlX9.4937$Sv3.524551_at_newsread1.prod.itd.earthlink.net...
> No, Oracle does not scan empty blocks. It would be inefficient to do so.

Nonsense. Oracle wouldn't know an empty block if it came up and hit on the head. When asked to read a table via a full table scan, Oracle reads EVERY block, empty or not, under the table's high water mark (the subtleties of ASSM notwithstanding) into the buffer cache.

So yes, it *does* read empty blocks. Inefficient or not, it has no choice: it has no other way of working out when it's hit the end of the table than to read *everything* up to the HWM, without discrimination.

Your answer requires another question: how does Oracle *know* the block is empty until it's read it and discovered it to be so? In which case, it must have read it into the buffer cache only to discover that it was empty. But that means, er... it's read it into the buffer cache.

And yes, that is indeed inefficient... which is why tables may occasionally need to be re-organised, so that the HWM lies flush with the actual data in the table. A very occasional resort to 'alter table BLAH move;'' achieves precisely that (though the indexes need to be rebuilt afterwards, which explains why this is an occasional occurence, not an every day thing).

Regards
HJR
>
> "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 Tue Jan 21 2003 - 18:37:43 CST