Q

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?

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close