Interested to Buy Any Domain ? << Click Here >> for more details...
Answer Posted / guest
The Hash join will be used, if there are no adequate indexes
on the joined columns. This is a worst situation. In this
case, hash table will be created. Hash join is most
efficient when one of the tables is significantly differ in
size than another one.
The query optimizer makes a Hash join in two phases: build
and probe. So, Hash join has two inputs: the build input and
the probe input.
On the build phase, hash table will be created by scanning
each value in the build input and applying the hashing
algorithm to the key.
Let me to describe Hash join on the example with two tables.
Look at this example:
if object_id('dbo.Table1') is not null drop table Table1
GO
CREATE TABLE Table1 (id int, Name char(10))
GO
if object_id('dbo.Table2') is not null drop table Table2
GO
CREATE TABLE Table1 (id int, Name char(20))
GO
DECLARE @i int
SELECT @i = 1
WHILE @i < 1000
BEGIN
INSERT INTO Table1 VALUES (@i, LTRIM(str(@i)))
SELECT @i = @i + 1
END
GO
DECLARE @i int
SELECT @i = 1
WHILE @i < 1000
BEGIN
INSERT INTO Table2 VALUES (@i, LTRIM(str(@i)))
SELECT @i = @i + 1
END
GO
SET SHOWPLAN_TEXT ON
GO
SELECT a.Name FROM Table1 a INNER JOIN Table2 b
ON a.Name = b.Name
GO
SET SHOWPLAN_TEXT OFF
GO
This is the result:
StmtText
-------------------------------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([a].[Name])=([b].[Name]),
RESIDUAL:([a].[Name]=[b].[Name]))
|--Table Scan(OBJECT:([pubs].[dbo].[Table1] AS [a]))
|--Table Scan(OBJECT:([pubs].[dbo].[Table2] AS [b]))
The smaller table will be build input, the other - probe
input. Field Name (column that joins the tables) is called
hash key. The hash table consists of linked lists called
hash buckets. The result of using a hash function on a hash
key is called hash value. Hash value and RID (row
identifier) will be placed into hash table.
Hash value must be smaller than hash key. So, query
processor economies on the size of the hash table. The real
example of hashing is notebook. You can open notebook on the
appropriate letter and scan all surnames on this letter to
find necessary ones. So, notebook is the example of hash
table, and pages on the appropriate letter are the example
of hash bucket.
During the probe phase, the entire probe input is scanned,
and for each probe row computes the same hash value on the
hash key to find any matches in the corresponding hash bucket.
There are two main kinds of Hash join:
# In-memory Hash join
# Grace Hash join
In-memory Hash Join will be used if entire build input can
be placed into memory.
Grace Hash join will be used if your server has not enough
memory to hold the entire build input. In this case, query
processor proceeds Hash Join in several steps (hash table
will be divided into multiple partitions and relevant
partition will be loaded as need).
Because the query optimizer usually selects the best
execution plan for a given select statement, it is not
necessary to enforce the desirable join type, but sometimes
it can be useful. You can enforce the desirable join type by
using the OPTION clause.
This is the example to enforce Hash join:
USE pubs
GO
SET SHOWPLAN_TEXT ON
GO
SELECT a.au_id FROM authors a JOIN titleauthor b
ON a.au_id = b.au_id OPTION (HASH JOIN)
GO
SET SHOWPLAN_TEXT OFF
GO
This is the result:
StmtText
-----------------------------------------------------------------------------------------------
SELECT a.au_id FROM authors a JOIN titleauthor b
ON a.au_id = b.au_id OPTION (HASH JOIN)
(1 row(s) affected)
StmtText
-----------------------------------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([a].[au_id])=([b].[au_id]),
RESIDUAL:([a].[au_id]=[b].[au_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[authors].[aunmind]
AS [a]))
|--Index
Scan(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [b]))
(3 row(s) affected)
| Is This Answer Correct ? | 2 Yes | 4 No |
Post New Answer View All Answers
what is transaction? : Sql dba
What is nosql db?
How do I truncate a sql log file?
Which is faster union or join?
Which are the different case manipulation functions in sql?
What is a left inner join?
What is the order of sql select?
Can pl sql procedure have a return statement?
How long does it take to learn pl sql?
What are types of joins?
Explain the the update statement in sql?
Table1: Col1 col2 1 2 10 3 4 89 5 6 Table:2 Col1 col2 3 2 9 5 4 7 6 87 With the help of table1 and table2 write a query to simulate the fallowing results. Output1: Col1 col2 1 2 2 3 3 4 4 5 5 6 Output2: Col1 col2 2 3 10 4 5 89 6 7 1.Write query for single row to multiple row using sql statements. Eg:a,b,c,d,e,f Change to A B C D E F 2. Write query for multiple row to single row using sql statements. Eg2 A B C D E F Change to Eg:a,b,c,d,e,f Table1: Col1 col2 8 5 2 9 4 2 5 1.Write a query to select all the rows from a table1,if the value of A is null then corresponding B’s value should be printed in A’s value.if the value of A is null in that table then corresponding B’s value should be printed as 30. 2. write a query to find the sum of A and B .display the max among both. 3.write a query to find total number of rows from table 1. Note: if any column value is null in a row then that row should be considered as 2 rows. 4.write a query to display all the records of table1 except A containg 2 as well B containg 5. 5.rewrite the fallowing without using join and group by. Select b.title,max(bc.returneddate –bc.checkoutdate)” mostdaysout” From bookshelf_checkout bc, Book shelf B Where bc.title(+)=b.title Group by b.title. 6.rewrite fallowing query Select id_category from category_master X where exists (select 1 from sub_category Y where X.id_category=Y.id_category) Customer: Name phone1 phone2 phone3 bitwise A 23456 67890 12345 --- B 67459 89760 37689 --- Don’t_call Col1 67890 37689 1.q) update the customer table of bitwise with 1 or 0. Exists in don’t_call table menas show -1 Other wise -0. Output. Name bitwise A 010 B 010
What is meant by <> in sql?
What are the types of optimization?
what is the difference between group by and order by in sql? : Sql dba
