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

How do I understand 'merge join without sort'? Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 12:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 8, 2013 7:40 PM
Points: 27, Visits: 25
Execution plan showed me merge join without sort.



Join was FULL JOIN with no equality in ON clause.

I can't understand this.

SQL Version is "Microsoft SQL Server 2008 (RTM) - 10.0.1600.22"

Recall script is as below.


CREATE TABLE tblA ( aa INT )
;
INSERT INTO tblA( aa )
SELECT TOP 10 ROW_NUMBER() OVER ( ORDER BY object_id, column_id )
FROM sys.all_columns
;
CREATE TABLE tblB ( aa INT, bb INT )
;
DECLARE @ii INT = 1;
BEGIN TRAN
WHILE @ii <= 50
BEGIN
INSERT INTO tblB( aa, bb )
SELECT TOP 2000 @ii, ROW_NUMBER() OVER ( ORDER BY object_id, column_id )
FROM sys.all_columns
;
SET @ii = @ii + 1;
END
COMMIT TRAN

SELECT tblA.aa, tblb.bb
FROM tblA FULL JOIN tblB ON tblA.aa < tblB.aa
;
Post #1350706
Posted Tuesday, August 28, 2012 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:27 PM
Points: 13,776, Visits: 28,178
Take a look at the properties on the other operations. I'll bet you they're ordered operations. This is all good. An ordered seek or scan with a merge can be the fastest way to move large amounts of data within a query.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1350963
Posted Tuesday, August 28, 2012 8:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 39,983, Visits: 36,351
It's an Ordered False operation. The thing is, that's actually a cross join. There's no join predicate at all. All rows qualify for that predicate so there's no ordering necessary, every row joins to every row.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1351002
Posted Tuesday, August 28, 2012 10:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 8, 2013 7:40 PM
Points: 27, Visits: 25
First of all, thank you Fritchey.

I recheck execution plan but I can't find any kind of sorting operations.

As you can see, table scan is used because there is no clustered index and sorting operator isn't be shown.

Isn't it weird?
Post #1351083
Posted Tuesday, August 28, 2012 10:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 39,983, Visits: 36,351
Not weird at all. There's no need to sort as all rows match all rows with that join you've specified. Look at the join columns in the merge join () = ()

This is essentially what your query boils down to:
SELECT tblA.aa, tblb.bb
FROM tblA FULL OUTER JOIN tblB ON 1=1

For a merge join, the two inputs need to be sorted on the join column, except there is no join column, so any order whatsoever will do.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1351090
Posted Tuesday, August 28, 2012 7:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 8, 2013 7:40 PM
Points: 27, Visits: 25
Thank you GilaMonster

You makes me clear.
Post #1351359
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse