Oracle Hints

When the execution plan is not correct (for whatever reason) you have a chance to force it to use your way of execution. How? With the help of hints 🙂 Hints are a specific set of commands used to change the execution plan. I will not discuss all hints provided by Oracle simply because I don’t want to copy the Oracle’s documentation pages. I will discuss only the most commonly used hints.

Important: Hints should be your last solution for an optimization. Always exhaust all other options before you get to hints !!!

We have 4 main types of hints

  • Single-table – operates with one table or view only
  • Multi-table – can operate with more than one table or view
  • Query block – operates on a particular query block
  • Statement – applies to the SQL statement as a whole


There are two ways how you can apply hints in your queries.

/* Single-line comment */

/* Multi-line comment */ 

As you can deduce from above, you use a comment code immediately followed by a plus (+) sign. The single-line comment requires all hints to be on one line only. Yes, you can combine multiple hints 🙂

The most commonly used HINTS


Instruct the Optimizer to use HASH JOIN (instead of Nested Loops usually).

 *  usage: use_hash(table_A table_B)
 *           or
 *         use_hash(table_a_alias table_b_alias) -- when table alias is used
SELECT /*+ use_hash(cnt inst) */
FROM contract cnt
LEFT JOIN installment inst
ON cnt.id_contract = inst.id_contract

Materialize hint is an undocumented hint. It is used in a combination with the WITH clause and assures that the “virtual table” will be stored as a temporary table and therefore queried once only (from the performance perspective it prevents DRY principle violation). This way the table will be read-only once! Without this hint, the table would be read three times.

WITH w_inst_3 AS (
                   SELECT --+ materialize 
                   , id_num_inst
                   , amt_inst
                   FROM installment
                   WHERE id_num_inst IN (1,2,3)

SELECT c.id_contract
, i1.amt_total as i1_amt_total
, i2.amt_total as i2_amt_total
, i3.amt_total as i3_amt_total
FROM contract c
LEFT JOIN w_inst_3 i1
ON i1.id_contract = c.id_contract
AND i1.id_num_inst = 1
LEFT JOIN w_inst_3 i2
ON i2.id_contract = c.id_contract
AND i2.id_num_inst = 2
LEFT JOIN w_inst_3 i3
ON i3.id_contract = c.id_contract
AND i3.id_num_inst = 3

Forces the Optimizer to bypass the index usage.

 * usage: no_index(table_name index_name)
 *         or
 *        no_index(table_alias index_name) -- when table alias is used
SELECT --+ no_index(emp idx_id_emp)
FROM employee emp
WHERE id_emp = 1002;

Forces the optimizer to scan all records in a table (perform the Full Table Scan; usually to avoid index scan)

 * usage: full(table_name)
 *         or
 *        full(table_alias) -- when table alias is used
SELECT --+ full(emp)
FROM employee emp
WHERE id_emp = 1002;

With this hint, you can decide which table will be the driving table out of the two joined tables. This is very important when you incorrectly write a query, where the small table is a driving table and the large one is a joined table or vice versa. The order matters a lot and might dramatically change the plan.

 * usage: leading(employee)
 *         or
 *        leading(emp) -- when table alias is used
SELECT --+ leading(dpt)
FROM employee emp, department dpt
WHERE emp.id_department = dpt.id_department
AND dpt.id_department = 20;

This is a very powerful hint and must be used with extreme care. This allows a query to be executed in parallel. That means the query will create X (depends on the parallel degree you set) threads to obtain data. If you choose a too high degree of parallelism, it might be a killer for your database.

I suggest using parallel only in critical situations where there is no other way for you to get your data. Remember, if your query takes too long you are probably doing something wrong. It is either the incorrect design or your improper query logic. Either way, you should fix this first before you proceed with this hint!

If you really need to use parallel, please do not set the degree higher than 5 or 10 (depends on your server configuration and resources). Usually, go with 2 or 3 in the beginning and if needed increase.

SELECT --+ parallel(4)
FROM employee;

APPEND hint (a.k.a. DIRECT LOAD) is a very handy script and might be very useful 🙂 This is most commonly used with TRUNCATE or GTTs. When you need to reload a table TRUNCATE & INSERT /*+ APPEND */ is your best friend 🙂 Even with Global Temporary Tables it works flawlessly. See the results and examples of use below. With APPEND hint the Optimizer skips buffer cache (see the Instance and storage article for more details)  and writes data directly into Oracle datafiles (see the Database files article for more details). Contrary to the conventional load (the one without APPEND), the direct load does not try to reuse the free space within a table. This is why DELETE and INSERT –+ APPEND is the worst idea ever as I mentioned in the article about High Water Mark.

Comparison of loading 100k rows with different approaches.

Table TypeOperationBuffer GetsDisk ReadsElapsed TimeCPU TimeUser IO Time
GTTINSERT --+APPEND8006332461668728
HEAPINSERT --+APPEND1587973801685264943

* To understand what are those fields about, please visit the article Check the real cost of a query.

As you can see, INSERT and +APPEND into GTT wins without doubts 🙂

Hints and Views

Hints work perfectly with views as well. You just cascade the hierarchy in your hint.

-- view definition
SELECT ord.id_order
, ord.amt_total
, emp_store.name_store
, emp_store.name_full as employee_name
FROM orders ord
      SELECT e.id_employee
      , e.name_full
      , s.name_store
      FROM employee e
      JOIN emp_to_store e2s
      ON e2s.id_empoyee = e.id_employee
      AND sysdate BETWEEN e2s.dtime_from AND e2s.dtime_to
      JOIN store s
      ON s.id_store = e2s.id_store
     ) emp_store
ON ord.id_employee = emp_store.id_employee
AND ord.dtime_order BETWEEN trunc(sysdate)-3 AND sysdate
AND ord.id_order NOT IN (SELECT id_order FROM test_order)

-- hint usage
SELECT /*+ use_hash(v.emp_store.e v.emp_store.e2s v.emp_store.s) full(ord) */  
FROM v_order_list v;