August 28, 2012 at 12:48 am
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
;
August 28, 2012 at 8:02 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 28, 2012 at 8:46 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2012 at 10:14 am
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?
August 28, 2012 at 10:19 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2012 at 7:15 pm
Thank you GilaMonster
You makes me clear.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply