19 Using Optimizer Hints

19.1.1 Types of Hints

Hints can be of the following general types:

• Single-table

  Single-table hints are specified on one table or view. INDEX and USE_NL are examples of single-table hints.

• Multi-table

  Multi-table hints are like single-table hints, except that the hint can specify one or more tables or views. LEADING is an example of a multi-table hint. Note that USE_NL(table1 table2) is not considered a multi-table hint because it is a shortcut for USE_NL(table1) and USE_NL(table2).

• Query block

  Query block hints operate on single query blocks. STAR_TRANSFORMATION and UNNEST are examples of query block hints.

• Statement

  Statement hints apply to the entire SQL statement. ALL_ROWS is an example of a statement hint.

19.1.2 Hints by Category

Optimizer hints are grouped into the following categories:

• Hints for Optimization Approaches and Goals

• Hints for Enabling Optimizer Features

• Hints for Access Paths

• Hints for Join Orders

• Hints for Join Operations

• Hints for Online Application Upgrade

• Hints for Parallel Execution

• Hints for Query Transformations

• Additional Hints

These categories, and the hints contained within each category, are listed in the sections that follow.

SELECT /*+ optimizer_features_enable('11.1.0.6') */ employee_id, last_name
FROM    employees
ORDER BY employee_id;

/*+ PQ_DISTRIBUTE(tablespec, distribution) */

SELECT /*+ORDERED PQ_DISTRIBUTE(departments HASH, HASH) USE_HASH (departments)*/
       e.employee_id, d.department_name
FROM   employees e, departments d
WHERE  e.department_id=d.department_id;

CREATE /*+ PQ_DISTRIBUTE(lineitem, PARTITION) */ TABLE lineitem
  NOLOGGING PARALLEL 16
  PARTITION BY HASH (l_orderkey) PARTITIONS 512
  AS SELECT * FROM lineitemxt; 

INSERT /*+ APPEND PARALLEL(LINEITEM, 16) PQ_DISTRIBUTE(LINEITEM, NONE) */
  INTO lineitem
  (SELECT * FROM lineitemxt);

19.2.1 Specifying a Full Set of Hints

When using hints, in some cases, you might need to specify a full set of hints 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 must determine the remaining access paths to be used, and 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 19-2, the LEADING hint specifies the exact join order. The join methods are also specified.

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;

19.2.2 Specifying a Query Block in a Hint

To identify a query block in a query, you can use an optional query block name in a hint to specify the block to which the hint applies. The syntax of the query block argument is of the form @queryblock, where queryblock is an identifier that specifies a block in the query. The queryblock identifier can either be system-generated or user-specified.

Note the following guidelines:

• You can obtain the system-generated identifier by using EXPLAIN PLAN for the query. You can determine pre-transformation query block names by running EXPLAIN PLAN for the query using the NO_QUERY_TRANSFORMATION hint.

• You can set the user-specified name with the QB_NAME hint.

Assumptions

This tutorial assumes the following:

• You intend to create a join view of employees and job_history that contains a nested query block.

• You want to query all rows in the view, but apply the NO_UNNEST hint to the query block only.

To apply the NO_UNNEST hint to the query block: 

1. Start SQL*Plus and log in as user hr.

2. Create the view.

   For example, run the following statement:

   CREATE OR REPLACE VIEW v_emp_job_history AS
     SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
     FROM   employees e1, (SELECT * FROM employees e3) 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
     AND    e1.salary = ( SELECT max(e2.salary) 
                          FROM employees e2 
                          WHERE e2.department_id = e1.department_id ) 
     GROUP BY e1.first_name, e1.last_name, j.job_id
     ORDER BY total_sal;
   

3. Explain the plan for a query of v_emp_job_history.

   For example, run the following SQL statement:

   EXPLAIN PLAN FOR SELECT * FROM v_emp_job_history; 
   

4. Query the plan table.

   For example, run the following SQL statement:

   SELECT PLAN_TABLE_OUTPUT 
   FROM   TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));
   

   The database displays the plan.

5. In the query plan output, obtain the operation ID associated with the query block, and then use the ID to find the query block name.

   For example, the following plan shows that the full scan of the employees table occurs in operation 11, which corresponds to query block @SEL$4:

   ------------------------------------------------------------------------------
   |Id| Operation                        |Name        |Rows|Bytes|Cost |Time
   ------------------------------------------------------------------------------
   |0 | SELECT STATEMENT                 |            |1   |46   |9(34)|00:00:01|
   .
   .
   .
   |11|          TABLE ACCESS FULL       | EMPLOYEES  |107 |749  |3(0) |00:00:01|
   .
   .
   .
   -------------------------------------------------------------------------------
   
   Query Block Name / Object Alias (identified by operation id):
   -------------------------------------------------------------
    
      1 - SEL$2980E977 / V_EMP_JOB_HISTORY@SEL$1
      2 - SEL$2980E977
      8 - SEL$8F9407EC / VW_SQ_1@SEL$32F848CB
      9 - SEL$8F9407EC
     11 - SEL$8F9407EC / E2@SEL$4
   

6. Query the view using the NO_UNNEST hint.

   For example, run the following SQL statement to apply the NO_UNNEST hint to query block @SEL$4 (sample output included):

   SQL> SELECT /*+ NO_UNNEST( @SEL$4 ) */ * FROM v_emp_job_history;
   
   FIRST_NAME           LAST_NAME                 JOB_ID      TOTAL_SAL
   -------------------- ------------------------- ---------- ----------
   Michael              Hartstein                 MK_REP           6000
   

19.2.3 Specifying Global Table Hints

Hints that specify a table typically refer to tables in the DELETE, SELECT, or UPDATE query block in which the hint occurs, not to tables inside any views referenced by the statement. When you want to specify hints for tables that appear inside views, Oracle recommends using global hints instead of embedding the hint in the view. You can transform the table hints described in this chapter into a global hint by using an extended tablespec syntax that includes view names with the table name.

In addition, an optional query block name can precede the tablespec syntax. See "Specifying a Query Block in a Hint".

Hints that specify a table use the following syntax, where view specifies a view name and table specifies the name or alias of the table:

tablespec::=
Description of the illustration tablespec.gif

If the view path is specified, then the database resolves the hint from left to right, where the first view must be present in the FROM clause, and each subsequent view must be specified in the FROM clause of the preceding view.

Example 19-3 creates a view v to return the first and last name of the employee, his or her first job, and the total salary of all direct reports of that employee for each employee with the highest salary in his or her department. When querying the data, you want to force the use of the index emp_job_ix for the table e3 in view e2.

CREATE OR REPLACE VIEW v AS
  SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
  FROM   employees e1, ( SELECT * FROM employees e3) 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
  AND e1.salary = ( SELECT max(e2.salary) FROM employees e2
                    WHERE e2.department_id = e1.department_id )
  GROUP BY e1.first_name, e1.last_name, j.job_id
  ORDER BY total_sal;

SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */  * FROM v;

SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ * FROM v;

SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ * FROM v;

SELECT /*+ LEADING(v.b a v.c) */ *
FROM a, v
WHERE a.id = v.id;

SELECT /*+ LEADING(A@SEL$1 B@SEL$2 C@SEL$2) */
FROM a a, v v
WHERE a.id = v.id;

CREATE OR REPLACE VIEW v1 AS
  SELECT *
    FROM employees
    WHERE employee_id < 150;

CREATE OR REPLACE VIEW v2 AS
  SELECT v1.employee_id employee_id, departments.department_id department_id
    FROM v1, departments
    WHERE v1.department_id = departments.department_id;

SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk)
                        FULL(v2.departments) */ *
  FROM v2
  WHERE department_id = 30;

19.2.4 Specifying Complex Index Hints

Hints that specify an index can use either a simple index name or a parenthesized list of columns as follows:

indexspec::=

The semantics are as follows:

• table specifies the name

• column specifies the name of a column in the specified table

  • The columns can optionally be prefixed with table qualifiers allowing the hint to specify bitmap join indexes where the index columns are on a different table than the indexed table. If tables qualifiers are present, then they must be base tables, not aliases in the query.

  • Each column in an index specification must be a base column in the specified table, not an expression. Function-based indexes cannot be hinted using a column specification unless the columns specified in the index specification form the prefix of a function-based index.

• index specifies an index name

  When tablespec is followed by indexspec in the specification of a hint, a comma separating the table name and index name is permitted but not required. Commas are also permitted, but not required, to separate multiple occurrences of indexspec.

The hint is resolved as follows:

• If an index name is specified, then the database only considered the specified index.

• If a column list is specified, and if an index exists whose columns match the specified columns in number and order, then the database only consider this index. If no such index exists, then any index on the table with the specified columns as the prefix in the order specified is considered. In either case, the behavior is exactly as if the user had specified the same hint individually on all the matching indexes.

For example, in Example 19-3 the job_history table has a single-column index on the employee_id column and a concatenated index on employee_id and start_date columns. To specifically instruct the optimizer on index use, you can hint the query as follows:

SELECT /*+ INDEX(v.j jhist_employee_ix (employee_id start_date)) */ * FROM v;

19.3.1 Hints and Complex Views

By default, hints do not propagate inside a complex view. For example, if you specify a hint in a query that selects against a complex view, then this hint is not honored, because it is not pushed inside the view.

Unless the hints are inside the base view, they might not be honored from a query against the view.

19.3.2 Hints and Mergeable Views

A mergeable view is a view that Oracle Database can replace with the query that defines the view. For example, suppose you create a view as follows:

CREATE OR REPLACE VIEW emp_view AS 
  SELECT last_name, department_name FROM employees e, departments d
  WHERE e.department_id=d.department_id;

This view is mergeable because the database can optimize the following query to use the SELECT statement that defines the view, avoiding use of the view itself.

SELECT * FROM emp_view;

19.3.3 Hints and Nonmergeable Views

With nonmergeable views, optimization approach and goal hints inside the view are ignored; the top-level query decides the optimization mode.

Because nonmergeable views are optimized separately from the top-level query, access path and join hints inside the view are preserved. For the same reason, access path hints on the view in the top-level query are ignored.

However, join hints on the view in the top-level query are preserved because, in this case, a nonmergeable view is similar to a table.