Re: Index management

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<409DF7C2.C34_at_yahoo.com>...
> Howard J. Rogers wrote:
> >
> > Daniel Morgan wrote:
> >
> > > Roger S Gay wrote:
> > >
> > >>>> By definition the order of rows in a relational table is random.
> > >>>
> > >>>
> > >>> Also absolutely true.
> > >>>
> > >>
> > >> Howard, are you sure of this? Relational theorists please correct me
> > >> if I am
> > >> wrong, but I thought the best we could say about the order of rows is
> > >> that
> > >> it is indeterminate, which means we can't even make statistical arguments
> > >> based on assumed randomness.
> > >
> > >
> > > You are correct. The use of the word "random" is exactly what is the
> > > problem with much of this thread: It is imprecise.
> >
> > No, the problem is intervention by people who don't know what the point
> > of this thread is.
> >
> > Imprecision in the use of the word "random" is not the issue.
> >
> > The issue is "should I rebuild my indexes". And moving on from that,
> > "how do I know if an index should be rebuilt". That is all. Everything
> > else is just so much navel-gazing.
> >
> > Mike Ault claims he has a ratio which tells you when an index should be
> > rebuilt. That ratio is built on one number we can't quite work out what
> > he means. And another number, the clustering factor, which we all know
> > presicely what it means.
> >
> > Either that ratio is a load of old nonsense, or it isn't. That's all you
> > have to be precise about. True or false. Black or White. Right or Wrong.
> >
> > All Mike has to do is to show us how to calculate this ratio, and show
> > us the ratio improving after an index rebuild (NOT an index redesign).
> >
> > That's all. It isn't rocket science. And it isn't very difficult. And
> > it's not at all imprecise.
> >
> > > I know that both
> > > Mike and Howard know that what you said is correct. And both undoubtedly
> > > will respond with "that is what I meant."
> >
> > Bzzt. I replied, and reply, that I couldn't give a monkeys whether its
> > random or indeterminate or painted bright blue and playing Knees Up
> > Mother Brown on the harpsichord. It *doesn't* *actually* *matter*.
> >
> > > But it may well be that much
> > > of this dispute is the fact the words are being thrown around without
> > > the clarity that would be provided by the demos we are asking for.
> >
> > No, the dispute is: can you give me a ratio that tells me when to
> > rebuild an index?
> >
> > Not when one of the components of that ratio is a number which is
> > invariant when rebuilding, you can't, is my reply.
> >
> > It is a simple enough dispute.
> >
> > > Mike, for example, must be able to quickly and easily determine whether
> > > a clustering factor is altered by a rebuild. The fact that he continues
> > > to repeat his statement indicates to me that we likely have more
> > > sloppinless of language than sloppiness of code. Problem is we have yet
> > > to see that code.
> >
> > This I will grant you. If Mike keeps stating something when the simplest
> > of test proves the contrary, he either is merely obtuse, or he is
> > talking about something we mere mortals aren't.
> >
> > We have already seen this, in fact, when Mike subtley shifts the meaning
> > of the words "rebuild an index" to actually encompass "drop index X...
> > create index newX".
> >
> > So maybe there is something else there he hasn't yet explained, and it
> > would do us all a lot of good for him to so explain it. That is indeed true.
> >
> > > Which is a point to which I will agree and I suspect Howard will too.
> > > That it appears that Mike does not is what I would like to see resolved
> > > in this thread if we can just stick to Oracle and not personal insults.
> >
> > I truly wish you would call a spade a spade. It is insulting, frankly,
> > to be accused of not discussing technical points. Which you do by your
> > "equanimity" in such comments.
> >
> > >
> > > Let the entertainment continue.
> > >
> >
> > It isn't entertainment. This is factual science, pure and simple. Either
> > Mike knows what he's talking about; or he's not talking about it very
> > clearly; or he doesn't. There are not many other possibilities.
> >
> > If you find that fun, so be it. Personally, I find it immensely
> > irritating that the *knowledge* element of this is having to be spelled
> > out as if for the first time.
> >
> > Regards
> > HJR
>
> There IS a ratio...Its
>
> effort to rebuild / benefit of rebuild < 1
>
> Of course, you can't precisely define the benefit until you've done it..
>
> :-)
>
> --
> Connor McDonald
> Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> ISBN: 1590592174
>
> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: connor_mcdonald_at_yahoo.com
>
> Coming Soon! "Oracle Insight - Tales of the OakTable"
>
> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
> and...he will sit in a boat and drink beer all day"
>
> ------------------------------------------------------------

Connor,

First let me say, excellent book! I suggest it to the PL/SQL developers I come across and use it to review, shall we say, suboptimal code?

Anyway, I thought the concept of dirty base table blocks was self-evident to those using Oracle, but since there seems to be some confusion let me digress into an explanation. When a table is created all of its blocks are in a sense "clean", that is, they contain no data. As Oracle writes data to the database tables, their blocks become "dirty", a reference no doubt to the "dirty buffers" the dirty-buffer writer writes to them (while the politically correct "database writer" has supplanted the original "dirty buffer writer" in the original database documentation in version 6 and earlier it was "dirty buffer writer".) So, a dirty block contains data, while a clean one does not. Obviously, unless it is corrupt, an index can only point to dirty blocks in a table. Hence a "perfect" clustering factor would be equal or perhaps less than the number of dirty blocks, while for a very random index, it would be closer to the number of actual table entries (for some bitmap indexes, I have seen it greater than the number of rows.) Usually, clustering factor shouldn't be greater than the number of rows in the underlying table.

Now, as a block fills and then reaches pctused, it is taken off of the free list and is not eligible for any more insertions, however it can under go updates which can cause row chaining, but that is another issue. Once deletions from the block cause the available space to drop below pctused and it is placed back on the free list for more inserts. This insertion back on the free list could be hours, days or some greater time later than the last time it was used for inserts. This means the index nodes that are referencing its inserted rows are most likely way across the index tree from the original ones that hold most of its entries.

Add to this the various node splitting algorithms Oracle uses for non-sequential inserts and updates and you can easily see why clustering factor increases and can become out of sync with reality. An index rebuild coalesces nodes and aligns them with the underlying table. Now, in many cases this reduces the clustering factor, however, I have seem it stay the same (usually on truly random keys such as text, or concatenated columns), decrease (the desired outcome) or increase!

However, I may have stated things unclearly, the goal in index rebuilding is not to reduce clustering factor, that is actually a desired by-product, the goal is to ensure that the index is properly aligned with the underlying table and that its entries are not sparse. Clustering factor ratios are just one of several indicators that can tell you an index needs to be investigated.

Mike Received on Sun May 09 2004 - 12:37:59 CDT