OraFAQ Forum: Performance Tuning » What does analyzing a table do for the indexes ?

Home » RDBMS Server » Performance Tuning » What does analyzing a table do for the indexes ? (Oracle 11g Win 7)

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

What does analyzing a table do for the indexes ? [message #567329]

Wed, 26 September 2012 11:46

jxh461
Messages: 148
Registered: March 2005

Senior Member

Hi All,

Can someone tell me what analyzing a table does to existing indexes ?
Do I need to rebuild the indexes after dbms_stats.gather_table_stats command ?

Thanks in advance

[Updated on: Wed, 26 September 2012 11:49]

Report message to a moderator

Re: What does analyzing a table do for the indexes ? [message #567337 is a reply to message #567329]

Wed, 26 September 2012 15:12

Michel Cadot
Messages: 54195
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Do I need to rebuild the indexes after dbms_stats.gather_table_stats command ?

No.

Regards
Michel

Report message to a moderator

Re: What does analyzing a table do for the indexes ? [message #567504 is a reply to message #567337]

Tue, 02 October 2012 16:48

alan.kendall@nfl.com
Messages: 160
Registered: June 2012
Location: Culver City, California

Senior Member

If you use "cascade=>TRUE" it will also analyse the indexes of the table.

ECSCDAP1P > execute dbms_stats.gather_table_stats('SCOTT','EMP',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

"cascade=>TRUE" also works for all the indexes if you gather stats at the schema level.

ECSCDAP1P > EXEC dbms_stats.gather_schema_stats('SCOTT', cascade=>TRUE);

PL/SQL procedure successfully completed.

Report message to a moderator

Re: What does analyzing a table do for the indexes ? [message #568438 is a reply to message #567504]

Thu, 11 October 2012 09:59

msol25
Messages: 211
Registered: June 2011
Location: Mumbai

Senior Member

Dear Jxh,

You no need to Rebuild indexes as you can analyze your table on table/Schema level or you can analyze your index as well.Please find
Difference between Rebuild/Analyze :

rebuild index can be used to relocate the index from one tbs to another and also to change the storage parameters of index.
Whenever you move table from one tbs to another tbs then you have to rebuild your indexed for that table as when you move tables associated indexes become invalid and you have to rebuild them.

analyze indexes to get/delete the statistics (current) on the indexes, also, to check the structure validity.

whenever you are getting new statistics, there is a chance of change in exection plan.

Like wise, there are many situation where you have to do decide analyze or rebild.

Report message to a moderator

Re: What does analyzing a table do for the indexes ? [message #568445 is a reply to message #568438]

Thu, 11 October 2012 10:23

Michel Cadot
Messages: 54195
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Add on -> NULL.
Mostly irrelevant.

Regards
Michel

Report message to a moderator

Previous Topic:	Improve performance postgre
Next Topic:	NOLOGGING in Oracle 11gr2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 22:42:40 CDT 2013

Total time taken to generate the page: 0.17583 seconds