Re: Index management

David Williams wrote:

> I am an Informix dba who is posting in an Oracle group. I do not know
> your terminology but I do not btree index structures.

It explains a lot.

>
>>can be put into an index in a sub-optimal manner or even what
>>"sub-optimal" actually means. I can't see what is sub-optimal about
>
>
> sub-optimal means having all the index entries in as few blocks as
> possible
> to read/update/delete the blocks required whilst still allowing room for
> expected
> growth. Having to read nore index blocks to satisfy a query is not
> optimal.

And how many extra blocks, precisely, do you think you have to read when you create an index on EMPNO with 99% free space in the index nodes compared to 0% free space, and your query says 'select * from emp where empno=8867'? (I'll give you a clue. It's a nice round number. Very round).

What you are saying is that indexes can grow over time, and that affects the speed with which we can perform index range scans. Absolutely true. And absolutely nothing to do with the current discussion, which is about whether the clustering factor of an index has anything to tell us about whether to rebuild an index.

So you've effectively diluted a thread with observations which have nothing to do with the matter being discussed. Mike will no doubt thank you for the diversion.

It also does not follow that because a large index takes longer to scan than a smaller one, that you should thus rebuild it. Because a large index contains a lot of empty space which subsequent inserts into the table can occupy without causing (expensive) block splits and extent acquisitions. A compact index will block split and have to acquire new extents as soon as users start doing DML on the table. There is therefore a cost/benefit analysis to perform.

Index management is a lot subtler a subject than you appear to be aware.

>
> If the index entries you need to read can fit in less blocks than less
> reads are
> done and it is faster yes?
>
> If two index entries are in the same block than less writes are done when
> you
> update both rows at the same time, yes?
>
> If the index blocks are such that range scans can be done using
> sequential
> rather than random disk reads than it is faster yes?

Oh dear.

You presumably think that extents are contiguous on disk, or that making them so speeds things up.

I give up. You're making silly assumptions and assertions without apparently knowing very much about the matter.

> It is subjective

No it's not. Or it oughtn't to be. Mike Ault claims there is an objective set of test you can perform that indicate when an index should be rebuilt. I say that objectively his tests mean nothing. Objectively, that can be measured, assessed and verified.

If we are all going to take refuge in the "it's all subjective" argument, then we might as well all pack up and go home and never post here again. "Anything goes, so long as it "feels" right to you"?

Very scientific. Not.

> and every case needs to be considered independently.
> You need to use some general rules

Some general rules that are verifiably true would help, don't you think?

>and the skill is in applying them to
> specific cases. We need a few examples but do not assume the hugh
> speedups you get from contrived examples will apply to you. Getting
> a small speedup from lots of little tweaks can add up to a lot -
> compound interest. Index rebuilds are just part of it.
>
> What else would you using idle time on the machine for? Running seti
> on all your servers...or sitting idle doing nothing? Of course even this
> advice needs to be balanced. Using every free second is not wise
> as well.

Well, it's an original argument, that's for sure. "I don't know how indexes work, or whether a rebuild will speed things up or slow them down, but I've got nothing better to do, so I might as well rebuild them anyway".

It's not going to win you any prizes, I think.

HJR Received on Sun May 09 2004 - 18:37:21 CDT