Re: alter index rebuild (NOT ONLINE)

On Thu, 30 Oct 2003 09:24:23 -0800, OakRogbak_erPine_at_yahoo.com Kill the 2 trees in email address to reply wrote:

<SNIP>

> I have read many places that it is not a good idea to rebuild them,
> and I believe that. It is odd though, that Oracle does recommend it:
> ---

<SNIP>

Oracle also recommend that you separate tables and indexes in different tablespaces, for performance reasons, because the action of reading from and index and then from a table is done simultaneously. It isn't.

This is a long running Oracle Myth which Howard has tried on numerous occasions to put to death. People still believe it. It should be noted that progress is being made as the 9i course manuals no longer mention that there is a performance enhancement in separating them.

> In my book "Oracle 9i DBA Handbook" (from Oracle Press), on page 149,
> the last paragraph says "To reclaim the unusable space in an index,
> you can use the ALTER INDEX REBUILD command. Schedule a batch job to
> run periodically to rebuild the indexes on your most active tables."

Another myth - deleted space in an index is never reused. This too is untrue and is easily demonstable. Howard has done so in this NG - have a scan of Google Groups if you like for details.

The best book to find good info about indexes is the 'Beginning Oracle Programming' book by Tom Kyte et al (and Howard J Rogers - who doesn't like being an 'et al' !). Don't be mislead by the title as it covers the inner workings of the database in some details.

Cheers,
Norm.

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