Join HINT

 

JOIN HINT in SQL, What and When:

Joint hint specifies that the query optimizer enforced the join strategy before joining two tables.

 By default SQL server takes the best execution plan before joining table's objects, be careful using joint hint in your SQL statement until you know them very well.

 The hint is using in join to increase the performance of execution of statement. But sometimes it gives you reverse action also.

It applies to SELECT, DELETE and UPDATE statements.

Syntax is

<join_hint> ::=

     { LOOP | HASH | MERGE | REMOTE }

 

In this article I try to explain about the different types of hint used in SQL server.

LOOP Joint Hint:

This is the simplest form of LOOP Join. Suppose you have Tabe1 Join with Table2 by LOOP join hint.

The algorithm is motioned bellow.

FOR  each row R1 in the outer table
          FOR each row R2 in the inner table
                  IF R1 joins with R2
                       return (R1, R2)

Example:

SELECT a.*, b.*

FROM   Table1 a

       INNER LOOP JOIN Table2 b ON a.roll=b.roll

WHERE  a.roll<=12

 

HASH Joint hint:

Hash joins parallelize and scale better than any other join and are great at maximizing throughput in data warehouses. 

The hash join executes in two phases: build and probe.  During the build phase, it reads all rows from the first input (often called the left or build input), hashes the rows on the equijoin keys, and creates an in-memory hash table.  During the probe phase, it reads all rows from the second input (often called the right or probe input), hashes these rows on the same equijoin keys, and looks or probes for matching rows in the hash table.  Since hash functions can lead to collisions (two different key values that hash to the same value), we typically must check each potential match to ensure that it really joins.

The algorithm is motioned bellow.

FOR  each row R1 in the build table
         BEGIN
                  calculate hash value on R1 join key(s)
                  insert R1 into the appropriate hash bucket
         END
FOR each row R2 in the probe table
         BEGIN
                  calculate hash value on R2 join key(s)
                  FOR each row R1 in the corresponding hash bucket
                          IF R1 joins with R2
                              return (R1, R2)
         END

Example:

SELECT a.*, b.*

FROM   Table1 a

       INNER HASH JOIN Table2 b ON a.roll=b.roll

WHERE  a.roll<=12

MERGE Join Hint:

The merge join works by simultaneously reading and comparing the two sorted inputs one row at a time.  At each step, we compare the next row from each input.  If the rows are equal, we output a joined row and continue.  If the rows are not equal, we discard the lesser of the two inputs and continue.  Since the inputs are sorted, we know that we are discarding a row that is less than any of the remaining rows in either input and, thus, can never join.

The algorithm is motioned bellow.

get first row R1 from input 1
get first row R2 from input 2
WHILE  not at the end of either input
    BEGIN
            IF R1 joins with R2
                BEGIN
                         return (R1, R2)
                         get next row R2 from input 2
                NED
            ELSE IF R1 < R2
                          get next row R1 from input 1
                      ELSE
                          get next row R2 from input 2
     END

 

Example:

SELECT a.*, b.*

FROM   Table1 a

       INNER MERGE JOIN Table2 b ON a.roll=b.roll

WHERE  a.roll<=12

 

REMOTE Joint Hint:

It is useful when the join left table is a local table and the right table is a remote table. It is used when the left table is fewer rows then the right table.

REMOTE is only used with Inner join.

Example:

SELECT a.*, b.*

FROM   Table1 a

       INNER REMOTE JOIN Table2 b ON a.roll=b.roll

WHERE  a.roll<=12

 

One of my friends Mr. Kunal (MVP), gives me opportunity to publish my article "JOIN Hint in SQL" to his web as guest.

The link is mentioned bellow.

http://www.kunal-chowdhury.com/2012/01/join-hint-in-sql-guest-post-by-joydeep.html

Posted by: MR. JOYDEEP DAS