Method R Discussion Group

Method R helps developers, DBAs, and decision-makers optimize Oracle-based software in every phase of the software life cycle.

Discuss Method R products, techniques, and events.

Ask new questions or post your own responses.

Workflow Purge - Webinar Question on 10/23/07

Once the Workflow tables grow significantly, and then we run the Workflow Purge program, do we need to reorganize the Workflow tables?
Becky Goodman Send private email
Tuesday, October 23, 2007
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!
Craig L. Anderson Send private email
Tuesday, October 23, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Discussion Groups Main Page

Powered by FogBugz