Oracle8i Designing and Tuning for Performance
Release 2 (8.1.6)

Part Number A76992-01

Library

Product

Contents

Index

Go to previous page Go to next page

7
Using Optimizer Hints

This chapter offers recommendations on how to use cost-based optimizer hints to enhance Oracle performance.

This chapter contains the following sections:

Understanding Hints

As an application designer, you may know information about your data that the optimizer does not know. For example, you may know that a certain index is more selective for certain queries. Based on this information, you may be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

Hints allow you to make decisions usually made by the optimizer. You can use hints to specify the following:

Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the following criteria:

Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used.

See Also:

For more information on default values, see Chapter 8, "Gathering Statistics"

Specifying Hints

Hints apply only to the optimization of the statement block in which they appear. A statement block is any one of the following statements or parts of statements:

For example, a compound query consisting of two component queries combined by the UNION operator has two statement blocks, one for each component query. For this reason, hints in the first component query apply only to its optimization, not to the optimization of the second component query.

You can send hints for a SQL statement to the optimizer by enclosing them in a comment within the statement.

See Also:

For more information on comments, see Oracle8i SQL Reference. 

A statement block can have only one comment containing hints. This comment can only follow the SELECT, UPDATE, or DELETE keyword.


Exception:

The APPEND hint follows the INSERT keyword. 


The syntax diagrams show the syntax for hints contained in both styles of comments that Oracle supports within a statement block.


or:


where:

DELETE 
SELECT 
UPDATE
 

Is a keyword that begins a statement block. Comments containing hints can appear only after these keywords. 

+ 

Causes Oracle to interpret the comment as a list of hints. The plus sign must immediately follow the comment delimiter (no space is permitted). 

hint
 

Is one of the hints discussed in this section. If the comment contains multiple hints, then each pair of hints must be separated by at least one space. 

text
 

Is other commenting text that can be interspersed with the hints. 

If you specify hints incorrectly, then Oracle ignores them, but does not return an error:

Other conditions specific to index type appear later in this chapter.

The optimizer recognizes hints only when using the cost-based approach. If you include a hint (except the RULE hint) in a statement block, then the optimizer automatically uses the cost-based approach.

See Also:

the "Using Hints" section shows the syntax of each hint. 

Specifying a Full Set of Hints

When using hints, in some cases, you may need to specify a full set of hints, so as 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 needs to determine the remaining access paths to be used, as well as the corresponding join methods. Therefore, even though you gave the INDEX hint, the optimizer may not necessarily use that hint, because the optimizer may have determined that the requested index cannot be used due to the join methods and access paths selected by the optimizer. In this particular example, we have specified the exact join order to be used, via the ORDERED hint, as well as the join methods to be used on the different tables.

SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) 
           USE_NL (glcc glf) USE_MERGE (gp gsb) */
 b.application_id ,
 b.set_of_books_id ,
 b.personnel_id,
 p.vendor_id Personnel,
 p.segment1 PersonnelNumber,
 p.vendor_name Name
FROM  jl_br_journals j,
      jl_br_balances b,
      gl_code_combinations glcc,
      fnd_flex_values_vl glf,
      gl_periods gp,
      gl_sets_of_books gsb,
      po_vendors p
WHERE  . . . . . . . . . . . . 

Using Hints Against Views

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


Note:

If the view is a single-table, then the hint is not propagated.  


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

Local vs. Global Hints

Table hints (i.e., hints that specify a table) normally refer to tables in the DELETE, SELECT, or UPDATE statement in which the hint occurs, not to tables inside any views or subqueries referenced by the statement. When you want to specify hints for tables that appear inside views or subqueries, Oracle recommends using global hints instead of embedding the hint in the view or subquery. Any table hint described in this chapter can be transformed into a global hint by using an extended syntax for the table name.

See Also:

For information on how to create global hints, see "Global Hints"

Using Hints

Hints for Optimization Approaches and Goals

The hints described in this section allow you to choose between the cost-based and the rule-based optimization approaches. With the cost-based approach, this also includes the goal of best throughput or best response time.

If a SQL statement has a hint specifying an optimization approach and goal, then the optimizer uses the specified approach regardless of the presence or absence of statistics, the value of the OPTIMIZER_MODE initialization parameter, and the OPTIMIZER_MODE parameter of the ALTER SESSION statement.


Note:

The optimizer goal applies only to queries submitted directly. Use hints to determine the access path for any SQL statements submitted from within PL/SQL. The ALTER SESSION... SET OPTIMIZER_MODE statement does not affect SQL that is run from within PL/SQL. 


ALL_ROWS

The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

The syntax of this hint is as follows:


For example, the optimizer uses the cost-based approach to optimize this statement for best throughput:

SELECT /*+ ALL_ROWS */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;

FIRST_ROWS

The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

This hint causes the optimizer to make the following choices:

The syntax of this hint is as follows:


For example, the optimizer uses the cost-based approach to optimize this statement for best response time:

SELECT /*+ FIRST_ROWS */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;

The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:

These statements cannot be optimized for best response time, because Oracle must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any of these statements, then the optimizer uses the cost-based approach and optimizes for best throughput.

If you specify either the ALL_ROWS or the FIRST_ROWS hint in a SQL statement, and if the data dictionary does not have statistics about tables accessed by the statement, then the optimizer uses default statistical values (such as allocated storage for such tables) to estimate the missing statistics and, subsequently, to choose an execution plan.

These estimates may not be as accurate as those gathered by the DBMS_STATS package. Therefore, use the DBMS_STATS package to gather statistics. If you specify hints for access paths or join operations along with either the ALL_ROWS or FIRST_ROWS hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.

CHOOSE

The CHOOSE hint causes the optimizer to choose between the rule-based and cost-based approaches for a SQL statement. The optimizer bases its selection on the presence of statistics for the tables accessed by the statement. If the data dictionary has statistics for at least one of these tables, then the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary does not have statistics for these tables, then it uses the rule-based approach.

The syntax of this hint is as follows:


Example

SELECT /*+ CHOOSE */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;

RULE

The RULE hint explicitly chooses rule-based optimization for a statement block. It also makes the optimizer ignore other hints specified for the statement block. The syntax of this hint is as follows:


Example

The optimizer uses the rule-based approach for this statement:

SELECT --+ RULE 
empno, ename, sal, job
FROM emp
WHERE empno = 7566;

The RULE hint, along with the rule-based approach, may not be supported in future releases of Oracle.

Hints for Access Methods

Each hint described in this section suggests an access method for a table.

Specifying one of these hints causes the optimizer to choose the specified access path only if the access path is available based on the existence of an index or cluster and on the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, then the optimizer ignores it.

You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. The table name within the hint should not include the schema name if the schema name is present in the statement.


Note:

For access path hints, Oracle ignores the hint if you specify the SAMPLE option in the FROM clause of a SELECT statement. For more information on the SAMPLE option, see Oracle8i Concepts and Oracle8i Reference.  


FULL

The FULL hint explicitly chooses a full table scan for the specified table. The syntax of this hint is as follows:


where table specifies the name or alias of the table on which the full table scan is to be performed. If your statement does not use aliases, then the table name is the default alias.

Example

Oracle performs a full table scan on the accounts table to execute this statement, even if there is an index on the accno column that is made available by the condition in the WHERE clause:

SELECT /*+ FULL(A) don't use the index on accno */ accno, bal
FROM accounts a
WHERE accno = 7086854;


Note:

Because the accounts table has alias "a", the hint must refer to the table by its alias rather than by its name. Also, do not specify schema names in the hint even if they are specified in the FROM clause. 


ROWID

The ROWID hint explicitly chooses a table scan by rowid for the specified table. The syntax of the ROWID hint is:


where table specifies the name or alias of the table on which the table access by rowid is to be performed.

Example

SELECT /*+ROWID(emp)*/ * 
FROM emp 
WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND empno = 155; 

CLUSTER

The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects. The syntax of the CLUSTER hint is:


where table specifies the name or alias of the table to be accessed by a cluster scan.

Example

SELECT --+ CLUSTER 
emp.ename, deptno
FROM emp, dept
WHERE deptno = 10 

AND emp.deptno = dept.deptno;

HASH

The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster. The syntax of the HASH hint is:


where table specifies the name or alias of the table to be accessed by a hash scan.

INDEX

The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B*-tree, and bitmap indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint.

The syntax of the INDEX hint is:


where:

table
 

Specifies the name or alias of the table associated with the index to be scanned. 

index
 

Specifies an index on which an index scan is to be performed. 

This hint may optionally specify one or more indexes:

For example, consider this query that selects the name, height, and weight of all male patients in a hospital:

SELECT name, height, weight
FROM patients
WHERE sex = 'm';

Assume that there is an index on the SEX column, and that this column contains the values m and f. If there are equal numbers of male and female patients in the hospital, then the query returns a relatively large percentage of the table's rows, and a full table scan is likely to be faster than an index scan. However, if a very small percentage of the hospital's patients are male, then the query returns a relatively small percentage of the table's rows, and an index scan is likely to be faster than a full table scan.

Barring the use of frequency histograms, the number of occurrences of each distinct column value is not available to the optimizer. The cost-based approach assumes that each value has an equal probability of appearing in each row. For a column having only two distinct values, the optimizer assumes each value appears in 50% of the rows, so the cost-based approach is likely to choose a full table scan rather than an index scan.

If you know that the value in the WHERE clause of your query appears in a very small percentage of the rows, then you can use the INDEX hint to force the optimizer to choose an index scan. In this statement, the INDEX hint explicitly chooses an index scan on the sex_index, the index on the sex column:

SELECT /*+ INDEX(patients sex_index) use sex_index because there are few

male patients  */ name, height, weight
FROM patients WHERE sex = 'm';

The INDEX hint applies to IN-list predicates; it forces the optimizer to use the hinted index, if possible, for an IN-list predicate. Multi-column IN-lists will not use an index.

INDEX_ASC

The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values. The syntax of the INDEX_ASC hint is:


Each parameter serves the same purpose as in the INDEX hint.

Because Oracle's default behavior for a range scan is to scan index entries in ascending order of their indexed values, this hint does not specify anything more than the INDEX hint. However, you may want to use the INDEX_ASC hint to specify ascending range scans explicitly, should the default behavior change.

INDEX_COMBINE

The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes. The syntax of INDEX_COMBINE is:


Example

SELECT /*+INDEX_COMBINE(emp sal_bmi hiredate_bmi)*/ * 
FROM emp  
WHERE sal < 50000 AND hiredate < '01-JAN-1990';

INDEX_JOIN

The INDEX_JOIN hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.


where:

table
 

Specifies the name or alias of the table associated with the index to be scanned. 

index
 

Specifies an index on which an index scan is to be performed. 

Example

SELECT /*+INDEX_JOIN(emp sal_bmi hiredate_bmi)*/ sal, hiredate  
FROM emp  
WHERE sal < 50000; 

INDEX_DESC

The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. The syntax of the INDEX_DESC hint is:


Each parameter serves the same purpose as in the INDEX hint.

INDEX_FFS

This hint causes a fast full index scan to be performed rather than a full table scan. The syntax of INDEX_FFS is:


Example

SELECT /*+INDEX_FFS(emp emp_empno)*/ empno 
FROM emp 
WHERE empno > 200; 

See Also:

"Using Fast Full Index Scans". 

NO_INDEX

The NO_INDEX hint explicitly disallows a set of indexes for the specified table. The syntax of the NO_INDEX hint is:


The NO_INDEX hint applies to function-based, B*-tree, bitmap, cluster, or domain indexes.

If a NO_INDEX hint and an index hint (INDEX, INDEX_ASC, INDEX_DESC, INDEX_COMBINE, or INDEX_FFS) both specify the same indexes, then both the NO_INDEX hint and the index hint are ignored for the specified indexes and the optimizer considers the specified indexes.

Example

SELECT /*+NO_INDEX(emp emp_empno)*/ empno 
FROM emp 
WHERE empno > 200; 

AND_EQUAL

The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. The syntax of the AND_EQUAL hint is:


where:

table
 

Specifies the name or alias of the table associated with the indexes to be merged. 

index
 

Specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five. 

USE_CONCAT

The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.

The USE_CONCAT hint turns off IN-list processing and OR-expands all disjunctions, including IN-lists. The syntax of this hint is:


Example

SELECT /*+USE_CONCAT*/ * 
FROM emp 
WHERE empno > 50 OR sal < 50000; 

NO_EXPAND

The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides the cost is lower than not using it. The syntax of this hint is:


Example

SELECT /*+NO_EXPAND*/ * 
FROM emp 
WHERE empno = 50 OR empno = 100; 

REWRITE

Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost. Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of its cost.

The syntax of this hint is:



See Also:

For more information on materialized views, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.  

NOREWRITE

Use the NOREWRITE hint on any query block of a request. This hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. The syntax of this hint is:


Hints for Join Orders

The hints in this section suggest join orders:

ORDERED

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. The syntax of this hint is:


For example, this statement joins table TAB1 to table TAB2 and then joins the result to table TAB3:

SELECT /*+ ORDERED */ tab1.col1, tab2.col2, tab3.col3
FROM tab1, tab2, tab3
WHERE tab1.col1 = tab2.col1

AND tab2.col1 = tab3.col1;

If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You may want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information allows you to choose an inner and outer table better than the optimizer could.

STAR

The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.

The syntax of this hint is:


Usually, if you analyze the tables, then the optimizer selects an efficient star plan. You can also use hints to improve the plan. The most precise method is to order the tables in the FROM clause in the order of the keys in the index, with the large table last. Then use the following hints:

/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */

Where facts is the table and fact_concat is the index. A more general method is to use the STAR hint.

See Also:

Oracle8i Concepts for more information about star plans. 

Hints for Join Operations

Each hint described in this section suggests a join operation for a table.

You must specify a table to be joined exactly as it appears in the statement. If the statement uses an alias for the table, then you must use the alias rather than the table name in the hint. The table name within the hint should not include the schema name, if the schema name is present in the statement.

Use of the USE_NL and USE_MERGE hints is recommended with the ORDERED hint. Oracle uses these hints when the referenced table is forced to be the inner table of a join, and they are ignored if the referenced table is the outer table.

USE_NL

The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is:


where table is the name or alias of a table to be used as the inner table of a nested loops join.

For example, consider this statement, which joins the accounts and customers tables. Assume that these tables are not stored together in a cluster:

SELECT accounts.balance, customers.last_name, customers.first_name
FROM accounts, customers
WHERE accounts.custno = customers.custno;

Because the default goal of the cost-based approach is best throughput, the optimizer chooses either a nested loops operation or a sort-merge operation to join these tables, depending on which is likely to return all the rows selected by the query more quickly.

However, you may want to optimize the statement for best response time, or the minimal elapsed time necessary to return the first row selected by the query, rather than best throughput. If so, then you can force the optimizer to choose a nested loops join by using the USE_NL hint. In this statement, the USE_NL hint explicitly chooses a nested loops join with the customers table as the inner table:

SELECT /*+ ORDERED USE_NL(customers) to get first row faster */
accounts.balance, customers.last_name, customers.first_name
FROM accounts, customers
WHERE accounts.custno = customers.custno;

In many cases, a nested loops join returns the first row faster than a sort-merge join. A nested loops join can return the first row after reading the first selected row from one table and the first matching row from the other and combining them, while a sort-merge join cannot return the first row until after reading and sorting all selected rows of both tables and then combining the first rows of each sorted row source.

USE_MERGE

The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is:


where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join.

Example

SELECT /*+USE_MERGE(emp dept)*/ * 
FROM emp, dept 
WHERE emp.deptno = dept.deptno; 

USE_HASH

The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is:


where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.

Example

SELECT /*+use_hash(emp dept)*/ * 
FROM emp, dept 
WHERE emp.deptno = dept.deptno; 

DRIVING_SITE

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. The syntax of this hint is:


where table is the name or alias for the table at which site the execution should take place.

Example

SELECT /*+DRIVING_SITE(dept)*/ * 
FROM emp, dept@rsite 
WHERE emp.deptno = dept.deptno;

If this query is executed without the hint, then rows from dept are sent to the local site, and the join is executed there. With the hint, the rows from emp are sent to the remote site, and the query is executed there, returning the result to the local site.

This hint is useful if you are using distributed query optimization.

See Also:

Oracle8i Distributed Database Systems 

LEADING

The LEADING hint causes Oracle to use the specified table as the first table in the join order. The syntax of the hint is:


Where table is the name or alias of a table to be used as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.

HASH_AJ and MERGE_AJ

As illustrated in Figure 7-1, the SQL IN predicate can be evaluated using a join to intersect two sets. Thus emp.deptno can be joined to dept.deptno to yield a list of employees in a set of departments.

Figure 7-1 Parallel Hash Anti-join


Alternatively, the SQL NOT IN predicate can be evaluated using an anti-join to subtract two sets. Thus emp.deptno can be anti-joined to dept.deptno to select all employees who are not in a set of departments, and you can get a list of all employees who are not in the Shipping or Receiving departments.

For a specific query, place the MERGE_AJ or HASH_AJ hints into the NOT IN subquery. MERGE_AJ uses a sort-merge anti-join and HASH_AJ uses a hash anti-join.

For example:

SELECT * FROM emp
WHERE ename LIKE 'J%' 

AND deptno IS NOT NULL 
AND deptno NOT IN (SELECT /*+ HASH_AJ */ deptno 
FROM dept
WHERE deptno IS NOT NULL 
AND loc = 'DALLAS');

If you want the anti-join transformation always to occur if the conditions in the previous section are met, then set the ALWAYS_ANTI_JOIN initialization parameter to MERGE or HASH. The transformation to the corresponding anti-join type then takes place whenever possible.

HASH_SJ and MERGE_SJ

For a specific query, place the HASH_SJ or MERGE_SJ hint into the EXISTS subquery. HASH_SJ uses a hash semi-join and MERGE_SJ uses a sort merge semi-join. For example:

SELECT * FROM dept 
WHERE exists (SELECT /*+HASH_SJ*/ * 
  FROM emp 
  WHERE emp.deptno = dept.deptno 
        AND sal > 200000); 

This converts the subquery into a special type of join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

A subquery is evaluated as a semi-join only with these limitations:

If you want the semi-join transformation always to occur if the conditions in the previous section are met, then set the ALWAYS_SEMI_JOIN initialization parameter to HASH or MERGE. The transformation to the corresponding semi-join type then takes place whenever possible.

Hints for Parallel Execution

The hints described in this section determine how statements are parallelized or not parallelized when using parallel execution.

PARALLEL

The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion.


Note:

The number of servers that can be used is twice the value in the PARALLEL hint if sorting or grouping operations also take place. 


If any parallel restrictions are violated, then the hint is ignored. The syntax is:


The PARALLEL hint must use the table alias if an alias is specified in the query. The hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies that the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism.

In the following example, the PARALLEL hint overrides the degree of parallelism specified in the emp table definition:

SELECT /*+ FULL(scott_emp) PARALLEL(scott_emp, 5) */ ename
FROM scott.emp scott_emp;

In the next example, the PARALLEL hint overrides the degree of parallelism specified in the emp table definition and tells the optimizer to use the default degree of parallelism determined by the initialization parameters. This hint also specifies that the table should be split among all of the available instances, with the default degree of parallelism on each instance.

SELECT /*+ FULL(scott_emp) PARALLEL(scott_emp, DEFAULT,DEFAULT) */ ename
FROM scott.emp scott_emp;

NOPARALLEL

You can use the NOPARALLEL hint to override a PARALLEL specification in the table clause. In general, hints take precedence over table clauses. The syntax of this hint is:


The following example illustrates the NOPARALLEL hint:

SELECT /*+ NOPARALLEL(scott_emp) */ ename
FROM scott.emp scott_emp;

PQ_DISTRIBUTE

Use the PQ_DISTRIBUTE hint to improve parallel join operation performance. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.

Use the EXPLAIN PLAN statement to identify the distribution chosen by the optimizer. The optimizer ignores the distribution hint if both tables are serial.

See Also:

For more information on how Oracle parallelizes join operations, see Oracle8i Concepts.  

The syntax of this hint is:


where:

table_name
 

Name or alias of a table to be used as the inner table of a join.  

outer_distribution 
 

The distribution for the outer table.  

inner_distribution 
 

The distribution for the inner table.  

There are six combinations for table distribution. Only a subset of distribution method combinations for the joined tables is valid, as explained in Table 7-1.

Table 7-1 Distribution Hint Combinations
Distribution  Interpretation 

Hash, Hash 

Maps the rows of each table to consumer query servers using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This hint is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort-merge join. 

Broadcast, None 

All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This hint is recommended when the outer table is very small compared to the inner table. A rule-of-thumb is: Use the Broadcast/None hint if the size of the inner table * number of query servers > size of the outer table.  

None, Broadcast 

All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This hint is recommended when the inner table is very small compared to the outer table. A rule-of-thumb is: Use the None/Broadcast hint if the size of the inner table * number of query servers < size of the outer table.  

Partition, None 

Maps the rows of the outer table using the partitioning of the inner table. The inner table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers, for example, 14 partitions and 15 query servers.

Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key.  

None, Partition 

Maps the rows of the inner table using the partitioning of the outer table. The outer table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers, for example, 14 partitions and 15 query servers.

Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key.  

None, None 

Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equi-partitioned on the join keys. 

Examples

Given two tables, R and S, that are joined using a hash-join, the following query contains a hint to use hash distribution:

SELECT <column_list> /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/
FROM r,s
WHERE r.c=s.c;

To broadcast the outer table r, the query should be:

SELECT <column list> /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */
FROM r,s
WHERE r.c=s.c;

APPEND

When you use the APPEND hint for INSERT, data is simply appended to a table. Existing free space in the blocks currently allocated to the table is not used. The syntax of this hint is:


If INSERT is parallelized using the PARALLEL hint or clause, then append mode is used by default. You can use NOAPPEND to override append mode. The APPEND hint applies to both serial and parallel insert.

The append operation is performed in LOGGING or NOLOGGING mode, depending on whether the [NO] option is set for the table in question. Use the ALTER TABLE... [NO]LOGGING statement to set the appropriate value.


Note:

Certain restrictions apply to the APPEND hint; these are detailed in Oracle8i Concepts. If any of these restrictions are violated, then the hint is ignored.  


NOAPPEND

Use NOAPPEND to override append mode.

PARALLEL_INDEX

Use the PARALLEL_INDEX hint to specify the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes. The syntax of the PARALLEL_INDEX hint is:


where:

table
 

Specifies the name or alias of the table associated with the index to be scanned. 

index
 

Specifies an index on which an index scan is to be performed (optional). 

The hint can take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table. The second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism.

Example

SELECT /*+ PARALLEL_INDEX(table1, index1, 3, 2) +/

In this example, there are 3 parallel execution processes to be used on each of 2 instances.

NOPARALLEL_INDEX

Use the NOPARALLEL_INDEX hint to override a PARALLEL attribute setting on an index. In this way you can avoid a parallel index scan operation. The syntax of this hint is:


Additional Hints

Several additional hints are included in this section:

CACHE

The CACHE hint specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. The syntax of this hint is:


In the following example, the CACHE hint overrides the table's default caching specification:

SELECT /*+ FULL (scott_emp) CACHE(scott_emp) */ ename
FROM scott.emp scott_emp;

NOCACHE

The NOCACHE hint specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache. The syntax of this hint is:


Example

SELECT /*+ FULL(scott_emp) NOCACHE(scott_emp) */ ename
FROM scott.emp scott_emp;


Note:

The CACHE and NOCACHE hints affect system statistics "table scans(long tables)" and "table scans(short tables)", as shown in the V$SYSSTAT view. 


MERGE

If a view's query contains a GROUP BY clause or DISTINCT operator in the select list, then the optimizer can merge the view's query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement, if the subquery is uncorrelated.

Complex merging is not cost-based--that is, the accessing query block must include the MERGE hint. Without this hint, the optimizer uses another approach.

Merge a view on a per-query basis by using the MERGE hint. The syntax of this hint is:


Example

SELECT /*+MERGE(v)*/ e1.ename, e1.sal, v.avg_sal 
FROM emp e1, 
  (SELECT deptno, avg(sal) avg_sal  
   FROM emp e2 
   GROUP BY deptno) v 
WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal; 


Note:

This example requires complex view merging to be enabled. 


NO_MERGE

The NO_MERGE hint causes Oracle not to merge mergeable views. The syntax of the NO_MERGE hint is:


This hint lets the user have more influence over the way in which the view is accessed.

Example

SELECT /*+NO_MERGE(dallasdept)*/ e1.ename, dallasdept.dname 
FROM emp e1, 
  (SELECT deptno, dname 
   FROM dept 
   WHERE loc = 'DALLAS') dallasdept 
WHERE e1.deptno = dallasdept.deptno; 

This causes view v not to be merged.

When the NO_MERGE hint is used without an argument, it should be placed in the view query block. When NO_MERGE is used with the view name as an argument, it should be placed in the surrounding query.

UNNEST

Setting the UNNEST_SUBQUERY session parameter to TRUE enables subquery unnesting. Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.

UNNEST_SUBQUERY first verifies if the statement is valid. If the statement is not valid, then subquery unnesting cannot proceed. The statement must then must pass a heuristic test.

See Also:

For more information on unnesting nested subqueries and the conditions that make a subquery block valid, see the Oracle8i SQL Reference. For more information on the UNNEST_SUBQUERY parameter and managing views, see Chapter 9, "Optimizing SQL Statements"

The UNNEST hint checks the subquery block for validity only. If it is valid, then subquery unnesting is enabled without Oracle checking the heuristics.

NO_UNNEST

If you enabled subquery unnesting with the UNNEST_SUBQUERY parameter, then the NO_UNNEST hint turns it off for specific subquery blocks.


Note:

The hints HASH_SJ, HASH_AJ, MERGE_SJ, and MERGE_AJ take precedence over this hint. 


Example

The following examples show situations where it might not be optimal to enable subquery unnesting.

SELECT *
FROM t_4k, t_5k
WHERE t_5k.ten = t_4k.thousand AND t_4k.thousand < 10

AND t_5k.unique3 < 10 
AND t_5k.thousand < ALL (SELECT /*+ NO_UNNEST */ z_4k.thousand
FROM z_4k
WHERE z_4k.ten < t_4k.hundred);

SELECT SUM(l_extendedprice) FROM lineitem, parts WHERE p_partkey = l_partkey and p_brand = 'Brand#23'
AND p_container = 'MED BOX'
AND l_quantity < (SELECT AVG (l_quantity)
FROM lineitem
WHERE l_partkey = p_partkey);

PUSH_PRED

Use the PUSH_PRED hint to force pushing of a join predicate into the view. The syntax of this hint is:


Example

SELECT /*+ PUSH_PRED(v) */ t1.x, v.y
FROM t1 

(SELECT t2.x, t3.y 
FROM t2, t3
WHERE t2.x = t3.x) v 
WHERE t1.x = v.x and t1.y = 1;

NO_PUSH_PRED

Use the NO_PUSH_PRED hint to prevent pushing of a join predicate into the view. The syntax of this hint is:


PUSH_SUBQ

The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible place in the execution plan. Normally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.

The hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join. The syntax of this hint is:


STAR_TRANSFORMATION

The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.

Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.

The syntax of this hint is:



See Also:

Oracle8i Concepts has a full discussion of star transformation. Also, the Oracle8i Reference describes STAR_TRANSFORMATION_ENABLED; this parameter causes the optimizer to consider performing a star transformation. 

ORDERED_PREDICATES

The ORDERED_PREDICATES hint forces the optimizer to preserve the order of predicate evaluation, except for predicates used as index keys. Use this hint in the WHERE clause of SELECT statements.

If you do not use the ORDERED_PREDICATES hint, then Oracle evaluates all predicates in the order specified by the following rules. Predicates:

The syntax of this hint is:



See Also:

Oracle8i Concepts  

Using Hints with Views

Oracle does not encourage you to use hints inside or on views (or subqueries). This is because you can define views in one context and use them in another. However, such hints can result in unexpected plans. In particular, hints inside views or on views are handled differently depending on whether the view is mergeable into the top-level query.

Should you decide, nonetheless, to use hints with views, the following sections describe the behavior in each case.

If you want to specify a hint for a table in a view or subquery, then the global hint syntax is recommended. The following section describes this in detail.

Hints and Mergeable Views

This section describes hint behavior with mergeable views.

Optimization Approaches and Goal Hints

Optimization approach and goal hints can occur in a top-level query or inside views.

Access Method and Join Hints on Views

Access method and join hints on referenced views are ignored unless the view contains a single table (or references another view with a single table). For such single-table views, an access method hint or a join hint on the view applies to the table inside the view.

Access Method and Join Hints Inside Views

Access method and join hints can appear in a view definition.

Parallel Execution Hints on Views

PARALLEL, NOPARALLEL, PARALLEL_INDEX and NOPARALLEL_INDEX hints on views are always recursively applied to all the tables in the referenced view. Parallel execution hints in a top-level query override such hints inside a referenced view.

Parallel Execution Hints Inside Views

PARALLEL, NOPARALLEL, PARALLEL_INDEX and NOPARALLEL_INDEX hints inside views are preserved when the view is merged with the top-level query. Parallel execution hints on the view in a top-level query override such hints inside a referenced view.

Hints and Nonmergeable Views

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

Because non-mergeable views are optimized separately from the top-level query, access method and join hints inside the view are always preserved. For the same reason, access method 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 non-mergeable view is similar to a table.

Global Hints

Table hints (i.e., hints that specify a table) normally refer to tables in the DELETE, SELECT, or UPDATE statement in which the hint occurs, not to tables inside any views or subqueries referenced by the statement. When you want to specify hints for tables that appear inside views or subqueries, you should use global hints instead of embedding the hint in the view or subquery. You can transform any table hint in this chapter into a global hint by using an extended syntax for the table name, as described below.

Consider the following view definitions and SELECT statement:

CREATE VIEW v1 AS

SELECT *
FROM emp
WHERE empno < 100;
CREATE VIEW v2 AS
SELECT v1.empno empno, dept.deptno deptno
FROM v1, dept
WHERE v1.deptno = dept.deptno;
SELECT /*+ INDEX(v2.v1.emp emp_empno) FULL(v2.dept) */ *
FROM v2
WHERE deptno = 20; 

The view V1 retrieves all employees whose employee number is less than 100. The view V2 performs a join between the view V1 and the department table. The SELECT statement retrieves rows from the view V2 restricting it to the department whose number is 20.

There are two global hints in the SELECT statement. The first hint specifies an index scan for the employee table referenced in the view V1, which is referenced in the view V2. The second hint specifies a full table scan for the department table referenced in the view V2. Note the dotted syntax for the view tables.

A hint such as:

INDEX(emp emp_empno)

in the SELECT statement is ignored because the employee table does not appear in the FROM clause of the SELECT statement.

The global hint syntax also applies to unmergeable views. Consider the following SELECT statement:

SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.emp emp_empno) FULL(v2.dept) */ *
FROM v2
WHERE deptno = 20;

It causes V2 not to be merged, and specifies access path hints for the employee and department tables. These hints are pushed down into the (nonmerged) view V2.

If a global hint references a UNION or UNION ALL view, then the hint is applied to the first branch that contains the hinted table. Consider the INDEX hint in the following SELECT statement:

SELECT /*+ INDEX(v.emp emp_empno) */ *
FROM (SELECT *

FROM emp
WHERE empno < 50
UNION ALL
SELECT *
FROM emp
WHERE empno > 1000) v
WHERE deptno = 20;

The INDEX hint applies to the employee table in the first branch of the UNION ALL view v, not to the employee table in the second branch.


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index