Disabling hash join in a procedure One of my procedures failed because it ran out of temp tablespace. 700 GB was not enough for the procedure. I looked on it, and found it was caused by an inefficient join type, chosen by the optimizer. It works well with nested loops, and is fast enough. So, I disabled the hash and merge joins inside the procedure.
I have a question. If I disabled this hash join, would that change affect other procedures?
Would it affect procedures which calls modified procedure?
And would it affect procedures which are called by the modified procedure?
It depends on how you did it.
With a plain SET, you would change the parameter for the whole session.
With SET LOCAL, you would change the parameter for the complete database transaction.
The best way would be to modify the procedure so that the setting is only active for the duration of the execution of the procedure:
ALTER PROCEDURE procname SET enable_hashjoin = off;

Let me add that this is only a last ditch measure: it would be much better if you could fix the bad row count estimates that lead to the bad choice of join strategy.