RSS Feed

Method R Discussion Group

Once the Workflow tables grow significantly, and then we run the Workflow Purge program, do we need to reorganize the Workflow tables?

Whenever you remove a significant amount of data from the Workflow tables, you should consider reorganizing or rebuilding the indexes and tables that were purged.  Of course, the meaning of "significant" can vary by environment, but if you have removed a few million rows, or perhaps 10% or more of the rows from a table, then you should consider reorganizing at least the indexes on the table, and then gathering a fresh set of database statistics on the table AND indexes.

If you have removed a large number of rows from a table and you don't anticipate that the table will ever grow that large again, or you need to reclaim the disk space, then you should reorganize the table AND the indexes, and then gather a fresh set of database statistics on the table AND its indexes.

The performance impact of rebuilding indexes on Workflow tables can be significant.  We have actually seen the optimizer refuse to use an UNIQUE index for a query that referenced all columns in the unique index until the index was rebuilt.  Needless to say, performance suffered greatly until the index was rebuilt!