This forum is currently locked. You can't register or post questions at this time. (read more)

Index issue

All posts relating to Oracle database administration.

Moderator: Tim...

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Index issue

Postby royalyogi » Sun Jul 21, 2013 6:19 am

Please help here

In 11g:
1.) If we have have a table and the table have indexes.If we collect statistics on that table,does the associated index of table get rebuilt automatically??
2.)How can we find out INDEX of table and how can we decide INDEX need to rebuild or not?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Index issue

Postby Tim... » Sun Jul 21, 2013 8:28 am

Hi.

1) No. Gathering table stats does not cause indexes to be rebuilt.

2) The DBA_INDEXES view lists all the indexes, along with the tables they belong to. There is no definitive measure for when an index needs rebuilding. The vast majority of the time it is not necessary to rebuild indexes as Oracle does a good job of maintaining them. I never "plan" for index rebuilds. I think the best way to think of this is, if there were a definitive way to identify an index needed rebuilding, Oracle would do it automatically. They do not, because there is not...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests

cron