OraFAQ Forum: Server Administration » after INDEX REBUILD --> should we ANALAYZE underlying table?

Home » RDBMS Server » Server Administration » after INDEX REBUILD --> should we ANALAYZE underlying table?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

after INDEX REBUILD --> should we ANALAYZE underlying table? [message #64240]

Wed, 05 January 2005 09:33

patrick
Messages: 83
Registered: December 2000

Member

Hi,

I'm using Oracle 8i on an AIX-UNIX platform.

I havn't found documentation about this question:

I executed several INDEX REBUILD: should I run an ANALYAZE TABLE ... ESTIMATE|COMPUTE STATISTICS on the underlying tables to keep up to date the cost based optimizer?

Thank you in advance!

Best regards,

Patrick.

Report message to a moderator

Re: after INDEX REBUILD --> should we ANALAYZE underlying table? [message #64241 is a reply to message #64240]

Wed, 05 January 2005 09:45

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

after index rebuild, you have to analyze the INDEX.
if you feel there is a lot of changes in the record ( underlying base tables) you should also analyze the tables.

FIrst, why are you rebuilding the index?
It is going to help NOTHING.

Report message to a moderator

Re: after INDEX REBUILD --> should we ANALAYZE underlying table? [message #64242 is a reply to message #64241]

Wed, 05 January 2005 10:08

patrick
Messages: 83
Registered: December 2000

Member

Hi,

Thank you for your answer! So one have to run an ANALYZE ... STATISTICS after REBUILDing INDEX, ok!

ANALYZE TABLE ... ESTIMATE|COMPUTE STATISTICS restore the statistics for the table's INDEXes too?

I have REBUILD sone indexes because I have MOVEd some tables: so the indexes buildt on them have been invalidated! That's why I executed a REBUILD command.

The ALTER INDEX REBUILD statement is a DDL but generates a lot of REDO: it's generates RECURSIVE DML on Data Dictionary tables.
But why is this REBUILD generating "so" much redo?
My Archiver filled up my disk because of these 8 INDEX REBUILD operations! :(
The underlying tables have between 6-30 million rows, but REBUILD is a DDL and shouldn't generate that much redo?

Thank you for your help.

Best regards,

Patrick.

Report message to a moderator

Just saw that you responded to the last part of my question! [message #64243 is a reply to message #64241]

Wed, 05 January 2005 10:12

patrick
Messages: 83
Registered: December 2000

Member

Hi,

Just saw that you responded to the last part of my question!!!

Thank you so much!!!

Best regards,

Patrick.

Report message to a moderator

Previous Topic:	max processes
Next Topic:	ALTER INDEX . REBUILD - Why it (DDL) generates REDO?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 27 04:20:29 CDT 2024