Hi,

I will explain What is the Optimizer Hints and How to Use Hints in Oracle in this article.

Optimizer Hints in Oracle

Normally, Oracle database selects a best execution plan for each SQL Statement because of Optimizer. If you don’t know What is the Optimizer and how Oracle Optimizer works, read the following article before this.

Optimizer Access Paths (Table and Index Scan) in Oracle | Oracle Database Performance Tuning Tutorial -7

Sometimes These execution plans are actually not the best and there is a better plan than Optimizer’s. But Optimizer doesn’t select that plan and you should instruct it forcibly.

Hints are used to alter execution plans and force the optimizer to use the optimal execution plan. Oracle Hints influence the Optimizer decisions and  select a certain execution plan based on the specific criteria.

How to Use Hints in Oracle

For example; Your query is not using Index, but it should use it. You can use the hint in this case to force the Optimizer to use this index.

The usage of Hints are as follows.

You can use the Index hint as follows.

SELECT company_name FROM companies c WHERE Company_ID = 1;

You can add  /*+ index(c COMP_NDX) */  clause the SQL Statements as follows to force using COMP_NDX  Index.

SELECT /*+ index(c COMP_NDX) */ company_name FROM companies c WHERE Company_ID = 1;

You can use the Parallel hint to force parallel running the SQL Statement

SELECT /*+ PARALLEL(employees 8) */ e.last_name
FROM   employees e
WHERE  department_id=63;

You need to place hints immediately after the first SQL keyword of a statement block  (MERGE, SELECT, INSERT, DELETE, or UPDATE)

Each SQL Statement can have only one hint comment, but it can contain multiple hints as follows.

SELECT /*+ LEADING(emp2 emp1) USE_NL(emp1) INDEX(emp1 emp_emp_id_pk) USE_MERGE(jh) FULL(jh) */
emp1.first_name, emp1.last_name, jh.job_id, sum(emp2.salary) total_sal
FROM employees emp1, employees emp2, job_history jh
WHERE emp1.employee_id = emp2.manager_id
AND emp1.employee_id = jh.employee_id
GROUP BY emp1.first_name, emp1.last_name, jh.job_id
ORDER BY emp1.first_name;

Hint Types in Oracle

There are 4 types of hints in the Oracle database as follows.

• Single-table hints ( one table or view ) such as INDEX and USE_NL hints
• Multitable hints ( Lots of tables or views ) such as LEADING hint.
• Query block hints ( a single query block ) such as STAR_TRANSFORMATION and UNNEST hints
• Statement hints ( entire SQL statement ) such as ALL_ROWS hint.

Hints Categories are as follows.

• Optimizer Approaches ( ALL_ROWS and FIRST_ROWS )
• Parallel execution
• Join orders
• Join operation
• Access paths ( Index Access, Full access and etc.. )
• Query transformations 

Oracle Hints and Definitions

Most important and popular Oracle hints and their definitions are as follows.

INDEX: use the specified index for the related table. If your query is not using the Index, you can use this hint to force using it.

FULL:  it is used for a full table scan

PARALLEL: Use parallelism for the related table. You can use this hint especially for the large tables and high load SQL Statements.

USE_NL : Use the Nested loop join for the specified table, If a large table is joined with a small table.

USE_HASH:  Use the Hash join for the specified table, If a large table is joined with a large table.

ALL_ROWS: Selects All Rows for the best throughput.

FIRST_ROWS(n) : it is used to optimize an individual SQL statement for fast response.

CLUSTER:  it is used for using a cluster scan

HASH: it is used for using a hash scan

ROWID: it is used for accessing via ROWID

INDEX_ASC: it is used for Scanning an index in ascending order

INDEX_COMBINE: it is used for choosing a bitmap access path.

INDEX_JOIN: it is used for the optimizer to use an index join as an access path.

INDEX_DESC: it is used for an index scan in descending order.

INDEX_FFS: it is used for a fast-full index scan.

INDEX_SS: it is used for an index skip scan.

NO_INDEX: it is used for not allowing indexes usage.

APPEND: it is used for direct-path INSERT

NOAPPEND : it is used for regular INSERT

CURSOR_SHARING_EXACT: it is used to Prevent replacing literals with bind variables

CACHE: it is used for Overrides the default caching specification of the table

PUSH_PRED: it is used for Pushes join predicate into view

PUSH_SUBQ: it is used for Evaluates nonmerged subqueries first

DYNAMIC_SAMPLING: it is used for dynamic sampling Control.

I will continue to explain Performance tuning tutorial in the next articles.

Next post link about Performance Tuning Tutorial is as follows.

What is the SQL Performance Analyzer and SPA Report in Oracle | Oracle Database Performance Tuning Tutorial -17

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

SQL Tuning Tips and Tricks Tutorial in Oracle -3

Do you want to learn Oracle Database Performance Tuning detailed, then read the following Tutorial Series.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database