Home » RDBMS Server » Performance Tuning » Rebuild all the indexes of schema
Rebuild all the indexes of schema [message #302761] Tue, 26 February 2008 23:36 Go to next message
kamragulshan
Messages: 63
Registered: May 2005
Location: Delhi
Member
Hi Experts,

I have queries related to indexes:

1. Is there any way to rebuild all the indexes of a schema with a single command?

2. How to get the information about the indexes needs to be rebuild.

Thanks
Gulshan
Re: Rebuild all the indexes of schema [message #302762 is a reply to message #302761] Tue, 26 February 2008 23:46 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

1. Is there any way to rebuild all the indexes of a schema with a single command?
you can create .sql script for rebuild all indexes which need too be rebuild.

2. How to get the information about the indexes needs to be rebuild.
check: select index_name,status from dba_indexes where status = 'INVALID' --- need to be rebuild.

Re: Rebuild all the indexes of schema [message #302780 is a reply to message #302761] Wed, 27 February 2008 00:33 Go to previous messageGo to next message
kamragulshan
Messages: 63
Registered: May 2005
Location: Delhi
Member
Hi Taj,

Thanks for the reply.

Is there any built in procedure that can be use to rebuild all the indexes in a schema / database?

When we need to rebuild the indexes (except the case if status is INVALID).

If the database is processing slow and lot of new entries are inserted then is it require to rebuild the indexes to get the good performance at the time of reporting or fetching the data.

Thanks
Gulshan
Re: Rebuild all the indexes of schema [message #302783 is a reply to message #302780] Wed, 27 February 2008 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
lot of new entries are inserted then is it require to rebuild the indexes to get the good performance at the time of reporting or fetching the data.

What make you think that?

Regards
Michel

[Updated on: Wed, 27 February 2008 00:36]

Report message to a moderator

Re: Rebuild all the indexes of schema [message #302798 is a reply to message #302783] Wed, 27 February 2008 01:02 Go to previous messageGo to next message
kamragulshan
Messages: 63
Registered: May 2005
Location: Delhi
Member
Hi Michel,

I mean to say that in one of our schema, automatic process make the connection and perform their task like run the query to get the information and then insert the records into the tables based on the job.

So my concern is that there is only insertion and query of data not the deletion of records.

Since the last few days, queries are taking more time to fetch the data. As data is heavily loaded into the tables on daily basis, Whether i will get the good performance with 'Rebuilding the indexes'.

Thanks
Gulshan
Re: Rebuild all the indexes of schema [message #302813 is a reply to message #302798] Wed, 27 February 2008 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Whether i will get the good performance with 'Rebuilding the indexes'.

Once again what makes you think that?
Did you measure that bad performances come from indexes?

Regards
Michel
Re: Rebuild all the indexes of schema [message #302821 is a reply to message #302798] Wed, 27 February 2008 02:00 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
kamragulshan wrote on Wed, 27 February 2008 14:02

Since the last few days, queries are taking more time to fetch the data. As data is heavily loaded into the tables on daily basis, Whether i will get the good performance with 'Rebuilding the indexes'.



You can rebuild index, but, why have you not thought about the other reason, not "Rebuild Index"?

In general situation, Index is not the thing caused slowly query/DML. It's belonged to others such as big tables, transaction, sql*net...

Sometime ago, I though like you, however,when I realized that, index is not the reason, it's such my bad knowledge and many, many redo logfile was generated than before. You can read something at

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

As you know, one time switch logfile, the archivelog is generated associately. Of course, index has physical structure, and.. read at above link.



Re: Rebuild all the indexes of schema [message #303069 is a reply to message #302821] Wed, 27 February 2008 21:09 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Have you tried just re-gathering statistics with DBMS_STATS?

Ross Leishman
Previous Topic: >>>Wired SQL Tuning issue, maybe Oracle's bug<<<<
Next Topic: Query performance
Goto Forum:
  


Current Time: Mon Dec 05 19:26:04 CST 2016

Total time taken to generate the page: 0.13300 seconds