Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Paul White » The Right Kind Of Join
Add to briefcase
29 posts, Page 2 of 3
««123»»

The Right Kind Of Join Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Kwex
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: Friday, October 12, 2012 7:03 AM
Points: 249, Visits: 224
Nice! Learnt something new today about hint limitations on RIGHT joins
Post #1164525
Andrew Watson-478275
Posted Wednesday, August 24, 2011 3:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:44 AM
Points: 953, Visits: 1,875
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
L' Eomot Inversé
Posted Wednesday, August 24, 2011 3:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 7,096, Visits: 7,156
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1164530
Paul White
Posted Wednesday, August 24, 2011 4:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 10,989, Visits: 10,534
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
Paul White
Posted Wednesday, August 24, 2011 4:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 10,989, Visits: 10,534
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
Kiara
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
rfr.ferrari
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: Thursday, May 02, 2013 10:51 AM
Points: 1,219, Visits: 13,507
nice question!

rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1164572
Britt Cluff
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,558, Visits: 247
Learned something new again today, thanks.

http://brittcluff.blogspot.com/
Post #1164575
Ernie Schlangen
Posted Wednesday, August 24, 2011 5:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 6:32 AM
Points: 1,592, Visits: 352
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
Kameswari Aravindh
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
29 posts, Page 2 of 3
««123»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.