Ask the Expert

Is running a reorg on indexes required?

If we run reorg on the table space weekly, is running a reorg on the index space ever required? With the required options a table space reorg by itself rebuilds indexes, correct?

    Requires Free Membership to View

Yes, if you are running a weekly table space reorg you may not need to reorg the indexes on that table space since those indexes are rebuilt every time the table space is reorganized.

However, for a very active application in a transaction processing environment you might want to see how disorganized the indexes become during the week. You might be able to get better performance if the indexes become disorganized quickly by reorganizing the index once (or perhaps more) during the period between table space reorgs. For example, if you reorg the table space every Saturday, you might want to examine the statistics for the indexes on Wednesday (or even Tuesday) to see how disorganized the indexes are. Look at the LEAFDIST, LEAFFAR, LEAFNEAR, and NLEVELS. As these stats get larger the index becomes less efficient. Maybe NLEVELS starts out at 3 on Saturday and is at 4 by Wednesday, then back to 3 after the reorg on Saturday. Reorganizing just the index on Wednesday night might help performance.

This was first published in January 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: