1

I have a merge join in azure data warehouse. My estimated execution plan currently shows it as a many to many join. I'd like to know if it is possible to achieve a many to one join. Currently I'm struggling to think of a way to do this as primary keys and unique constraints are unsupported. Is there anything available that would allow me to tell it that one of the tables will always contain unique values.

1

Are you getting this info from an EXPLAIN plan or the new SSMS plan functionality for Azure SQL Data Warehouse? Please provide.

In the "box product" engine (SQL Server 2017 14.0.3008.27), I can reproduce converting a many-to-many MERGE join to a one-to-many using the DISTINCT keyword against a columnstore table. I can't guarantee this would behave the same in Azure SQL Data Warehouse however and it won't apply to all queries:

SELECT *
FROM dbo.Numbers a
    INNER JOIN 
        (
        SELECT TOP 10 Number
        FROM dbo.Numbers 
        ) b ON a.Number = b.Number
OPTION ( MERGE JOIN );
GO

SELECT *
FROM dbo.Numbers a
    INNER JOIN 
        (
        SELECT DISTINCT TOP 10 Number
        FROM dbo.Numbers 
        ) b ON a.Number = b.Number
OPTION ( MERGE JOIN );

Full script available here. My results:

one-to-many

  • I'm currently using DBCC PDW_SHOWEXECUTIONPLAN as that's the only method I know of for azure sql data warehouse – Neil P Apr 5 '18 at 9:01
  • 1
    This indeed does set many to many = false. – Neil P Apr 5 '18 at 9:23
  • Have you tried the new visual plans for Azure SQL Data Warehouse? They’re nice; estimated plan only I think at the moment but I’m sure it will get better. azure.microsoft.com/en-gb/blog/… – wBob Apr 5 '18 at 12:36
  • I've had a quick play, I'm sure they will be really handy, I think there was something missing when I tried, perhaps it was the batch/row mode indicator – Neil P Apr 5 '18 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.