Hash Joins and Point based Joins

Question

Hash Joins and Point based Joins

Loner

SSC-Insane

Points: 21279
More actions
May 19, 2006 at 2:41 pm

#168489

I heard SQL server 2005 had Hash joins. Does anyone use it? Can someone give me an example ? The same as Point based joins.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

lakusha SSCommitted Points: 1857 More actions · Answer 1

Your "heard" half right. SQL2k AND SQL2k5 both support hash joins (among others). Everybody use them, although most people don't know it

The query optimizer decides what is best for you. If you look at a query plan, you will see it. If you have a slow running query, look at its query plan and you might find a hash join in there. It is the worst join type. You see it when you have a badly written query or poor indexes (ok, ok, not only then)

You can force the optimizer to use it:

Select *

from MyTable

inner hash join MyTable2 b on a.PK=b.FK

AFAIK, there is not such thing as a point base join unless it lives by another name. Have you tried the funny join?

Select *

From MyTable

Funny join MyTable2 b on a.PK=b.FK

it execute without errors

It IS friday

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 2

I am sure we do not have funny join and I do not want to try as if it execute without errors it will bring my confidence level to 0 and if it throws an error I will feel really stupid.

Will someone really try executing funny join ?

Kindest Regards,

Amit Lohia

lakusha SSCommitted Points: 1857 More actions · Answer 3

I don't care about feeling stupid once in a while, it keeps my ego from bursting the seams

Think about it:

Select *

From MyTable

Funny join MyTable2 b on a.PK=b.FK

is the same as

Select *

From MyTable Funny

join MyTable2 b on a.PK=b.FK

is the same as

Select *

From MyTable as [Funny]

join MyTable2 b on a.PK=b.FK

Loner SSC-Insane Points: 21279 More actions · Answer 4

I am sorry I put in the wrong name - it was supposed to be Pointer-based joins (not funny join).

It joins tables T1 and T2 by tracing explicit links between related data items. It found primarily in object databases in the form of object identifier (OIDs).

The links pre-compute the join path between data items. The cost of storing and maintaining links can be very expensive. However it is very efficient because links between related entities are explicit.