How do I understand 'merge join without sort'?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » How do I understand 'merge join without...

How do I understand 'merge join without sort'?

Rate Topic

Display Mode

Topic Options

Author

Message

uook

Posted Tuesday, August 28, 2012 12:48 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 3:07 PM
Points: 27, Visits: 24

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

Grant Fritchey

Posted Tuesday, August 28, 2012 8:02 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 13,381, Visits: 25,168

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1350963

GilaMonster

Posted Tuesday, August 28, 2012 8:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 37,729, Visits: 29,990

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

uook

Posted Tuesday, August 28, 2012 10:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 3:07 PM
Points: 27, Visits: 24

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

GilaMonster

Posted Tuesday, August 28, 2012 10:19 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 37,729, Visits: 29,990

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

uook

Posted Tuesday, August 28, 2012 7:15 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 3:07 PM
Points: 27, Visits: 24

Thank you GilaMonster

You makes me clear.

Post #1351359

« Prev Topic | Next Topic »

Permissions