I think, rebuilding indexes may use the existing index to build, so that the performancce will improve comparitively doind drop and create indexes...
Nirmal.,
-----Original Message-----
From: Alex Hillman [SMTP:[EMAIL PROTECTED]]
Sent: Sunday, October 28, 2001 3:30 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rebuilding Indexes Question
I remember reading somewhere that alter index ... rebuild analyzes index with estimate default. In case this is not desirable one need to reanalyze an index after rebuilding.
Alex Hillman
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jacques Kilchoer
Sent: Monday, October 22, 2001 7:50 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rebuilding Indexes Question
> -----Original Message-----
> From: [EMAIL PROTECTED] [ <mailto:[EMAIL PROTECTED]>]
>
> What are the advantages/disadvantages to doing a alter index rebuild
> (Oracle 8.0.X) versus dropping and recreating the index.
My personal opinion - if your database is not subject to any of the bugs reported against alter index ... rebuild commands,
{some bug examples:
[BUG:1475310] ALTER INDEX .. REBUILD ONLINE/ MOVE IOT ONLINE can corrupt the index. See [NOTE:125149.1]
[BUG:1427002] ALTER INDEX i REBUILD PARAMETERS('SYNC') may raise ORA-29863 / DRG-10595 / ORA-28579 (interMedia Text)
[BUG:1573283] OERI:6033 from ALTER INDEX .. REBUILD ONLINE PARAMETERS ('OPTIMIZE FULL')
etc...
}
alter index ... rebuild is easier to use, since you don't have to specify the full syntax that was used to build the index. I don't know of any reasons to prefer the drop index / create index method.
Any corrections/suggestions welcome.