Skip site navigation (1) Skip section navigation (2)

Re: RIGHT/FULL OUTER hash joins (was Re: small table left outer join big table)

From: Jie Li <jay23jack(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: RIGHT/FULL OUTER hash joins (was Re: small table left outer join big table)
Date: 2010-12-30 16:20:36
Message-ID: AANLkTikfbkea=_uSR-XfWMo_hOAidL_yq7iE3zx06TTP@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, Dec 30, 2010 at 11:50 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Dec 30, 2010 at 10:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > I had an epiphany about this topic, or actually two of them.
> >
> > 1. Whether or not you think there's a significant performance reason
> > to support hash right joins, there's a functionality reason.  The
> > infrastructure for right join could just as easily do full joins.
> > And AFAICS, a hash full join would only require one hashable join
> > clause --- the other FULL JOIN ON conditions could be anything at all.
> > This is unlike the situation for merge join, where all the JOIN ON
> > conditions have to be mergeable or it doesn't work right.  So we could
> > greatly reduce the scope of the dreaded "FULL JOIN is only supported
> > with merge-joinable join conditions" error.  (Well, okay, it's not
> > *that* dreaded, but people complain about it occasionally.)
>
> Yeah, that would be neat.  It might be a lot faster in some cases, too.
>

Yeah, PostgreSQL should have this great feature.

Actually Oracle 10g already has the right hash join,
http://dbcrusade.blogspot.com/2008/01/oracle-hash-join-right-outer.html

 And Oracle 11g has the full hash join.
http://www.dba-oracle.com/oracle11g/oracle_11g_full_hash_join.htm

Haven't checked whether other DBMS have this feature.

Thanks,
Li Jie

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-12-30 16:35:05
Subject: Re: RIGHT/FULL OUTER hash joins (was Re: small table left outer join big table)
Previous:From: Robert HaasDate: 2010-12-30 16:07:55
Subject: Re: Old git repo

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group