Ask the Expert

Index scans affecting database file sequential read waits

I found high database file sequential read waits in my Statspack report. When I dug down to find the reason, I found that unnecessary index scans may be one of the reasons. (Is this true?) I have many large tables with indexes on duplicate columns, say one index on columns (X,Y) and a second one on columns (Z,X). We access the data using both indexes:

where X=... and Y=...

where Z=... and X=...
1) Should I remove the second index (on Z and X) and create an index on Z only? In Oracle9i, column position is not a problem. I can also use the index skip scan hint to use an index when column without a leading position is used in the WHERE clause. Please suggest to me which of the column indexes should be there.

2) If there are two indexes, one on columns X,Y and second only on Y, should I drop second the index and pass a hint in a query which uses i=only column Y in the WHERE clause? If yes, will this degrade the performance of the SQL?

    Requires Free Membership to View

As you've correctly observed, database file sequential read waits are due to index reads. If your optimizer statistics are up to date, Oracle should use only the index or indexes that will provide the most efficient execution of your query. To determine the proper indexing strategy, you should experiment with different index configurations, observing both the execution plans produced and the run time. Just remember to gather stats on a newly-created index so that the optimizer can use it. I doubt that you'll have to resort to hints.

The key here is to test possible configurations and make your decision based on the test results.

This was first published in April 2005

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: