Sybase reorg rebuild

learning_sybase
learning_sybase used Ask the Experts™
on
Hi,
I want to know when should we run reorg rebuild on a database or a table.
If i have run the reorg rebuild 3-4 months back and running update index stats regularly, when should i run reorg now.
What parameter will tell me when is the time to run reorg rebuild ? Please provide details.
How optdiag can be useful here ?
Thanks Deepak
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
1. "optdiag statistice" can help you get the clustered ratio for table data rows and indexes, and 1.00 is the highest values which means table/index performance is very good. When ratio is lower than 0.8, you should maintain your tables/indexes.
2. "update index stats" only refresh the statistice data in optimize instead of collecting garbage data/space from table/index. So, reorg rebuild is necessary  when table/index performance is poor. BTW, "update index stats" is also important because it can tell the optimize to use the proper query plan.
3. You can also try drop/recreate indexes to maintain tables especially for the tables have clustered indexes, this can reclaim much space when a table has low data/index clustered ratio. Also remember run "update index stats" after indexes recreating.

Commented:
You can use derived_stat function to determine whether you need it or not. Check that question and discussion:

http://www.experts-exchange.com/Database/Sybase/Q_25083002.html

Author

Commented:
good

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial