Why does changing the declared join column order introduce a sort?
43
votes
2
answers
2743
views
I have two tables with identically named, typed, and indexed key columns. One of the them has a *unique* clustered index, the other one has a *non-unique*.
**The test setup**
Setup script, including some realistic statistics:
DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;
CREATE TABLE #left (
a char(4) NOT NULL,
b char(2) NOT NULL,
c varchar(13) NOT NULL,
d bit NOT NULL,
e char(4) NOT NULL,
f char(25) NULL,
g char(25) NOT NULL,
h char(25) NULL
--- and a few other columns
);
CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)
UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;
CREATE TABLE #right (
a char(4) NOT NULL,
b char(2) NOT NULL,
c varchar(13) NOT NULL,
d bit NOT NULL,
e char(4) NOT NULL,
f char(25) NULL,
g char(25) NOT NULL,
h char(25) NULL
--- and a few other columns
);
CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)
UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;
**The repro**
When I join these two tables on their clustering keys, I expect a one-to-many MERGE join, like so:
SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
l.a=r.a AND
l.b=r.b AND
l.c=r.c AND
l.d=r.d AND
l.e=r.e AND
l.f=r.f AND
l.g=r.g AND
l.h=r.h
WHERE l.a='2018';
This is the query plan I want:
(Never mind the warnings, they have to do with the fake statistics.)
However, if I change the order of the columns around in the join, like so:
SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
l.c=r.c AND -- used to be third
l.a=r.a AND -- used to be first
l.b=r.b AND -- used to be second
l.d=r.d AND
l.e=r.e AND
l.f=r.f AND
l.g=r.g AND
l.h=r.h
WHERE l.a='2018';
... this happens:
The Sort operator seems to order the streams according to the declared order of the join, i.e.
(Never mind the warnings, they have to do with the fake statistics.)
However, if I change the order of the columns around in the join, like so:
SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
l.c=r.c AND -- used to be third
l.a=r.a AND -- used to be first
l.b=r.b AND -- used to be second
l.d=r.d AND
l.e=r.e AND
l.f=r.f AND
l.g=r.g AND
l.h=r.h
WHERE l.a='2018';
... this happens:
The Sort operator seems to order the streams according to the declared order of the join, i.e. c, a, b, d, e, f, g, h, which adds a blocking operation to my query plan.
**Things I've looked at**
* I've tried changing the columns to NOT NULL, same results.
* The original table was created with ANSI_PADDING OFF, but creating it with ANSI_PADDING ON does not affect this plan.
* I tried an INNER JOIN instead of LEFT JOIN, no change.
* I discovered it on a 2014 SP2 Enterprise, created a repro on a 2017 Developer (current CU).
* Removing the WHERE clause on the leading index column does generate the good plan, but it kind of affects the results.. :)
**Finally, we get to the question**
* Is this intentional?
* Can I eliminate the sort without changing the query (which is vendor code, so I'd really rather not...). I can change the table and indexes.
Asked by Daniel Hutmacher
(9173 rep)
Aug 16, 2018, 11:58 AM
Last activity: Aug 16, 2018, 09:58 PM
Last activity: Aug 16, 2018, 09:58 PM