By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,077 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,077 IT Pros & Developers. It's quick & easy.

Removing HASH Match / Inner Join

P: 18
Hi,

I'm at my wits end! I have two large tables one with 1.2mill one with 2.3 mill and they are very wide tables. I have a select with an inner join. All columns used in the join are contained in indexes. But it does an index scan and a massive hashmatch. Why is this? both tables have columns in the index ordered the same, all datatypes of the indexes are ints. The code looks like this.

SELECT TblActivities.*
FROM TblActivities
INNER JOIN TblBookingsCraig
ON
TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion

Does anyone know why it won't perform an index seek?

Cheers
C
Sep 15 '06 #1
Share this Question
Share on Google+
3 Replies


aramki
P: 9
You should have the indexes on the fields you want to join, do you have them like that?

TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion
Sep 15 '06 #2

P: 18
Hi,

Thanks for the reply. Yes I do have the indexes identically setup on both tables. Could it be ignoring the indexes because of the size difference of the two tables? One has 1millish and the other 2.5mill ish?

Cheers
C
Sep 18 '06 #3

P: 1
Did you check the values of these columns? if they contain duplicate values or highly identical values for number of records; say for 100 records at least the indexes have same values. In this case, the index scan would be the decision that SQL optimizer might take!
Aug 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.