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 -----------------------------------------------------------------