Re: Hash_join_enabled: what if equals FALSE

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Mon, 03 May 1999 09:17:41 -0700
Message-ID: <372DCC25.824A0C1F@us.oracle.com>

From memory, Sybrand is correct. The HASH_JOIN_ENABLED parameter came in 7.3, and I don't believe the RBO was enhanced to understand the use of hash joins.

HTH. Pete

Sybrand Bakker wrote:

> Hi Michel
> Joins can be performed in one of three manners
> nested loops (retrieve one row from a master table, subsequently retrieve
> all rows from the corresponding detail table)
> merge-join (join is performed by executing a sort, all applicable rows are
> buffered in memory)
> and hash-join (a hash key is being calculated and this key is used for
> sorting purposes. In merge-join the complete select expression is sorted)
> Turning the parameter off simply means you can't use it.
> Now this hash-join mechanism might (?!) be supported by the cost based
> optimizer only. I have a feeling if you are running rule based (this happens
> with optimizer_mode = choose and if you don't have statistics in the
> dictionary) it won't be used anyway.
>
> Hth,
> Sybrand Bakker, Oracle DBA
> Michel Gauthier wrote in message ...
> >Hi all,
> >
> >After having a few ORA-600 [15851] [6] [6] etc... I searched the Oracle Web
> >support to find out that they sometimes suggest to turn off the
> >HASH_JOIN_ENABLED parameter. This parameter seems to appear with 7.3 and
> I'm
> >not familiar with it. Anyway, I turned it off, and it seems to actually
> >work. Fine.
> >
> >Unfortunately, that does not say what this parameter is useful to, neither
> >which are the impacts to turn it off (except it workaround my problem!). I
> >surfed the Oracle doc and DEJANEWS without success.
> >
> >Can anybody tell us about this parameter. That would be nice.
> >
> >TIA.
> >
> >Michel Gauthier
> >
> >

Regards

Pete

Pete Sharman                              Email: psharman_at_us.oracle.com
Project Manager                           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

Oracle Corporation

SQL> select standard_disclaimer, witty_remark 2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

Received on Mon May 03 1999 - 11:17:41 CDT