Re: Oracle index rebuilding performance

Hi Guys,

Thanks for the replies. Daniel, re-reading my post I understand the confusion over versions I'm at 8.1.7 hopefully going to 9.2.?.

I know that the rebuilding of the tables/indices isn't a solution, that's why I'm posting! As I said before I've used SQL Server for a few years but don't have much experience with Oracle. The current Optimizer_Mode is CHOOSE. I don't know when the database was created, but the original Delphi application which talks to it was created in 1999, I imagine it created in 8.1.7.

Joel, what you said about the indexes maybe not be created is interesting. The performance difference is exactly what I'd expect between indexes being there and not. This doesn't explain why it would get slower over (a rather short period of) time though.

The rebuilding code is exactly as i stated in the earlier post ALTER TABLE <tablename> MOVE TABLESPACE
<spacename>. Then ALTER INDEX indexname REBUILD. There are three different namespaces in the schema, one for archiving, one for large tables, and one for small ones.

I'll push for the upgrade, and do some reading about updating statistics. Thanks again for all your help.

Ryan

. Received on Fri Jun 03 2005 - 03:35:16 CDT