Saturday, October 1, 2011

JOINS


Hash join :

Hash Join will perform mostly superior when one table is big and the other is small.  Smaller table is made into hash and fed into the larger table. This is good when you have 1 Large and 1 Small table.
            A hash join (ideally) takes the smaller table (or row source), iterates over its rows and performs a hash algorithm on the columns and stores the result. After it has finished, it iterates over the other table and performs the same hashing algorithm on the joined columns. It then searches the previously built hashed values and if they match, it returns the row.

Nested Loop :

It’s a loop with in a loop. The nested loop iterates over all rows of the outer table. If there are conditions in the where clause of the SQL statement that apply to the outer table only. These rows from the inner table are either found using an index (if a suitable exists) or by doing a full table scan.


Sort Merge:

 Good in cases when Both are large tables . A merge join basically sorts all relevant rows in the first table, and also sorts the relevant rows in the second table, then merges these sorted rows.


0 comments:

Post a Comment

Auto Scroll Stop Scroll