Re: Index management

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<40a088c4$0$25324$ed9e5944_at_reading.news.pipex.net>...
> "Joel Garry" <joel-garry_at_home.com> wrote in message
> news:91884734.0405101458.5eba0327_at_posting.google.com...
> > So do I need to rebuild indexes? (Pretty sure the answer would be
> > "no" since there is no key updating and hasn't yet been deletion...
> > but there will be, and massive... hopefully, after going to 9 and
> > partitioning. But it sure looks bad in Tablespace Map. Of course,
> > this whole thread only applies to CBO, right?).
>
> Er no, ISTM that the fact that Oracle's balanced b-tree indexes physically
> store index data efficiently in the vast majority of cases is also relevant
> to the RBO. In fact I don't think I have ever heard anyone argue that
> rebuilding indexes will affect the cost calculations used by the CBO
> presumably because those folk bright enough to have figured out and
> published stuff on cost calculations also know how indexes work.

ISTM that, given hjr's reply to Brian on deletion and balancing, you could have a situation where you delete rows, freeing up blocks in a periodic manner in the index (for illustrative purposes, say, every other block), then refill them with new increasing semi-monotonic data (by semi-monotonic I mean several possible values as a leading index field followed by a sequence, for example). Could you create a situation where Oracle trying to scan the index would take longer, because it has to follow the links in a hopscotch manner, as opposed to getting twice as many of them at a time after rebuilding the index for contiguity?

jg

--
@home.com is bogus.  "We are now on the right track, and we are
closing in on the goal of completion." "nearly guaranteed to cause
mission-critical failures and further delays."
http://www.signonsandiego.com/uniontrib/20040511/news_1n11fbi.html