Today database vendors are more willing to provide a means for the end user to influence the decision of which execution plan to use for a SQL statement. They provide this because database optimizers cannot guarantee that they the will generate the best execution plan for a given SQL statement. However, when you do apply Optimization Hints you might find that sometimes the specified Hints may not affect the database SQL optimizer’s decision. The reason that the optimizer does not select the execution plan following the Hints direction is that your SQL statement’s syntax prevents the database SQL optimizer from using the Hints.
Let’s take a look at the following SQL statement and execution plans with and without a Hint:
In reviewing the execution plans, this example shows you that the USE_NL hint does not cause the Oracle SQL optimizer to generate a Nest Loop join for this SQL statement. It is actually quite often that the database SQL optimizer will not follow your instruction due to the limitation of the SQL syntax. For complicated SQL statements, the situation is even more complex as we may not be able to tell whether the Hints will be used or how good the result will be if a Hint is applied. That is why Quest SQL Optimizer takes a different approach and does not follow the knowledge oriented SQL tuning approach. The SQL Transformation engine in Quest SQL Optimizer will try most of the possible combinations for rewriting the SQL syntax combined with applying optimization Hints to explore the potential of a database SQL optimizer like the following diagram illustrations:
The first step of this SQL Transformation engine transforms the original SQL statement and produces a group of alternative SQL statements where all the alternative SQL statements still provided the exact same results. Then, Quest SQL Optimizer rewrites each newly created SQL statement to produce another group of alternatives. The engine continues rewriting each alternative until all the SQL statements cannot be rewritten any further or until quotas are reached.
After the SQL Transformation engine has exhausted rewriting the syntax of the SQL statement, the optimization hints are applied to the original SQL statement and each of the SQL alternatives until all selected hints have been applied to all the SQL alternatives or until the quotas are reached.
The SQL Transformation engine releases you from the human trial and error effort and replaces it with a sophisticated computer algorithm. The result is a collection of SQL alternatives which are a combination of rewritten syntax and the application of Hints. What you need to do is to test the performance of each alternative SQL statement to find a better one to replace your original poor performing SQL statement.