Home » RDBMS Server » Server Administration » Verify Rebuild index (Oracle 10.2.0.4.0,SunOS)
Verify Rebuild index [message #548776] Mon, 26 March 2012 04:13 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

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

Regards,
Jack
Re: Verify Rebuild index [message #548777 is a reply to message #548776] Mon, 26 March 2012 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Verify Rebuild index [message #548781 is a reply to message #548777] Mon, 26 March 2012 04:33 Go to previous messageGo to next message
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,
Re: Verify Rebuild index [message #548786 is a reply to message #548781] Mon, 26 March 2012 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
index_stats pseudo-table is field only after "ANALYZE INDEX VALIDATE STRUCTURE".

Regards
Michel
Re: Verify Rebuild index [message #548792 is a reply to message #548786] Mon, 26 March 2012 05:21 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Michel,

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

Regards
Re: Verify Rebuild index [message #548793 is a reply to message #548792] Mon, 26 March 2012 05:33 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
INDEX_STATS stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.
Re: Verify Rebuild index [message #548798 is a reply to message #548793] Mon, 26 March 2012 05:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Verify Rebuild index [message #548822 is a reply to message #548799] Mon, 26 March 2012 08:42 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Thu Mar 28 18:52:08 CDT 2024