OraFAQ Forum: Server Administration » Tidying index and tables

Home » RDBMS Server » Server Administration » Tidying index and tables

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

Tidying index and tables [message #62096]

Thu, 24 June 2004 09:19

James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)

Member

I need to set up regular jobs that keep indexes and tables tidy. I plan to run "alter index indexname rebuild;" to tidy the indexes but is there anything else i could do, say on the actual table?

We're running Oracle 8i on a Sun Solaris 8 unix platform.

Report message to a moderator

Re: Tidying index and tables [message #62097 is a reply to message #62096]

Thu, 24 June 2004 09:43

andrew again
Messages: 2577
Registered: March 2000

Senior Member

You will probably also need to collect stats again. Before you get into rebuilding indexes you should be able to quantify the improvement you think you are getting. Oracle has no general recommendation to rebuild indexes. Of course there are cases when rebuilding tables or indexes can shrink their sizes. Be careful not to start a new myth that rebuilding indexes and tables as a regular activity is a good thing. A good reason not to mess around with your schema is that you could end up dropping an index if your rebuild job fails and then you are going to have a 3 month investgation to find outwhy your app is suddenly slow. Oracle has a great paper called "How to Stop Defragmenting and Start Living". Just google for it.

Report message to a moderator

Re: Tidying index and tables [message #62098 is a reply to message #62096]

Thu, 24 June 2004 10:26

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Andrew said it sound and clear.
There is no proof that, just rebuilding indexes helps on performance.
Rather updating the statistics by analzying the table and indexes in REGULAR BASIS will definatly help the database.

Report message to a moderator

Previous Topic:	SPACE LEAK
Next Topic:	Cannot connect to Oracle 8 server

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 14:32:09 CDT 2024