OraFAQ Forum: Server Administration » Verify Rebuild index

Home » RDBMS Server » Server Administration » Verify Rebuild index (Oracle 10.2.0.4.0,SunOS)

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

Verify Rebuild index [message #548776]

Mon, 26 March 2012 04:13

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Hi,

How to verify if rebuild an index is required in database.

Regards,
Jack

Report message to a moderator

Re: Verify Rebuild index [message #548777 is a reply to message #548776]

Mon, 26 March 2012 04:17

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Index rebuilt is required very rarely and almost only when you do bulk delete so you know when you do this and rebuild the index.
Otherwise you don't need unless some queries show there is such a problem. So if you have no performances problem you don't need it.

Regards
Michel

Report message to a moderator

Re: Verify Rebuild index [message #548781 is a reply to message #548777]

Mon, 26 March 2012 04:33

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Michel,

Thank you for your quick responds.

Quote:

Index rebuilt is required very rarely and almost only when you do bulk delete

.

To find out the bulk delete is happening , i executed the below queries and found no rows return.

SQL> SELECT name,del_lf_rows,lf_rows - del_lf_rows lf_rows_used,
  2  to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  3  FROM index_stats
  4  /

no rows selected

Does this mean it does not need index to be rebuild ?

Can you please clarify my questions?

Regards,

Report message to a moderator

Re: Verify Rebuild index [message #548786 is a reply to message #548781]

Mon, 26 March 2012 04:53

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

index_stats pseudo-table is field only after "ANALYZE INDEX VALIDATE STRUCTURE".

Regards
Michel

Report message to a moderator

Re: Verify Rebuild index [message #548792 is a reply to message #548786]

Mon, 26 March 2012 05:21

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Michel,

Can you elaborate your answer? I did not get your point.

Regards

Report message to a moderator

Re: Verify Rebuild index [message #548793 is a reply to message #548792]

Mon, 26 March 2012 05:33

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

INDEX_STATS stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.

Report message to a moderator

Re: Verify Rebuild index [message #548798 is a reply to message #548793]

Mon, 26 March 2012 05:55

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Ram/Michel,

Let me explain you the findings.

Step1.

There were 200 tables in a schema.I verified the dml operations using below query.

select table_name,delete,timestamp from user_tab_modifications

Out of 200 tables , only few tables say (50 tables) have approximate number of deletes like below.

tbl name delete
--------- -------
Tab1 -- 336046
tab2 -- 9821468
tab3 -- 25800068
tab4 -- 140268

Step2

I analyzed the indexes (for only those 50 tables) using below command.

analyze index JACK.SCUST_IDX1 validate structure;

After analyzing the index ,i ran the below query to see the percentage of table data changes.

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats where name ='SCUST_IDX1'

But the above statement returns 0 rows.

Does it mean we dont need to rebuild the index based on the above output.Can you clarify ?

Regards,

[Updated on: Mon, 26 March 2012 05:58]

Report message to a moderator

Re: Verify Rebuild index [message #548799 is a reply to message #548798]

Mon, 26 March 2012 06:09

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Does it mean we dont need to rebuild the index based on the above output

You NEVER have to rebuild indexes unless something requires it, that is bulk delete (which means maintenance window and so you know there is and rebuild is part of the task) or performances problem.
STOP searching some or any query to know if an index need to be rebuilt, there is none.

Regards
Michel

Report message to a moderator

Re: Verify Rebuild index [message #548822 is a reply to message #548799]

Mon, 26 March 2012 08:42

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Michel,
Thanks for your advice. I will follow the same.

Quote:

You NEVER have to rebuild indexes unless something requires it, that is bulk delete

I agree with you.My question is how to verify the table has bulk delete ?

Regards,

[Updated on: Mon, 26 March 2012 08:43]

Report message to a moderator

Re: Verify Rebuild index [message #548824 is a reply to message #548822]

Mon, 26 March 2012 08:52

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As I said, a bulk delete is not (should not be) done during normal hours and so application will ask for a maintenance time for this and so you will know.
In short, the user will tell you.
And, in addition, this does not matter as index rebuilds should be done in the same process.

Regards
Michel

[Updated on: Mon, 26 March 2012 08:53]

Report message to a moderator

Previous Topic:	BLOB are missing
Next Topic:	IP Address, Hostname or Domain Of Oracle Database Servers

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 20:51:44 CST 2025