Will Oracle optimizer use multiple Hints in the same SELECT?

Question

I'm trying to optimize query performance and have had to resort to using optimizer hints. But I've never learned if the optimizer will use more than one hint at a time.

e.g.

SELECT /*+ INDEX(i dcf_vol_prospect_ids_idx)*/
       /*+ LEADING(i vol) */ 
       /*+ ALL_ROWS */ 
       i.id_number,
       ...
  FROM i_table i
  JOIN vol_table vol on vol.id_number = i.id_number
  JOIN to_a_bunch_of_other_tables...
 WHERE i.solicitor_id = '123'
   AND vol.solicitable_ind = 1;

The explain plan shows the same cost, but I know that's just an estimate.

Please assume that all table and index statistics have been calculated. FYI, the index dcf_vol_prospect_ids_idx is on the i.solicitor_id column.

Thanks,

Stew

Solution 1

Try specifying all the hints in a single comment block, as shown in this example from the wonderful Oracle documentation (http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm).

16.2.1 Specifying a Full Set of Hints

When using hints, in some cases, you might need to specify a full set of hints in order to ensure the optimal execution plan. For example, if you have a very complex query, which consists of many table joins, and if you specify only the INDEX hint for a given table, then the optimizer needs to determine the remaining access paths to be used, as well as the corresponding join methods. Therefore, even though you gave the INDEX hint, the optimizer might not necessarily use that hint, because the optimizer might have determined that the requested index cannot be used due to the join methods and access paths selected by the optimizer.

In Example 16-1, the LEADING hint specifies the exact join order to be used; the join methods to be used on the different tables are also specified.

Example 16-1 Specifying a Full Set of Hints

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

Solution 2

Oracle 19c introduced Hint Usage Reporting feature:

EXPLAIN PLAN FOR
SELECT /*+ INDEX(i dcf_vol_prospect_ids_idx)*/
       /*+ LEADING(i vol) */ 
       /*+ ALL_ROWS */ 
       i.id_number,
       ...
  FROM i_table i
  JOIN vol_table vol on vol.id_number = i.id_number
  JOIN to_a_bunch_of_other_tables...
 WHERE i.solicitor_id = '123'
   AND vol.solicitable_ind = 1;

SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));
                                                     --============

It shows another section Hint Report:

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: ...
---------------------------------------------------
...

Comments

[Stew S]

If anyone cares, I tried this and the Explain Plan gave the same cost as before. Who knows if the CBO will actually use it! :-/

[Stew S]

Dave, I've certain RTFMed that chapter, but never stumbled upon that great example. Sorry for the dumb post; I really try not to do that! :-/ Thanks, Stew