Re: ALTER INDEX COALESCE

On 8/2/06, Dennis Williams <oracledba.williams_at_gmail.com> wrote:
>
>
> Note that several people on this list that are much smarter than me
> have written about the futility of rebuilding indexes unless some special
> events have occurred like lots of deletes. Their point is that (from my
> small understanding) a B+ tree index in a normal, operating system has a
> small amount of fragmentation in the indexes.
>

One example is when a primary key is generated by a sequence, and the table undergoes frequent deletes. This leaves a lot of unusable entries in the index. Unusable because the values continually ascend, with new values continually going to new blocks.

One cure for that is a reverse index, but it has its own problems, depending on how you use the index. Reverse indexes don't do well with range scans.

That particular scenario may be a good reason to use SYS_GUID(), even if you don't use RAC. (doesn't everybody?)

There's a fair bit of writing about this index topic on AskTom.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l