Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

The Right Kind Of Join Expand / Collapse
Author
Message
Posted Wednesday, August 24, 2011 3:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 1:55 AM
Points: 298, 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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:26 AM
Points: 1,170, Visits: 2,152
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

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:56 PM
Points: 8,271, Visits: 8,717
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 all
A right join B on A.x=B.x
is identical in meaning to
B left join A on A.x=B.x
and 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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:29 PM
Points: 11,168, Visits: 10,928
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 White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1164544
Posted Wednesday, August 24, 2011 4:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:29 PM
Points: 11,168, Visits: 10,928
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 White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1164548
Posted Wednesday, August 24, 2011 5:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:25 PM
Points: 1,253, Visits: 13,546
nice question!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1164572
Posted Wednesday, August 24, 2011 5:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 25, 2013 7:37 AM
Points: 1,617, 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse