How to change the access path of query optimizer using hint

We can change the access path of our SQL statement by placing an access path hint in our query. The two most common access path hints to place in a query tell the Oracle optimizer to do a full table scan, or use an index.

Often, the optimizer does a good job of choosing the best or at least a reasonable path to
the data needed for a query. Sometimes, though, because of the specific makeup of data in a table, the statistics for the objects, or the specific configuration of a given database, the optimizer doesn’t necessarily make the best choice. In these cases, we can influence the optimizer by placing a hint in your query.

By the time we decide to place a hint in a query, we should already know that the optimizer isn’t making the choice you want. Let’s say we want to place a hint in our query to tell the optimizer to modify the access path to either perform a full table scan, or change how the optimizer will access the data from table. Full table scans are appropriate if our query will be returning a large number of rows. For example, if we want to perform a full table scan on our table, our hint will appear as follows:


SELECT /*+ full(emp) */ empno, ename
FROM emp
WHERE DEPTNO = 20;

The foregoing hint instructs the optimizer to bypass the use of any possible indexes on the EMP table, and simply scan the entire table in order to retrieve the data for the query.

let’s say we are retrieving a much smaller subset of data from the EMP table, and we want to get the average salary for those employees in department 20. we can tell the optimizer to use an index on a given table in the query:

Here I have created in index deptno_idx on deptno column.

SELECT /*+ index(emp deptno_idx) */ avg(sal)
FROM emp
WHERE deptno = 20;

Note: Hints with incorrect or improper syntax are ignored by the optimizer.

How It Works

Access path hints, like many hints, are placed in our query because we already know what access path the optimizer is going to take for our query, and we believe it will be more efficient using the method we specify with the hint. It is very important that before we use a hint, we validate that we are not getting the access path our desire or think we should be getting. we can also gauge the potential performance gain by analyzing the optimizer’s cost of the query with and without the hint.

Suppose if there is no index, the execution plan will look like this:

If there is an index the execution plan will look like this:

If we want to bypass the use of the index in the query, placing the FULL hint in the query will instruct the optimizer to bypass the use of the index as below:

Another way you can tell the optimizer to bypass the use of an index is by telling the optimizer to not use indexes to retrieve the data for a given query. In this particular case, it has the same effect as the FULL hint:

SELECT /*+ no_index(emp) */ job, min(sal), avg(sal), max(sal)
FROM emp
WHERE deptno=20
GROUP BY job;

We can also explicitly state the name of the index you wish to bypass:

SELECT /*+ no_index(emp deptno_idx) */ job, min(sal), avg(sal), max(sal)
FROM emp
WHERE deptno=20
GROUP BY job;