RE: Rebuilding Indexes Question

From: Nirmal Kumar Muthu Kumaran <NIRMALK_at_qtel.com.qa>
Date: Sat, 27 Oct 2001 22:20:04 -0700
Message-ID: <F001.003B6746.20011027222017@fatcity.com>

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:ahillman_at_erols.com]
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: root_at_fatcity.com [mailto:root_at_fatcity.com]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: cjordan_at_smcwv.com [ <mailto:cjordan_at_smcwv.com>]
>
> 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. Received on Sun Oct 28 2001 - 00:20:04 CDT