Forcing o HASH JOIN instead of Nested Loops
Oracle 9i
=======
I have a database environment predisposed towards FTSs. We got a batch run that we are trying to tune to perform better. Have a look at this SQL statement -
select ...
FROM tab1, tab2
where tab1.col between tab2.colstart and tab2.colend
--(the actual query is quite big, so I cannot give you the full SQL here)
Since, it is a range-join, the database goes for a Nested Loop join. However, a HASH_JOIN will make it got better since we are aiming for good throughput here. I know the machine has got enough resources to handle the hash hint. What I am not sure is how Oracle will work to implement a USE_HASH hint since there is a range-join. Maybe a better cost is just a mirage? Please advise. Thanks.