Re: Index management

Mike Ault wrote:

> Ok.
>
> I don't mind when folks disagree, however, when you attach names to
> quotes and then say in effect this person is an idiot and this is
> rubbish, it does appear to be a personal attack rather than a
> professional one.

I agree. I think everyone involved should keep their personal opinions of the others to themselves. Lets just deal with Oracle, David Bowie, and Rugby.

> To distill my advice on index rebuilds, I say to only rebuild indexes
> which show, through proper analysis, to have problems.

I doubt anyone would disagree.

  Examples of
> possible probelms are: a large clustering factor to dirty base table

But to make this 'discussion' more valuable to everyone can we get explanations or examples of these things. I, for one, have no idea what a "dirty base table" is and would appreciate it if explanations for clarity were included.

> Reduction in the size of the clustering factor improves the chance the
> index will be selected by the CBO since the clustering factor is a
> multiplier in the index cost.

It seems to me that there is a dispute as to whether the clustering factor is affected by a rebuild. Can someone please provide evidence, and I mean a demo, proving that their point of view is valid. Is there a demonstration of the clustering factor changing with a rebuild? I haven't seen one. And yet my recollection is that someone posted a demo that showed the opposite.

> When this advice has been followed we (the folks at TUSC and other
> consultants) have seen batch times reduced from 5 hours to 2 hours,
> significant reduction in index space usage and significant reduction
> in required IO against the index tablespace.

I'm not disputing this but it is just anecdotal. Can you produce a demo ... CREATE TABLE, INSERT INTO TABLE, etc. that demonstrates this so that those of us in the bleachers can follow along?

> Mike Ault

Thanks.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)