Query conversion hints are NO_REWRITE, NO_EXPAND, USE_CONCAT, MERGE, NO_MERGE, UNNEST, NO_UNNEST, PLACE_GROUP_BY, NO_PLACE_GROUP_BY, and NO_PRED_DEDUCE.
NO_REWRITE hint
The NO_REWRITE hint instructs the optimizer to disable query rewrite for the query block.
The syntax of the NO_REWRITE hint is as follows:
/*+ NO_REWRITE [ ( @queryblock ) ] */
Here is an example of the NO_REWRITE hint:
SELECT /*+ NO_REWRITE */ sum(s.amount_sold) AS dollars
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;
NO_EXPAND hint
The NO_EXPAND hint instructs the optimizer not to consider OR expansion for queries with a WHERE clause that has OR conditions or IN lists. Generally, the optimizer uses OR expansion when it decides that the use of OR expansion costs lower.
The syntax of the NO_EXPAND hint is as follows:
/*+ NO_EXPAND [ ( @queryblock ) ] */
Here is an example of the NO_EXPAND hint:
SELECT /*+ NO_EXPAND */ *
FROM employees e, departments d
WHERE e.manager_id = 108
OR d.department_id = 110;
USE_CONCAT hint
The USE_CONCAT hint instructs the optimizer to transform a query that has combined OR conditions in the WHERE clause into a compound query by using the UNION ALL operator. If the USE_CONCAT hint is not specified, the transformation takes place only when the cost of using a concatenated query is lower than the cost of not using it.
The syntax of the USE_CONCAT hint is as follows:
/*+ USE_CONCAT [ ( @queryblock ) ] */
Here is an example of the USE_CONCAT hint:
SELECT /*+ USE_CONCAT */ *
FROM employees e
WHERE manager_id = 108
OR department_id = 110;
MERGE hint
The MERGE hint allows you to merge views in a query.
The syntax of the MERGE hint is as follows:
/*+ MERGE [ ( @queryblock ) | ( [ @queryblock ] tablespec ) ] */
If the query block of a view has a GROUP BY clause or DISTINCT operator in the SELECT list, the optimizer can merge the view into the accessing statement only when complex view merging is enabled. You can also use complex merging to merge an IN subquery into the accessing statement if the subquery is uncorrelated.
Here is an example of the MERGE hint:
SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
FROM employees e1,
(SELECT department_id, avg(salary) avg_salary
FROM employees e2
GROUP BY department_id) v
WHERE e1.department_id = v.department_id
AND e1.salary > v.avg_salary
ORDER BY e1.last_name;
Place a MERGE hint in the view query block if the hint is used without an argument. Place a MERGE hint in the surrounding query if the hint is used with the view name as an argument.
NO_MERGE hint
The NO_MERGE hint instructs the optimizer not to merge the outer query and inline view queries into a single query.
The syntax of the NO_MERGE hint is as follows:
/*+ NO_MERGE [ ( @queryblock ) | ( [ @queryblock ] tablespec ) ] */
This hint allows you to have more influence over the way you access views. In the following example statement, the seattle_dept view is not merged.
SELECT /*+ NO_MERGE(seattle_dept) */ e.last_name, seattle_dept.dept_name
FROM employees e,
(SELECT location_id, dept_id, dept_name
FROM departments
WHERE location_id = 1700) seattle_dept
WHERE e.dept_id = seattle_dept.dept_id;
When you use the NO_MERGE hint in the view query block, specify it without an argument. When you use the NO_MERGE hint in the surrounding query, specify it with the view name as an argument.
UNNEST hint
The UNNEST hint instructs the optimizer to unnest and merge the body of the subquery into the body of the query block that contains the hint, allowing the optimizer to consider them together when evaluating access paths and joins.
The syntax of the UNNEST hint is as follows:
/*+ UNNEST [ ( @queryblock ) ] */
The optimizer verifies whether the subquery is valid before unnesting the subquery. The UNNEST hint instructs the optimizer to check only the subquery block for validity. If the subquery block is valid, the subquery is directly unnested.
Here is an example of the UNNEST hint:
SELECT AVG(t1.c) FROM t1
WHERE t1.b >=
(SELECT /*+UNNEST*/AVG(t2.b)
FROM t2
WHERE t1.a = t2.a);
NO_UNNEST hint
The NO_UNNEST hint disables unnesting.
The syntax of the NO_UNNEST hint is as follows:
/*+ NO_UNNEST [ ( @queryblock ) ] */
Here is an example of the NO_UNNEST hint:
SELECT /*+NO_UNNEST(@qb1)*/AVG(t1.c)
FROM t1 WHERE t1.b >=
(SELECT /*+qb_name(qb1)*/AVG(t2.b)
FROM t2)
WHERE t1.a = t2.a);
PLACE_GROUP_BY hint
The PLACE_GROUP_BY hint instructs the optimizer to use the GROUP BY placement mechanism regardless of the cost after the transformation.
The syntax of the PLACE_GROUP_BY hint is as follows:
/*+ PLACE_GROUP_BY [ ( @queryblock ) ] */
Here is an example of the PLACE_GROUP_BY hint:
SELECT /*+PLACE_GROUP_BY*/SUM(t1.c),SUM(t2.c) FROM t1, t2
WHERE t1.a = t2.a AND t1.b > 10 AND t2.b > 10
GROUP BY t1.a;
NO_PLACE_GROUP_BY hint
The NO_PLACE_GROUP_BY hint disables GROUP BY replacement.
The syntax of the NO_PLACE_GROUP_BY hint is as follows:
/*+ NO_PLACE_GROUP_BY [ ( @queryblock ) ] */
Here is an example of the NO_PLACE_GROUP_BY hint:
SELECT /*+NO_PLACE_GROUP_BY*/SUM(t1.c),SUM(t2.c) FROM t1, t2
WHERE t1.a = t2.a AND t1.b > 10 AND t2.b > 10
GROUP BY t1.a;
NO_PRED_DEDUCE hint
The NO_PRED_DEDUCE hint instructs the optimizer not to use the predicate deduction rule.
The syntax of the NO_PRED_DEDUCE hint is as follows:
/*+ NO_PRED_DEDUCE [ ( @queryblock ) ] */
Here is an example of the NO_PRED_DEDUCE hint:
SELECT /*+NO_PRED_DEDUCE(@qb1)*/ *
FROM (
SELECT /*+no_merge qb_name(qb1)*/ t1.a, t2.b
FROM t1, t2
WHERE t1.a = t2.a) v, t3
WHERE t3.a = 1 AND t3.a = v.a;