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 */ [SELECT,INSERT,UPDATE,MERGE,DELETE] --+ hint /* Multi-line comment */ [SELECT,INSERT,UPDATE,MERGE,DELETE] /*+ hint */
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 WHERE 1=1
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_contract , 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 -- WHERE 1=1
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 Type||Operation||Buffer Gets||Disk Reads||Elapsed Time||CPU Time||User IO Time|
* 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 CREATE OR REPLACE VIEW v_order_list AS SELECT ord.id_order , ord.amt_total , emp_store.name_store , emp_store.name_full as employee_name FROM orders ord JOIN ( 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 WHERE 1=1 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;