On 8/2/06, Dennis Williams <oracledba.williams@xxxxxxxxx> 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
