All Products
Search
Document Center

Hints related to query transformation

Last Updated: Jun 18, 2021

NO_REWRITE Hint

The NO_REWRITE hint instructs the optimizer to disable query rewrites for the query block and override the setting of the QUERY_REWRITE_ENABLED parameter.

The NO_REWRITE hint uses the following syntax:

/*+ NO_REWRITE [ ( @ queryblock ) ] */

The following statement provides an example:

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 prevents the optimizer from considering OR expansion for queries that have OR conditions or IN lists in the WHERE clause. Usually, the optimizer uses OR expansion when the optimizer decides that the cost of using OR extension is lower than not using it.

The NO_EXPAND hint uses the following syntax:

/*+ NO_EXPAND [ ( @ queryblock ) ] */

The following statement provides an example:

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 combined OR conditions in the WHERE clause of a query into a compound query by using the UNION ALL operator. If this hint is not used, this transformation occurs only if the cost of the query based on concatenations is lower than that without concatenations. The USE_CONCAT hint overrides the cost consideration.

The USE_CONCAT hint uses the following syntax:

/*+ USE_CONCAT [ ( @ queryblock ) ] */

The following statement provides an example:

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 MERGE hint uses the following syntax:

/*+ MERGE [ ( @ queryblock )  | ( [ @ queryblock ] tablespec ) ] */

If the query block of a view includes the GROUP BY clause or DISTINCT operator in the SELECT list, the optimizer can merge the view into the accessing statement only if complex view merging is enabled. If an IN subquery is uncorrelated, you can also use complex merging to merge the subquery into the accessing statement.

The following statement provides an example:

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;

When you use the MERGE hint without an argument, you must place the hint in the query block of a view. When you use

the MERGE hint with the view name as an argument, you must place the hint in the surrounding query.

NO_MERGE Hint

The NO_MERGE hint instructs the optimizer not to combine the outer query and inline view queries into a single query.

The NO_MERGE hint uses the following syntax:

/*+ NO_MERGE [ ( @ queryblock )  | ( [ @ queryblock ] tablespec ) ] */

This hint affects how you access the view. For example, if you execute the following statement, the view seattle_dept cannot be merged:

SELECT /*+ NO_MERGE(seattle_dept) */ e1.last_name, seattle_dept.department_name
  FROM employees e1,
       (SELECT location_id, department_id, department_name
          FROM departments
          WHERE location_id = 1700) seattle_dept
  WHERE e1.department_id = seattle_dept.department_id;

When you use the NO_MERGE hint in the view query block, you do not need to specify arguments for the hint. When you specify the NO_MERGE hint in the surrounding query, you must specify the hint with the view name as an argument.

UNNEST Hint

The UNNEST hint instructs the optimizer not to nest but to merge the body of the subquery into the body of the query block that contains the hint. This allows the optimizer to consider the subquery and the hint together when the optimizer evaluates access paths and joins.

The UNNEST hint uses the following syntax:

/*+ UNNEST [ ( @ queryblock ) ] */

Before a subquery is unnested, the optimizer first verifies whether the subquery is valid. The subquery must pass heuristic and query optimization tests. When you use the UNNEST hint, the optimizer verifies the validity of only the subquery block. If the subquery block is valid, subquery unnesting is enabled even if heuristic and query optimization tests are not passed.

The following statement provides an example:

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 is used to disable subquery unnesting.

The NO_UNNEST hint uses the following syntax:

/*+ NO_UNNEST [ ( @ queryblock ) ] */

The following statement provides an example:

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 sequence replacement rules that are specified in the GROUP BY clause. In this case, the optimizer does not consider the cost increase caused by the transformation.

The PLACE_GROUP_BY hint uses the following syntax:

/*+ PLACE_GROUP_BY [ ( @ queryblock ) ] */

The following statement provides an example:

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 is used to disable the sequence transformation that is specified in the GROUP BY clause.

The NO_PLACE_GROUP_BY hint uses the following syntax:

/*+ NO_PLACE_GROUP_BY [ ( @ queryblock ) ] */

The following statement provides an example:

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 predicates to deduce the transformation rules.

The NO_PRED_DEDUCE hint uses the following syntax:

/*+ NO_PRED_DEDUCE [ ( @ queryblock ) ] */

The following statement provides an example:

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;