Oracle8 Tuning Release 8.0 A58246-01 |
Library | Product | Contents | Index

# 8
Optimization Modes and Hints

This chapter explains when to use the available optimization modes and how to use hints to enhance Oracle performance.

Topics include:

- Using Cost-Based Optimization
- Using Rule-Based Optimization
- Introduction to Hints
- How to Specify Hints
- Hints for Optimization Approaches and Goals
- Hints for Access Methods
- Hints for Join Orders
- Hints for Join Operations
- Hints for Parallel Execution
- Additional Hints
- Using Hints with Views

See Also: Oracle8 Concepts for an introduction to the optimizer, access methods, join operations, and parallel execution.

## Using Cost-Based Optimization

This section discusses:

- When to Use the Cost-Based Approach
- How to Use the Cost-Based Approach
- Using Histograms for Nonuniformly Distributed Data
- Generating Statistics
- Choosing a Goal for the Cost-Based Approach
- Parameters that Affect Cost-Based Optimization Plans
- Tips for Using the Cost-Based Approach

### When to Use the Cost-Based Approach

Attention: In general, you should always use the cost-based optimization approach. The rule-based approach is available for the benefit of existing applications, but all new optimizer functionality uses the cost-based approach.

The following features are available only with cost-based optimization; you must analyze your tables to get good plans:

- partitioned tables
- index-only tables
- reverse indexes
- parallel query
- star transformation
- star join

The cost-based approach generally chooses an execution plan that is as good as or better than the plan chosen by the rule-based approach, especially for large queries with multiple joins or multiple indexes. The cost-based approach also improves productivity by eliminating the need to tune your SQL statements yourself. Finally, many Oracle performance features are available only through the cost-based approach.

Cost-based optimization must be used for efficient star query performance. Similarly, it must be used with hash joins and histograms. Cost-based optimization is always used with parallel query and with partitioned tables. You must use the ANALYZE command in order to keep the statistics current.

### How to Use the Cost-Based Approach

To use cost-based optimization for a statement, first collect statistics for the tables accessed by the statement. Then enable cost-based optimization in one of these ways:

- Make sure the OPTIMIZER_MODE initialization parameter is set to its default value of CHOOSE.
- To enable cost-based optimization for your session only, issue an ALTER SESSION . . . OPTIMIZER_MODE statement with the ALL_ROWS or FIRST_ROWS option.
- To enable cost-based optimization for an individual SQL statement, use any hint other than RULE.

The plans generated by the cost-based optimizer depend on the sizes of the tables. When using the cost-based optimizer with a small amount of data to test an application prototype, do not assume that the plan chosen for the full database will be the same as that chosen for the prototype.

### Using Histograms for Nonuniformly Distributed Data

For nonuniformly distributed data, you should create histograms describing the data distribution of particular columns. For this type of data, histograms enable the cost-based optimization approach to accurately guess the cost of executing a particular statement. For data that is uniformly distributed, the optimizer does not need histograms to accurately estimate the selectivity of a query.

#### How to Use Histograms

Create histograms on columns that are frequently used in WHERE clauses of queries and have a highly skewed data distribution. You create a histogram by using the ANALYZE TABLE command. For example, if you want to create a 10-bucket histogram on the SAL column of the EMP table, issue the following statement:

    ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;

The SIZE keyword states the maximum number of buckets for the histogram. You would create a histogram on the SAL column if there were an unusual number of employees with the same salary and few employees with other salaries.

See Also: Oracle8 SQL Reference for more information about the ANALYZE command and its options.

#### Choosing the Number of Buckets for a Histogram

The default number of buckets is 75. This value provides an appropriate level of detail for most data distributions. However, since the number of buckets in the histogram, the sampling rate, and the data distribution all affect the usefulness of a histogram, you may need to experiment with different numbers of buckets to obtain the best results.

If the number of frequently occurring distinct values in a column is relatively small, then it is useful to set the number of buckets to be greater than the number of frequently occurring distinct values.

#### Viewing Histograms

You can find information about existing histograms in the database through the following data dictionary views:

USER_HISTOGRAMS
ALL_HISTOGRAMS
DBA_HISTOGRAMS

Find the number of buckets in each column's histogram in:

USER_TAB_COLUMNS
ALL_TAB_COLUMNS
DBA_TAB_COLUMNS

See Also: Oracle8 Concepts for column descriptions of data dictionary views, as well as histogram use and restrictions.

### Generating Statistics

Since the cost-based approach relies on statistics, you should generate statistics for all tables, clusters, and indexes accessed by your SQL statements before using the cost-based approach. If the size and data distribution of these tables changes frequently, generate these statistics regularly to ensure that they accurately represent the data in the tables.

Oracle can generate statistics using these techniques:

- estimation based on random data sampling
- exact computation

Use estimation, rather than computation, unless you think you need exact values, because:

- Computation always provides exact values, but can take longer than estimation. The time necessary to compute statistics for a table is approximately the time required to perform a full table scan and a sort of the rows of the table.
- Estimation is often much faster than computation, especially for large tables, because estimation never scans the entire table.

To perform a computation, Oracle requires enough space to perform a scan and sort of the table. If there is not enough space in memory, temporary space may be required. For estimations, Oracle requires enough space to perform a scan and sort of all of the rows in the requested sample of the table.

Because of the time and space required for the computation of table statistics, it is usually best to perform an estimation for tables and clusters. For indexes, computation does not take up as much time or space, so it is best to perform a computation.

When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the analyzed object, Oracle updates the existing statistics with the new ones. Oracle invalidates any currently parsed SQL statements that access any of the analyzed objects. When such a statement is next executed, the optimizer automatically chooses a new execution plan based on the new statistics. Distributed statements issued on remote databases that access the analyzed objects use the new statistics when they are next parsed.

Some statistics are always computed, regardless of whether you specify computation or estimation. If you choose estimation and the time saved by estimating a statistic is negligible, Oracle computes the statistic.

You can generate statistics with the ANALYZE command.

Example: This example generates statistics for the EMP table and its indexes:

    ANALYZE TABLE emp
       ESTIMATE STATISTICS;

### Choosing a Goal for the Cost-Based Approach

The execution plan produced by the optimizer can vary depending upon the optimizer's goal. Optimizing for best throughput is more likely to result in a full table scan rather than an index scan, or a sort-merge join rather than a nested loops join. Optimizing for best response time is more likely to result in an index scan or a nested loops join.

For example, consider a join statement that can be executed with either a nested loops operation or a sort-merge operation. The sort-merge operation may return the entire query result faster, while the nested loops operation may return the first row faster. If the goal is best throughput, the optimizer is more likely to choose a sort-merge join. If the goal is best response time, the optimizer is more likely to choose a nested loops join.

Choose a goal for the optimizer based on the needs of your application:

- For applications performed in batch, such as Oracle Reports applications, optimize for best throughput. Throughput is usually more important in batch applications, because the user initiating the application is only concerned with the time necessary for the application to complete. Response time is less important because the user does not examine the results of individual statements while the application is running.
- For interactive applications, such as Oracle Forms applications or SQL*Plus queries, optimize for best response time. Response time is usually important in interactive applications because the interactive user is waiting to see the first row accessed by the statement.
- For queries that use ROWNUM to limit the number of rows, optimize for best response time. Because of the semantics of ROWNUM queries, optimizing for response time provides the best results.

By default, the cost-based approach optimizes for best throughput. You can change the goal of the cost-based approach in these ways:

- To change the goal of the cost-based approach for all SQL statements in your session, issue an ALTER SESSION...OPTIMIZER_MODE statement with the ALL_ROWS or FIRST_ROWS option.
- To specify the goal of the cost-based approach for an individual SQL statement, use the ALL_ROWS or FIRST_ROWS hint.

Example: This statement changes the goal of the cost-based approach for your session to best response time:

    ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

### Parameters that Affect Cost-Based Optimization Plans

The following parameters affect cost-based optimization plans:

OPTIMIZER_FEATURES_ENABLED | Turns on a number of optimizer features, including: B_TREE_BITMAP_PLANS, COMPLEX_VIEW_MERGING, PUSH_JOIN_PREDICATE, FAST_FULL_SCAN_ENABLED
OPTIMIZER_MODE | As initialization parameter, sets the mode of the optimizer at instance startup: rule-based, cost based optimized for throughput or response time, or a choice based on presence of statistics. Use OPTIMIZER_MODE option of ALTER SESSION statement to change the value dynamically during a session.
OPTIMIZER_PERCENT_PARALLEL | Defines the amount of parallelism that the optimizer uses in its cost functions.
HASH_AREA_SIZE | Larger value causes hash join costs to be cheaper, giving more hash joins.
SORT_AREA_SIZE | Large value causes sort costs to be cheaper, giving more sort merge joins.
DB_FILE_MULTIBLOCK_READ_COUNT | Large value gives cheaper table scan cost and favors table scans over indexes.
COMPLEX_VIEW_MERGING | Controls complex view merging.
PUSH_JOIN_PREDICATE | Enables the optimizer to evaluate whether or not to push individual join predicates into the view query block.

ALWAYS_ANTI_JOIN | Sets the type of antijoin that Oracle uses: NESTED_LOOPS/MERGE/HASH.
HASH_JOIN_ENABLED | Enables or disables the hash join feature; should always be set to TRUE for data warehousing applications.
SORT_DIRECT_WRITES | Gives lower sort costs and more sort merge joins.

The following parameters often need to be set in a data warehousing application:

HASH_MULTIBLOCK_IO_COUNT | Larger value causes hash join costs to be cheaper, giving more hash joins.
SORT_WRITE_BUFFER_SIZE | Large value causes sort costs to be cheaper, giving more sort merge joins.
OPTIMIZER_SEARCH_LIMIT | The maximum number of tables in the FROM clause for which all possible join permutations will be considered.
BITMAP_MERGE_AREA_SIZE | The size of the area used to merge the different bitmaps that match a range predicate. Larger size will favor use of bitmap indexes for range predicates.

The following parameters rarely need to be changed:

Note: The following sort parameters can be modified using ALTER SESSION ... SET or ALTER SYSTEM ... SET DEFERRED:

SORT_AREA_SIZE
SORT_AREA_RETAINED_SIZE
SORT_DIRECT_WRITES
SORT_WRITE_BUFFERS
SORT_WRITE_BUFFER_SIZE
SORT_READ_FAC

See Also: Oracle8 Reference for complete information about each parameter.

#### COMPLEX_VIEW_MERGING

Recommended value: default

When set to FALSE, this parameter causes complex views or subqueries to be evaluated before the referencing query. In this case, you can cause a view to be merged on a per-query basis by using the MERGE hint.

When set to TRUE, this parameter causes complex views or subqueries to be merged. In this case, you can use the NO_MERGE hint within the view to prevent one particular view from being merged. Alternatively, you can use the NO_MERGE hint in the surrounding query, and specify the name of the view that should not be merged.

#### PUSH_JOIN_PREDICATE

Recommended value: TRUE

When this parameter is set to TRUE, the optimizer can evaluate, on a cost basis, whether or not to push individual join predicates into the view query block. This can enable more efficient access path and join methods, such as transforming hash joins into nested loop joins, and full table scans to index scans.

If PUSH_JOIN_PREDICATE is TRUE, you can use the NO_PUSH_JOIN_PRED hint to prevent pushing join predicates into the view.

If PUSH_JOIN_PREDICATE is FALSE, you can use the PUSH_JOIN_PRED hint to force pushing of a join predicate into the view.

### Tips for Using the Cost-Based Approach

The cost-based optimization approach assumes that a query will be executed on a multiuser system with a fairly low buffer cache hit rate. Thus a plan selected by cost-based optimization may not be the best plan for a single user system with a large buffer cache. Timing a query plan on a single user system with a large cache may not be a good predictor of performance for the same query on a busy multiuser system.

Analyzing a table uses more system resources than analyzing an index. It may be helpful to analyze the indexes for a table separately, with a higher sampling rate.

Use of access path and join method hints invokes cost-based optimization. Since cost-based optimization is dependent on statistics, it is important to analyze all tables referenced in a query that has hints, even though rule-based optimization may have been selected as the system default.

## Using Rule-Based Optimization

Rule-based optimization is supported in Oracle8, but you are advised to write any new applications using cost-based optimization. Cost-based optimization should be used for new applications and for data warehousing applications, because it supports new and enhanced features. Much of the functionality in Oracle8 (such as hash joins, improved star query processing, and histograms) is available only through cost-based optimization.

If you have developed existing OLTP applications using version 6 of Oracle and have tuned your SQL statements carefully based on the rules of the optimizer, you may want to continue using rule-based optimization when you upgrade these applications to Oracle8.

If you neither collect statistics nor add hints to your SQL statements, your statements will use rule-based optimization. However, you should eventually migrate your existing applications to use the cost-based approach, because the rule-based approach will not be available in future versions of Oracle.

If you are using an application provided by a third-party vendor, check with the vendor to determine which type of optimization is best suited to that application.

You can enable cost-based optimization on a trial basis simply by collecting statistics. You can then return to rule-based optimization by deleting them or by setting either the value of the OPTIMIZER_MODE initialization parameter or the OPTIMIZER_MODE option of the ALTER SESSION command to RULE. You can also use this value if you want to collect and examine statistics for your data without using the cost-based approach.

## Introduction to Hints

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

Hints are suggestions that you give the optimizer for optimizing a SQL statement. Hints allow you to make decisions usually made by the optimizer. You can use hints to specify

- the optimization approach for a SQL statement
- the goal of the cost-based approach for a SQL statement
- the access path for a table accessed by the statement
- the join order for a join statement
- a join operation in a join statement

Note, however, that the use of hints involves extra code that must also be managed, checked, controlled.

## How to Specify 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:

- a simple SELECT, UPDATE, or DELETE statement
- a parent statement or subquery of a complex statement
- a part of a compound query

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 this 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 Oracle8 SQL Reference.

A statement block can have only one comment containing hints. This comment can only follow the SELECT, UPDATE, or DELETE 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 DELETE, SELECT, or UPDATE keyword that begins a statement block. Comments containing hints can appear only after these keywords.
+ | Is a plus sign that 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, 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, Oracle ignores them but does not return an error:

- Oracle ignores hints if the comment containing them does not follow a DELETE, SELECT, or UPDATE keyword.
- Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same comment.
- Oracle ignores combinations of conflicting hints, but considers other hints within the same comment.
- Oracle also ignores hints in all SQL statements in those environments that use PL/SQL Version 1, such as SQL*Forms Version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5.

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

The following sections show the syntax of each hint.

## 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 and, with the cost-based approach, between the goals of best throughput and best response time.

- ALL_ROWS
- FIRST_ROWS
- CHOOSE
- RULE

If a SQL statement contains a hint that specifies an optimization approach and goal, 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 command.

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).

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 these choices:

- If an index scan is available, the optimizer may choose it over a full table scan.
- If an index scan is available, the optimizer may choose a nested loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops.
- If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation.

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:

- set operators (UNION, INTERSECT, MINUS, UNION ALL)
- GROUP BY clause
- FOR UPDATE clause
- group functions
- DISTINCT operator

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, the optimizer uses the cost-based approach and optimizes for best throughput.

If you specify either the ALL_ROWS or FIRST_ROWS hint in a SQL statement and the data dictionary contains no statistics about any of the tables accessed by the statement, 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 generated by the ANALYZE command; therefore, you should use the ANALYZE command to generate statistics for all tables accessed by statements that use cost-based optimization. If you specify hints for access paths or join operations along with either the ALL_ROWS or FIRST_ROWS hint, 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 approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement. If the data dictionary contains statistics for at least one of these tables, the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary contains no statistics for any of these tables, the optimizer uses the rule-based approach.

Syntax of this hint is as follows:

For 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 any other hints specified for the statement block.

Syntax of this hint is as follows:

For 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 versions of Oracle.

## Hints for Access Methods

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

- FULL
- ROWID
- CLUSTER
- HASH
- HASH_AJ
- INDEX
- INDEX_ASC
- INDEX_COMBINE
- INDEX_DESC
- INDEX_FFS
- MERGE_AJ
- AND_EQUAL
- USE_CONCAT

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 the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, 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, 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.

### FULL

The FULL hint explicitly chooses a full table scan for the specified table.

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.

For 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 an 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.

### 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.

The following example illustrates the use of the CLUSTER hint.

    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.

### HASH_AJ

The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access the specified table. The syntax of the HASH_AJ hint is:

### HASH_SJ

The HASH_SJ hint transforms a correlated EXISTS subquery into a hash semi-join to access the specified table. The syntax of the HASH_SJ hint is:

### INDEX

The INDEX hint explicitly chooses an index scan for the specified table. 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:

- If this hint specifies a single available index, the optimizer performs a scan on this index. The optimizer does not consider a full table scan or a scan on another index on the table.
- If this hint specifies a list of available indexes, the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The optimizer may also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan or a scan on an index not listed in the hint.
- If this hint specifies no indexes, the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The optimizer may also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.

For example, consider this query, which 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, 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, 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.

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, 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, since there are few
                 male patients  */
           name, height, weight
      FROM patients
     WHERE sex = 'M';

The INDEX hint applies to inlist predicates; it forces the optimizer to use the hinted index, if possible, for an inlist predicate. Multi-column inlists 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, 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 currently 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

If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use on the table whatever Boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to use some Boolean combination of those particular bitmap indexes. The syntax of INDEX_COMBINE is:

### INDEX_DESC

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

Each parameter serves the same purpose as in the INDEX hint. This hint has no effect on SQL statements that access more than one table. Such statements always perform range scans in ascending order of the indexed values.

### 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:

See Also: "Fast Full Index Scan" on page 10-9

### MERGE_AJ

The MERGE_AJ hint transforms a NOT IN subquery into a merge anti-join to access the specified table. The syntax of the MERGE_AJ hint is:

### MERGE_SJ

The MERGE_SJ hint transforms a correlated EXISTS subquery into a merge semi-join to access the specified table. The syntax of the MERGE_SJ hint is:

### 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 inlist processing and OR-expands all disjunctions, including inlists.

Syntax of this hint is:

## Hints for Join Orders

The hints in this section suggest join orders:

- ORDERED
- STAR

### ORDERED

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

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, 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 would allow 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 3 tables, the large table's concatenated index has at least 3 columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.

Syntax of this hint is:

Usually, if you analyze the tables the optimizer will choose 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: Oracle8 Concepts for more information about star plans.

## Hints for Join Operations

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

- USE_NL
- USE_MERGE
- USE_HASH
- USE_HASH
- DRIVING_SITE

You must specify a table to be joined exactly as it appears in the statement. If the statement uses an alias for the table, 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;

Since the default goal of the cost-based approach is best throughput, the optimizer will choose 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, 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) Use N-L 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.

### 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.

### 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. 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, rows from DEPT will be sent to the local site and the join will be executed there. With the hint, the rows from EMP will be sent to the remote site and the query will be executed there, returning the result to the local site.

## Hints for Parallel Execution

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

- PARALLEL
- NOPARALLEL
- APPEND
- NOAPPEND
- PARALLEL_INDEX
- NOPARALLEL_INDEX

See Also: Chapter 19, "Tuning 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. If any parallel restrictions are violated, 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. Note, in general, that hints take precedence over table clauses. Syntax of this hint is:

The following example illustrates the NOPARALLEL hint:

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

The NOPARALLEL hint is equivalent to specifying the hint

    /*+ PARALLEL(table,1,1) */

### APPEND

When you use the APPEND hint for INSERT, data is simply appended to a table. Existing free space in the block is not used. Syntax of this hint is:

If INSERT is parallelized using the PARALLEL hint or clause, append mode will be used by default. You can use NOAPPEND to override append mode. Note that 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]LOGGING option is set for the table in question. Use the ALTER TABLE [NO]LOGGING statement to set the appropriate value.

Certain restrictions apply to the APPEND hint; these are detailed in Oracle8 Concepts. If any of these restrictions are violated, the hint will be ignored.

### NOAPPEND

You can 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.

For example:

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

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

### NOPARALLEL_INDEX

You can 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
- NOCACHE
- MERGE
- NO_MERGE
- PUSH_JOIN_PRED
- NO_PUSH_JOIN_PRED
- PUSH_SUBQ
- STAR_TRANSFORMATION

### 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. 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. Syntax of this hint is:

The following example illustrates the NOCACHE hint:

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

### MERGE

When the COMPLEX_VIEW_MERGING parameter is set to FALSE, this parameter causes complex views or subqueries to be evaluated before the surrounding query. In this case, you can cause a view to be merged on a per-query basis by using the MERGE hint. Syntax of this hint is:

For example:

    SELECT /*+ MERGE(v) */ t1.x, v.avg_y
    FROM t1

    (SELECT x, avg(y) AS avg_y
    FROM t2
    GROUP BY x) v

WHERE t1.x = v.x AND t1.y = 1;

### NO_MERGE

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

This hint allows the user to have more influence over the way in which the view will be accessed. For example,

    SELECT /*+ NO_MERGE(v) */ t1.x, v.avg_y
    FROM t1

    (SELECT x, avg(y) AS avg_y
    FROM t2
    GROUP BY x) v

    WHERE t1.x = v.x AND t1.y = 1;

causes view v not to be merged.

When COMPLEX_VIEW_MERGING is set to TRUE, you can use the NO_MERGE hint within the view to prevent one particular query from being 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.

### PUSH_JOIN_PRED

When the PUSH_JOIN_PREDICATE parameter is TRUE, the optimizer can evaluate, on a cost basis, whether or not to push individual join predicates into the view. This can enable more efficient access paths and join methods, such as transforming hash joins into nested loop joins, and full table scans to index scans.

If the PUSH_JOIN_PREDICATE parameter is FALSE, you can use the PUSH_JOIN_PRED hint to force pushing of a join predicate into the view.

Syntax of this hint is:

For example:

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

    (SELECT t2.x, t3.y
    FROM t2, t3
    SHERE t2.x = t3.x) v

WHERE t1.x = v.x AND t1.y = 1;

### NO_PUSH_JOIN_PRED

If PUSH_JOIN_PREDICATE is TRUE, you can use the NO_PUSH_JOIN_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 nonmerged 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, it will improve 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. 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.

Note that even if the hint is given, there is no guarantee that the transformation will take place. The optimizer will only generate the subqueries if it seems reasonable to do so. If no subqueries are generated, there is no transformed query, and the best plan for the untransformed query will be used regardless of the hint. The syntax of this hint is:

See Also: Oracle8 Concepts for a full discussion of star transformation.
Oracle8 Reference describes STAR_TRANSFORMATION_ENABLED; this parameter causes the optimizer to consider performing a star transformation.

## Using Hints with Views

Oracle Corporation does not encourage users to use hints inside or on views (or subqueries). This is because views can be defined in one context and used in another; such hints can result in unexpected plans. In particular, hints inside views or on views are handled differently depending on whether or not 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.

- Hints and Mergeable Views
- Hints and Nonmergeable Views

### 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.

- If there is such a hint in the top-level query, that hint is used regardless of any such hints inside the views.
- If there is no top-level optimizer mode hint, then mode hints in referenced views are used as long as all mode hints in the views are consistent.
- If two or more mode hints in the referenced views conflict, then all mode hints in the views are discarded and the session mode is used, whether default or user-specified.

#### 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.

- If the view is a subquery (that is, if it appears in the FROM clause of a SELECT statement), then all access method and join hints inside the view are preserved when the view is merged with the top-level query.
- For views that are not subqueries, access method and join hints in the view are preserved only if the top-level query references no other tables or views (that is, if the FROM clause of the SELECT statement contains only the view).

#### 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 refere)ced view.

### 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.

Since n-nmergeable 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 since, in this case, a nonmergeable view is similar to a table.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index