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