Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Hash join not being used

RE: Hash join not being used

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Tue, 27 Jun 2000 08:19:55 -0700 (PDT)
Message-Id: <10541.110544@fatcity.com>


Patrick,

I am not so sure whether the scenario you mention here i.e., "two tables that have about the same number of rows, or both sides of the join have the same number of rows (If histograms are used)", is the only selection criteria for the use of hash joins by the optimizer. We don't claim to have seen everything if life (Oracle included), but surely have not seen what you mention. More importantly, we have seen hash joins in many many more scenarios than the one you mention here.

In our experience (Oracle 7.3.4 - 8.0.6), we have seen hash joins selected by the optimizer in quite a few scenarios, based on the values of some key init.ora settings. I have already written about that in yesterday's posting. However, in theory, hash joins are designed for and are beneficial in the following scenario:

"Applications that contain SQL that join tables, one of which is very small (e.g. 1000 rows) and the other is significantly large (e.g. 1000000 rows), and contain predicates that process a significant portion of the larger table."

Please do not read the above scenario as "hash joins will work for SQL that contains only 2 tables in the from clause". What I am referring here, in the above scenario is a "join pair", which means that hash joins will even work for multi-table joins, so long as the "join pair(s)" in the SQL have been identified to possess the aforementioned characteristic.

The aforementioned scenario/characteristic is typical in a lot of batch jobs, which is mostly where hash joins increase performance and perform better than Nested Loops. Hash Joins may also benefit some components of a transactional system, but that probably is an exception rather than the rule.

Best Regards,

Gaja.


Gaja Krishna Vaidyanatha
Director, I-O Management Products
Quest Software Inc.
(972)-304-1170
gajav_at_yahoo.com Received on Tue Jun 27 2000 - 10:19:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US