Author: Popo

With every new release we see more sophisticated optimizers and an increasing number of methods for changing the execution plans for your SQL. Oracle optimizer promises to optimize a query's execution plan. No Oracle optimizer hints should be required at all if your optimizer is really doing a good job. As the characteristics of the data in the database are change rapidly so the optimizer may not work properly. In such cases an Oracle Optimizer Hint could help you out.

Optimizer hints can be used with SQL statements to alter the execution plans. You can use the optimizer hints to determine the optimal execution plan and force the optimizer to use that optimal execution plan.

Oracle Optimizer Hints:

The use of Oracle optimizer hints involves extra code that must be managed, checked, and controlled.

Oracle optimizer hints except for the RULE hint invoke the cost-based optimizer (CBO). If you include a hint (except the RULE hint) in a statement block, then the optimizer automatically uses the cost-based approach. If you have not gathered statistics, then defaults are used.

You can use Oracle Optimizer hints to specify

Categorizing Oracle Optimizer Hints:

Hints can be categorized as follows.

Choosing query execution plan:

Oracle optimizer hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on below criteria.

Statement Block:

Oracle optimizer hints apply only to the optimization of the statement block in which they appear.

A statement block is any one of the below statements or parts of statements.

A compound query consisting of two component queries combined by the UNION operator has two statement blocks, one for each component query. If an Oracle Optimizer Hint is applied to the first component query then it applies only to its optimization and not to the optimization of the second component query.

Enclosing Oracle Optimizer Hints in a comment:

Oracle Optimizer Hint can be sent for a SQL statement to the optimizer by enclosing them in a comment within the statement.

A statement block can have only one comment containing hints and this comment can only follow the SELECT, UPDATE, or DELETE keyword. The APPEND hint always follows the INSERT keyword whereas the PARALLEL hint can follow the INSERT keyword.

Oracle supports hints contained within a statement block in below styles of comments.

Incorrect Oracle Optimizer Hints:

If you specify incorrect Oracle Optimizer Hints then Oracle ignores them but does not return an error. Below are the cases when Oracle Optimizer hints are specified incorrectly and hence ignored.

Hint not followed by Keyword:

If the comment containing Oracle optimizer hint does not follow a DELETE, SELECT, or UPDATE keyword then it is ignored.

Syntax Errors:

If the Oracle optimizer hint contains syntax errors then Oracle ignores that specific hint that has error and considers the correctly specified hints within the same comment.

Conflicting Hints:

Oracle ignores combinations of conflicting hints, but considers other hints within the same comment.

Hints in SQL Statements:

Oracle ignores optimizer hints in all SQL statements in the environments that use PL/SQL version 1, Oracle Forms 4.5, and Oracle Reports 2.5. These hints can be passed to the server, but the server ignores them.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Is Relational DBMS Dying?
   Amazing Tips to Fix Broken Oracle ASM Instance!!
   Great Tips on Recovering Controlfile without Resetlogs!
   Top-N Queries for Oracle - SQL, Some Concerns