Answer / guest

Microsoft SQL Server 7.0/2000 supports three types of join
operations:

# Nested-Loop joins
# Merge joins
# Hash joins

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)


INNER JOIN: inner join is used to retrieve matched data from
2 or more tables. inner join used "=" sign.

Answer / ram

when the joining tables having huge data or selective columns don't have index at that
hash will occured automatically by using optimization

Answer / anil kumar jaiswal

Hash Join : When there is no index available on join columns and both table has huge amount of data then oracle optimizer will pick hash join automatically.

we also have use_hash hints in oracle to instruct oracle optimizer to perform hash join on the given Query.

check explain plan for any query to see the join performed on that particular query.

What is online transaction processing (oltp)?

0 Answers  

---

I have the table like this S.No Name ID 01 Xyz 123 I want the result as 01Xyz123 How to write the query to retrieve the entire row data in a single column?

1 Answers  

---

i have a customer table. trans_id trans_date trans_amt debit_credit_indicator 001 01-JAN-13 1099 cr 001 12-JAN-13 500 db 002 24-FEB-13 400 db 002 23-MAR-13 345 cr 001 18-APR-13 800 cr 002 15-MAR-13 600 db 001 12-FEB-13 200 cr i want like this output. trans_id trans_amt debit_credit_indicator i want get highest credit amount and lowest credit amount and highest debit amount and lowest debit amount for each trans_id. pls give me answer. i want urgent

3 Answers  

---

What is function and procedure in pl sql?

0 Answers  

---

How do you concatenate in sql?

0 Answers  

---

How do I clear the screen in sql plus?

0 Answers  

---

What is denormalization in a database?

0 Answers  

---

what is isam? : Sql dba

0 Answers  

---

Does a primary key have to be a number?

0 Answers  

---

Hi All, I am new to both this blog and technology. I was able to see a response for one of the questions on triggers as below. I would like to know why are we using " if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then" instead, can't we use " if sysdate = 'sunday' then". I can understand the use of "rtrim", but dont know y v r using to_char. I have seen this in many cases but did not get a convincible explaination. Please help me with this and do excuse if this question sounds silly. Thanks in advance...... create or replace trigger trg_sun before insert on <table name> begin if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then raise_application_error(-20345,'no transaction in sunday'); end if; end trg_sun;

2 Answers  

---

How to rename a table?

0 Answers  

---

Can a view be mutating? If yes, then how?

0 Answers  

---