Re: Rebuilding indexes

"Matthieu DEGLIN" <mdeglin_at_remove-me.partner.auchan.com> wrote in message news:<a4qhen$4r7$1_at_reader1.imaginet.fr>...
> Hello,
>
> My question is quite simple:
>
> Is there a difference beetween rebuilding an index and dropping et
> re-creating it ?
> Which of the 2 methods is the quickest ?
>
> Tank you.
>
> Matthieu

In theory a rebuild reads the index entries and recreates the index without having to physically read the table. This should be faster than dropping and recreating the index. In reality Oracle is smart enought to recognize certain conditions that require it to physically access the table and will do so when necessary to rebuild the index (Example: alter table move to new tablespace. all the rowids now point to the wrong file(s)). Contrary to what some people believe the rebuild does not save time by not having to sort. Even in normal case where Oracle avoids reading the table it reads the index using a fast full index scan so a sort is still required. But normally the index is much smaller than the table so this is faster.

HTH

• Mark D Powell --