2

I am testing Merge join in sql server. I have an INNER JOIN and force the optimizer to do MERGE JOIN:

  • ID in Personal Table is a Primary key
  • ID in Abteilung Table is a primary key
  • There is no other Indexes in these tables

     select * from  [dbo].[Personal] as P inner join [dbo].[Abteilung] as A  
     on  P.[ID]= A.[Personal_ID]   OPTION (MERGE JOIN) 
    

then Optimizer uses this plan to run my query:

enter image description here

I would like to force optimizer to use index seek instead of Table Scan

What I did to achieve this aim:

  • Defining a grouped index in the Table Abteilung on columns ID, Personal_ID. If I run the query, the execution plan is changes like this:

enter image description here

here I have Clustered index scan, but not clustered index seek

  • Using FORCESEEK hint. If I am using this hint, an error occures by running the query:

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

  • If I add a where clause in the query, then the query would be change to:

      select * from  [dbo].[Personal] as P inner join [dbo].[Abteilung] as A  
      on P.[ID]= A.[Personal_ID] where A.[Personal_ID]=2 
      OPTION (MERGE    JOIN)
    

    enter image description here in this situation I have only Clustered index scan.

My question:

How do I change my query to have Clustered Index Seek in the bottom line?

  • Why do you need to do that? What problem are we trying to solve here? – Tom V Nov 24 '15 at 14:01
  • It is only a test for me, I would like to know if it is possible ;-) – Kaja Nov 24 '15 at 14:02
  • It is only an estimate but they are both 50%. Not sure you are solving a real problem here. – paparazzo Nov 24 '15 at 17:11
6

To get a clustered index seek, you'd need a clustered index that supports your filter (e.g. leading key would have to be Personal_ID, not ID).

You can't force a seek if there's no index with a leading column of Personal_ID that supports the filter.

This does not mean you should change the existing clustered index, unless this is the only query you ever run against the table.

And while you could create a non-clustered index with Personal_ID as the key column, a seek on that index might not be what you want anyway - since you're using SELECT * (are you really sure you need all columns from both tables?), it will need to go fetch the rest of the columns from the clustered index anyway, and if there are more than some number of rows returned, at some point a seek (well, what would amount to a range scan disguised as a seek) + lookups will be more expensive than a regular scan.

Why do you think you need a seek here? How many rows does the query return, how wide are they, and how long does it take? Is this just educational, or are you under the assumption that a seek will always perform better than a scan? (It won't, btw.)

Some useful reading:

  • And even if the clustered index was changed to get a seek the plan still wouldn't have a merge join as it then just becomes a cross join of the two seeked results. – Martin Smith Nov 24 '15 at 20:11
1

Without having created any tests I would guess that it's not possible to seek an index without having seek predicates, especially not if you use SELECT * and get all table columns.

I would try to filter on an index-column that is part of the index you want to seek in and additionally retrieve only columns that are covered by this index.

Documentation explains that Merge join typically scans or alternatively sorts it afterwards. Read: https://technet.microsoft.com/en-us/library/ms190967(v=sql.105).aspx

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.