Re: hash join and nested loop join in an oltp query
- From: dantow@xxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 4 May 2004 17:01:57 -0500
I have generally found that when hash joins win for sensible OLTP queries,
queries that return few enough rows to be useful online, you are either looking
at quite small tables (in which case either join method will likely be OK, but
hash may win), or your nested-loops join order was wrong. If you get the join
order right, you may be surprised how rarely hash joins offer a significant
advantage, especially with OLTP queries. The key is that with the right join
order, you usually get down to (and stay down at) few rows very early in the
execution plan (usually right from the driving table), at which point hash
joins would be slower except in the case of a really tiny table, where hash
joins might be *slightly* (but not significantly) faster. (Note that I use the
word "significant" - there are abundant cases where hash joins to small tables
are *very slightly* better, but with the right join order, these joins are
generally fast with either join method.)
Thanks,
Dan Tow
650-858-1557
www.singingsql.com
Quoting ryan.gaffuri@xxxxxxx:
> I Have seen some multi-table joins which return a small subset of records
> using hash joins. To test results, I hinted them to do nested loop joins and
> found response time to be alot slower and consistent gets to be much higher.
>
> what types of cases make hash joins superior to nested loops? I see them alot
> in batch processing when I need to join most of the rows, but I am see them
> now when I return a small subset of rows.
>
> has anyone experienced this?
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
