Re: alter index rebuild (NOT ONLINE)

On Sun, 02 Nov 2003 13:43:22 -0800, Yong Huang wrote:

Morning Yong,

> What you describe is quite correct. Tom Kyte has a very impressive
> analogy. An index is like an ordinary person; if you force yourself to
> lose fat, you'll get a little fatter back to what you are now.
> Similarly, if you force an index to be unnaturally skinny (densely
> packed), it'll still become plump as it is now.

Not me, I never go an a diet - my wife on the other hand ......

> The case I described is such that the next data load may come a few
> weeks or even months down the road. Before that time, many users need
> to query the data.

This is true, however, are the users complaining about very slow response times - if not, then do you *really* need to be rebuilding indexes ? If you do, then when do you have to do it - while your users are querying the data or outside normal hours.

In the first case, *you* are causing the users response probles as querys hang waiting for access to the table because your rebuild has taken out an exclusive lock on it.

If it is outside normal hours, then you are missing out on life because you are sitting in an office waiting for a rebuild to finish. And you can't fire off a rebuild script and go home because it might fail and then where will you be tomorrow when the users do start to complain?

> In this case, it makes sense to force the index to
> be "unnaturally" skinny by rebuilding or coalescing it, if I can. The
> less data blocks for the index, the less disk I/O and more efficient
> use of buffer cache. In practice, though, you need try at least once
> to see if the index size really comes down.

Not unless you have a performance problem.

> Given that Tom has been in this business for so long, it's amazing he
> still remembers the exact number of times he rebuilt indexes...

Well. I can see how he would remember if he has only had to do it infrequently. I've only ever *had* to rebuild an index twice in 7 years myself. It was based on a sequence number and had a lot of deletions - but not enough to free up entire blocks for re-use. I've been asked on numerous occasions by developers to rebuild indexes to help with performance - it didn't, the problem was in the SQL being used :o)

I suspect we won't agree on this one, but I say 'never rebuild indexes unless you really have to'.

Cheers,
Norm.

>
> Yong

-- 
Delete the obvious bit from my email address to reply by email.