Friday, September 18, 2015

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


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.


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;

No comments: