Re: Index management

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"

------------------------------------------------------------