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.
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.
Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.
Do you want to learn Oracle Database Performance Tuning detailed, then read the following Tutorial Series.
2,790 views last month, 2 views today