Index hints are like football commentary.
The word Hint already presents a challenge when referring to databases in the year 2025. Banks are becoming increasingly intelligent, making more and more accurate choices. Many internal changes have occurred, especially in the versions that operate in the cloud.
I still use Hints, especially in Oracle, when dealing with old code and cannot make drastic changes, particularly when the client does not want to go through the approval phase of new routines immediately.
However, the only HINT completely banned for me is the dreaded INDEX. The optimizers are not only smarter than us, they have time on their side.
INDEX hints are like football commentary, they deteriorate quickly.
I remember a commentator who called Rivaldo a "clumsy player" during his time at Corinthians.
Recently, I worked on a project full of index hints. My first task was to examine the queries to identify which indexes should be created and which ones didn't make sense. Quickly, the hints set by the previous programmer began to become an obstacle.
But that's not all, if the optimizer does not select your estimated index, consider it as an opportunity to improve your query, or as a sign that something is not working correctly in the database management, such as the collection of index statistics, something quite common in ON-PREMISE databases.
I have participated in numerous legacy maintenance projects where HINTS were used because at some point the index was not being utilized, due to poor database configuration, the indices "lost their meaning" for the optimizer. Most of the time, a chat with the DBA was enough, but sometimes I had to escalate the issue.But 90% of the time the DBA actually appreciates the "nudge."
If you have no other option but to resort to the Index Hint, can´t change your query, cannot prove that the problem is due to database maintenance, do not have access to the DBA, at least document the situation in the code, noting the time, place, and reason for the unfortunate circumstance of having to resort to the index hint, do not trivialize the situation.
