Oracle Database 10g R2 -- Disable Hash Joins Usage for Queries

Problem

Sometimes hash joins may lead to undesirable situations as, for example, ORA-600 errors, inconsistent query results in parallel mode, extensive usage of  temp segments, suboptimal execution plans and many others. 

In these situations it may be required to disable hash joins, temporary for testing purposes or any other needs.

Solution

To disable hash joins at query level use the opt_param hint:

/*+ opt_param('hash_join_enabled','false') */

To disable hash joins at the session level use the underscored parameter:

alter session set "_hash_join_enabled"=FALSE;