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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-12-30 16:35:05 | Re: RIGHT/FULL OUTER hash joins (was Re: small table left outer join big table) |
Previous Message | Robert Haas | 2010-12-30 16:07:55 | Re: Old git repo |