Notes in-line.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar
"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message
news:4093a2c2$0$20085$afc38c87_at_news.optusnet.com.au...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> > So a range scan might now go:
> > block 49, 50, 750, 51, 52
>
> I was trying to figure out if this could occur.
> It appears to me that assuming:
> 1- a very volatile table where its index also gets "pushed around",
> 2- a separate partition for the index,
>
> there might be a case for needing to re-build an index.
> Nothing to do with a problem with the way Oracle stores the
> indexes or the nature of their implementation of B-trees,
> but just on pure physical optimisation grounds.
>
I think the "rules" are always changing in areas like this,
sometimes for reasons that we won't think of because
our habits keep us looking in the wrong direction.
Two (contradictory) thoughts:
1) Why would reading blocks 50, 51, 52
in serial fashion be any quicker than
reading blocks 50, 750, 51 ?
After all, between the first and second
reads, someone else will almost certainly
have put in a read request for a completely
different location on the disk anyway.
2) Oracle has an 'index prefetch' algorithm
that I believe is supposed to kick in for large
index range scans. I think I've seen it happen
a couple of times on an index FULL scan (NB
I don't mean 'fast full scan'). I think the code
works on the assumption that the leaf blocks
for a large range scan are likely to be adjacent,
so a read request is made for multiple blocks,
and irrelevant ones are discarded.
I think every case has to be considered on
its own merits - and we keep finding more pros
and cons that have to be weighed.
Of course, as the default action, I would still
assume that indexes don't need to be rebuilt
to schedule, but still be inclined to rebuild
partitions just before making them read-only.
Received on Sat May 01 2004 - 12:41:38 CDT
