When a table is first indexed, especially with a clustered index, the records are in order, and there are a minimal number of index pages to find each record. As update, insert and deletes happen, the number of index pages increases. Over time this will impact query performance. It is recommended that dba's run reorgs periodically to collect garbage, compact the database, and compact the index pages. The downside to reorgs, is that prior to 15.7, they block access to the table, so typically must be run during off hours.
1 - What is the underlying cause?
Insert/Update/Delete activity in the database creates additional database pages, and index pages. The more pages the query has to read to get to the data it's looking for, the longer the query will take.
2 - Should reogs be performed on a regular basis?
Probably. Static, or rarely changing tables will need it infrequently, but as the activity on a table increases, the need for reorgs increases as well.
How should I determine when to run?
This depends on the environement you are in. The need for reorgs is directly related to the Insert/Update/Delete activity of the table. You can use the optdiag command to check the health of your tables, take a close look at some of the following values:
Index page cluster ratio;
Data page cluster ratio;
Empty data page count;
Empty leaf pages;
Deleted row count;
Forwarded row count;
Index empty leaf page count.
Check the Sybase documentation on optidag and the Sybase documentation on reorg to get a good idea of how to determine when to run reorgs in your environment.
