Re: Proving or Debunking the need for rebuilding

DA Morgan wrote :
> hasta_l3_at_hotmail.com wrote:
> >>> Is it possible what the OP is seeing is a delayed block cleanout?
> >> I think it is entirely possible. I'm wondering if simply doing a
> >> count(*) that uses the index in question, before performing each test,
> >> might shed light one way or the other. The idea is to visit all blocks
> >> and clean them.
> >>
> >
> > We are deleting in a bunch of small transactions.
> > We should have (almost) no delayed block cleanouts, right ?
> >
> > --- Raoul
>
> Depends!

Would you elaborate, Doug ?
I am not much familiar with this area.

At commit time, blocks will be cleaned up if they are still in the cache, right ?

Off hand, I cannot think why (many) dirty blocks would be written on disk :

1. We have pretty big redo logs.
2. Checkpointing happens with low frequency
3. With high probability, we dont do huge selects or table scans that would flush blocks out of the cache.
4. The structure of an index does not massively change when - say - ten or twenty rows are deleted, right ? Or does it ?

Anything I missed ?

• Raoul