Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 The Right Kind Of Join Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, August 24, 2011 3:44 AM
 Old Hand Group: General Forum Members Last Login: Monday, April 20, 2015 9:14 AM Points: 305, Visits: 240
 Nice! Learnt something new today about hint limitations on RIGHT joins
Post #1164525
 Posted Wednesday, August 24, 2011 3:45 AM
 SSCommitted Group: General Forum Members Last Login: Thursday, December 1, 2016 3:10 AM Points: 1,633, Visits: 2,645
 Paul - that's exactly what I did when BOL didn't quite give enough information to answer.I agree with your view on the 2 point question; sorry for doubting you.(In real life, they're all 2+ point questions - you never get it handed on a plate).
Post #1164528
 Posted Wednesday, August 24, 2011 3:47 AM
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 2:14 PM Points: 9,833, Visits: 11,907
 Good question.Isn't the inability to apply the loop hint to a right join a bit bizarre though? Although it seems pretty obvious why this happens, there is in fact something odd about the exclusion of right join. After allA right join B on A.x=B.xis identical in meaning to B left join A on A.x=B.xand I can apply a loop hint to the second. So I ought to be able to apply it to the first, unless the optimiser interprets the loop hint as saying "use a nested loop join where the outinner loop is on the right hand component of the join, that is the table which occurs second in the text of the query" rather than just "use a nested loop join (choosing whichever component the optimiser thinks best for the outer loop)". The optimiser has great freedom generally, and making the loop hint specify more of a constraint on the optimiser than it really needs is out of tune with that usual freedom.edit: "outer" should be "inner", see text struck and replaced above Tom
Post #1164530
 Posted Wednesday, August 24, 2011 4:34 AM
 SSCrazy Eights Group: General Forum Members Last Login: Wednesday, December 7, 2016 6:42 AM Points: 9,932, Visits: 11,347
 Tom.Thomson (8/24/2011)Isn't the inability to apply the loop hint to a right join a bit bizarre though?I would normally answer this, but today's question is one of three... Question 2 will appear next week (1 September), and question 3 a week later. Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1164544
 Posted Wednesday, August 24, 2011 4:36 AM
 SSCrazy Eights Group: General Forum Members Last Login: Wednesday, December 7, 2016 6:42 AM Points: 9,932, Visits: 11,347
 Andrew Watson-478275 (8/24/2011)Paul - that's exactly what I did when BOL didn't quite give enough information to answer. I agree with your view on the 2 point question...No worries Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1164548
 Posted Wednesday, August 24, 2011 5:27 AM
 Old Hand Group: General Forum Members Last Login: Thursday, July 30, 2015 8:11 AM Points: 363, Visits: 1,426
 Nice. I had to work for this one - learned quite a bit in the process. Thanks! -Ki
Post #1164570
 Posted Wednesday, August 24, 2011 5:30 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, November 25, 2016 5:31 AM Points: 1,265, Visits: 13,618
 nice question! rfr.ferrariDBA - SQL Server 2008MCITP | MCTS remember is live or suffer twice!the period you fastest growing is the most difficult period of your life!
Post #1164572
 Posted Wednesday, August 24, 2011 5:39 AM
 SSCommitted Group: General Forum Members Last Login: Sunday, April 27, 2014 7:45 PM Points: 1,589, Visits: 253
 Learned something new again today, thanks. http://brittcluff.blogspot.com/
Post #1164575
 Posted Wednesday, August 24, 2011 5:54 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, November 29, 2016 6:20 AM Points: 1,748, Visits: 446
 I was able to look this up and find the right answer but I'd be lying if I said that I understood what is going on here. Is there an article that explains the join hints? If not, perhaps that's a good area for a future article. MSDN wasn't particularly helpful to me on this. I suspect that they expect me to have some base level of knowledge that I'm lacking.
Post #1164583
 Posted Wednesday, August 24, 2011 5:55 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, May 9, 2012 3:46 AM Points: 2,223, Visits: 177
 Good Question on Join Hints.
Post #1164584

 Permissions