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
 SSC Veteran Group: General Forum Members Last Login: Tuesday, September 03, 2013 1:55 AM Points: 289, Visits: 236
 Nice! Learnt something new today about hint limitations on RIGHT joins
Post #1164525
 Posted Wednesday, August 24, 2011 3:45 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, November 29, 2013 2:52 AM Points: 1,083, Visits: 2,023
 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
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 3:43 PM Points: 7,945, Visits: 8,365
 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'S iomadh doigh a th’ air cu a mharbhadh gun a thachdadh le ìme
Post #1164530
 Posted Wednesday, August 24, 2011 4:34 AM
 SSChampion Group: General Forum Members Last Login: Saturday, December 07, 2013 7:09 PM Points: 11,052, Visits: 10,818
 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #1164544
 Posted Wednesday, August 24, 2011 4:36 AM
 SSChampion Group: General Forum Members Last Login: Saturday, December 07, 2013 7:09 PM Points: 11,052, Visits: 10,818
 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #1164548
 Posted Wednesday, August 24, 2011 5:27 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, May 01, 2013 6:14 AM Points: 592, Visits: 1,423
 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, December 06, 2013 8:07 AM Points: 1,253, Visits: 13,537
 nice question! rfr.ferrariDBA - SQL Server 2008MCITP | MCTS remember is live or suffer twice!
Post #1164572
 Posted Wednesday, August 24, 2011 5:39 AM
 SSCommitted Group: General Forum Members Last Login: Wednesday, October 24, 2012 8:17 PM Points: 1,588, Visits: 247
 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: Thursday, July 25, 2013 7:37 AM Points: 1,615, Visits: 355
 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 09, 2012 3:46 AM Points: 2,223, Visits: 177
 Good Question on Join Hints.
Post #1164584

 Permissions