Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This select statement:

select *
from
  tab1 
  inner join tab2 on tab1.col1 = tab2.col2
where
  tab1.col3 in ('<some conditions>')

In the execution plan I see two indices used. But the second index which is used is not needed.

Could you please suggest a way to remove that particular index from the plan?

share|improve this question
6  
i can suggest you a way to pose the question in such a way that somebody could help you: show us the create-statements of the table and indexes and show us the query plan. –  miracle173 Apr 10 '12 at 9:41

2 Answers 2

There's no way this can be solved with a single index unless you have a FK Constraint on tab1.col1 which references tab2.col2 which means all the rows in tab1 meet the condition and the optimizer can tell that without checking.

You have a minimum of three checks required here:

  • WHERE Tab1.col3... will need to reference either an index on col3 or the clustered index (and do a clustered index scan)
  • INNER JOIN... will need two checks - one on tab1.col1 and another on tab2.col1. It's possible both the conditions on tab1 can be met by a single index if it was properly designed but it's unlikely.

What makes you think the second scan is unneeded? You need to share your exec plan and table structure to get any further assistance, but based on what's available your premise is flawed.

share|improve this answer

One approach that might help would be to specify the (few) columns you actually need instead of just using the "blanket" SELECT *.....

When you define a small set of columns to retrieve, you might be able to create a covering index (a nonclustered index that contains all the columns you need) that could be used to satisfy the query, and thus possibly avoid an index scan.

When using SELECT * ... you're more or less making sure you cannot make good use of any of the indices - since in the end, the fact you want ALL the data will always have to go back to the actual data pages (the clustered index leaf level pages) and thus more often than not, the query optimizer will just "give up" and switch to a clustered index scan.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.