I need help on one of the query. My execution plans shows Index Scan in spite of using the Indexed Column in the Join predicate. I have two tables and one is 35 million and another one with 10K rows. I am trying to do a left outer join and my execution plans shows up Hash Match with Index Scan. I am not sure on how to improve this query. Also, my statistics are updated. Please can someone help on this issue?
Many thanks in advance.
Cheers - DV
dbo.Test_Sales.Sector = dbo.Test_Risk_Factor.Sector
Hash match is common if the inputs are unsorted, so the optimizer needs to build a hash table and probe it from the other table, rather than use the 'faster' merge join.
What indexes are on these tables? And can you post the execution plan, either as an image or as XML?
I had a quick look before you removed the pasted XML plan, and there seemed to be a datatype conversion on the
Based on what you have said below, and the amount of data being processed, the hash match may be the best operator. The reason for the index scan, rather than the index seek is simply because you have no