Re: hash join and nested loop join in an oltp query

From: Ryan <ryan.gaffuri_at_cox.net>
Date: Tue, 4 May 2004 20:34:07 -0400
Message-ID: <1dd401c43238$ad065fd0$51a36244@ryan2le36ofjce>

Thanks Dan. What I have noticed recently that in some 3 table joins of the form

one small table
two large tables

One of the large tables have multiple 'filters' that lead to just a small fraction of the records returned. If I index properly where my indexes lead with these filters, then have the join column last, I am occasionally get hash joins used by the CBO. When I hint, Nested Loops, the query resulted in significanlty worse response times and higher logical I/Os. Not quite sure why. I'm more looking for 'patterns' now than plans, because I can find the best plan. Its a matter of doing it faster.

The odd thing is that I am seeing 'range scans' with hash joins now. I can't remember ever seeing this in 8i.
----- Original Message -----
From: <dantow_at_singingsql.com>
To: <oracle-l_at_freelists.org>
Sent: Tuesday, May 04, 2004 6:01 PM
Subject: Re: hash join and nested loop join in an oltp query

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 04 2004 - 19:33:04 CDT