I keep hearing that using hints is a rabbit hole and "using hints is a dangerous path to go down".
But. An example from a production environment where it's mission critical that a particular query should perform: we have a query that always uses hash join and it should - but from time to time it can happen (let's not talk about the reasons, there can be many that our team has no effect on) that cardinality estimates become way off and the CBO decides to use nested loops which in this case considerably degrades performance.
What's wrong with using a USE_HASH hint to ensure that this query will always use hash join as it should? My understanding is that at worst it will change nothing, at best it will revert the plan to the right one.
Thanks!