Oracle7 Server Tuning

How to Write New SQL Statements

If you are writing SQL statements in a new application, follow these steps to optimize your statements:

Create indexes that can be used by your statements.

Create clusters to optimize your join statements.

Create hash clusters that can be used by your statements.

Choose an optimization approach for your statements.

Use hints where appropriate in your statements.

Compare alternative syntax for your statements.

This section discusses each of these steps.

How to Use Indexes

This section makes recommendations about creating indexes and discusses these issues:

how to decide when to create indexes

how to choose which columns to index

how to use composite indexes

how to write statements to use indexes

Once you have decided to create an index, you can create it with the CREATE INDEX command. For more information on creating indexes, see the Oracle7 Server Application Developer's Guide.

When to Create Indexes

Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, you should create indexes on tables that are often queried for less than 2% or 4% of the table's rows. This guideline is based on these assumptions:

Rows with the same value for the column on which the query is based are uniformly distributed throughout the data blocks allocated to the table.

Rows in the table are randomly ordered with respect to the column on which the query is based.

Each data block allocated to the table contains at least 10 rows.

The table contains a relatively small number of columns.

Most queries on the table have relatively simple WHERE clauses.

The cache hit ratio is low and there is no operating system cache.

If these assumptions do not describe the data in your table and the queries that access it, the percentage of the table's rows selected under which an index is helpful may increase to as much as 25%.

How to Choose Columns to Index

Follow these guidelines for choosing columns to index:

Consider indexing columns that are used frequently in WHERE clauses.

Consider indexing columns that are used frequently to join tables in SQL statements. For more information on optimizing joins, see the section "How to Use Clusters" .

Only index columns with good selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the indexed column. An index's selectivity is good if few rows have the same value.

Note: Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints. These indexes are the most selective and the most effective in optimizing performance.

You can determine the selectivity of an index by dividing the number of rows in the table by the number of distinct indexed values. You can obtain these values using the ANALYZE command. A selectivity calculated in this manner should be interpreted as a percentage.

Do not index columns with few distinct values. Such columns usually have poor selectivity and, therefore, do not optimize performance unless the frequently selected column values appear less frequently than the other column values.

For example, consider a column containing equal numbers of the values 'YES' and 'NO'. Indexing this column would not normally improve performance. However, if the value 'YES' appears relatively infrequently and your application often queries for 'YES', then indexing the column may improve performance.

Do not index columns that are frequently modified. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables .

Do not index columns that only appear in WHERE clauses with functions or operators. A WHERE clause that uses a function (other than MIN or MAX) or an operator with an indexed column does not make available the access path that uses the index.

Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows Oracle to modify data in the child table without locking the parent table.

Appendix A, "Performance Diagnostic Tools"

How to Choose Composite Indexes

A composite index is an index that is made up of more than one column. Composite indexes can provide additional advantages over single-column indexes:

better selectivity Sometimes two or more columns, each with poor selectivity, can be combined in a composite index with good selectivity.
additional data storage If all the columns selected by a query are in a composite index, Oracle can return these values from the index without accessing the table.

A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:

CREATE INDEX comp_ind
   ON tab1(x, y, z);

These combinations of columns are leading portions of the index: X, XY, and XYZ. These combinations of columns are not leading portions of the index: YZ and Z.

Follow these guidelines for choosing columns for composite indexes:

Consider creating a composite index on columns that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either column individually.

If several queries select the same set of columns based on one or more column values, consider creating a composite index containing all of these columns.

Of course, consider the guidelines associated with the general performance advantages and tradeoffs of indexes described in the previous sections. Follow these guidelines for ordering columns in composite indexes:

Create the index so that the columns that are used in WHERE clauses make up a leading portion.

If some of the columns are used in WHERE clauses more frequently, be sure to create the index so that the more frequently selected columns make up a leading portion to allow the statements that use only these columns to use the index.

If all columns are used in WHERE clauses equally often, ordering these columns from most selective to least selective in the CREATE INDEX statement best improves query performance.

If all columns are used in the WHERE clauses equally often but the data is physically ordered on one of the columns, place that column first in the composite index.

How to Write Statements That Use Indexes

After you create an index, the optimizer cannot use an access path that uses the index simply because the index exists. The optimizer can only choose such an access path for a SQL statement if it contains a construct that makes the access path available. For information on access paths and the constructs that make them available, see Chapter 5, "The Optimizer".

To be sure that a SQL statement can use an access path that uses an index, be sure the statement contains a construct that makes such an access path available. If you are using the cost-based approach, you should also generate statistics for the index. Once you have made the access path available for the statement, the optimizer may or may not choose to use the access path, based on the availability of other access paths.

How to Write Statements That Avoid Using Indexes

In some cases, you may want to prevent a SQL statement from using an access path that uses an existing index. You may want to do this if you know that the index is not very selective and that a full table scan would be more efficient. If the statement contains a construct that makes such an index access path available, you can force the optimizer to use a full table scan through one of these methods:

You can make the index access path unavailable by modifying the statement in a way that does not change its meaning. The following example illustrates this method.

You can use the FULL hint to force the optimizer to choose a full table scan instead of an index scan.

You can use the INDEX or AND_EQUAL hint to force the optimizer to use one index or set of indexes instead of another.

Since the behavior of the optimizer may change in future versions of Oracle, relying on methods such as the first to choose access paths may not be a good long-range plan. Instead, use hints to suggest specific access paths to the optimizer. For information on hints, see the section "How to Use Hints"

Example

Consider these queries that select rows from a table based on the value of a single column:

SELECT *
   FROM tab1
   WHERE col1 = 'A'
SELECT *
   FROM tab1
   WHERE col1 = 'B';

Assume that the values of the COL1 column are the letters A through Z. Assume also that the table has 1000 rows and that 75% of those rows have a COL1 value of 'A'. Each of the other letters appears in 1% of the rows.

Since the value 'A' appears in 75% of the tables rows, the first query is likely to be executed faster with a full table scan than with an index scan using an index on the COL1 column. Since the value 'B' appears in 1% of the rows, an index scan is likely to be faster than a full table scan for the second query. For these reasons, it is desirable to create an index to be used by the second query, but it is not desirable to use this index for the first query. However, the number of occurrences of each distinct column value is not available to the optimizer. The optimizer is likely to choose the same access path for both of these queries, despite the disparity in the percentage of the table's rows each returns.

For the best performance of these queries, create an index on TAB1.COL1 so that it can be used by the second query:

CREATE INDEX col1_ind
   ON tab1(col1);

Modify the WHERE clause of the first query so that it does not make available the access path that uses the COL1_IND index:

SELECT *
   FROM tab1
   WHERE col1 || '' = 'A';

This change prevents the query from using the access path provided by COL1_IND. Index access paths are not available if the WHERE clause performs an operation or function on the indexed column. For this reason, the optimizer must choose a full table scan for this query.

Note: This change to the WHERE clause does not change the result of the condition, so it does not cause the query to return a different set of rows. For a column containing number or date data, you can achieve the same goal by modifying the WHERE clause condition so that the column value is added to 0.

How to Use Clusters

Follow these guidelines for choosing when to cluster tables:

Consider clustering tables that are often accessed by your application in join statements.

Do not cluster tables if your application only joins them occasionally or modifies their common column values frequently. Modifying a row's cluster key value takes longer than modifying the value in an unclustered table because Oracle may have to migrate the modified row to another block to maintain the cluster.

Do not cluster tables if your application often performs full table scans of only one of the tables. A full table scan of a clustered table can take longer than a full table scan of an unclustered table. Oracle is likely to read more blocks since the tables are stored together.

Consider clustering master-detail tables if you often select a master record and then the corresponding detail records. Since the detail records are stored in the same data block(s) as the master record, they are likely to still be in memory when you select them, so Oracle may perform less I/O.

Consider storing a detail table alone in a cluster if you often select many detail records of the same master. This measure improves the performance of queries that select detail records of the same master but does not decrease the performance of a full table scan on the master table.

Do not cluster tables if the data from all tables with the same cluster key value exceeds more than one or two Oracle blocks. To access a row in a clustered table, Oracle reads all blocks containing rows with that value. If these rows take up multiple blocks, accessing a single row could require more reads than accessing the same row in an unclustered table.

Consider the benefits and drawbacks of clusters with respect to the needs of your application. For example, you may decide that the performance gain for join statements outweighs the performance loss for statements that modify cluster key values. You may want to experiment and compare processing times with your tables both clustered and stored separately. To create a cluster, use the CREATE CLUSTER command. For more information on creating clusters, see the Oracle7 Server Application Developer's Guide.

How to Use Hashing

Follow these guidelines for choosing when to use hash clusters:

Consider using hash clusters to store tables that are often accessed by SQL statements with WHERE clauses that contain equality conditions that use the same column or combination of columns. Designate this column or combination of columns as the cluster key.

Store a table in a hash cluster if you can determine how much space is required to hold all rows with a given cluster key value, including rows to be inserted immediately as well as rows to be inserted in the future.

Do not use hash clusters if space in your database is scarce and you cannot afford to allocate additional space for rows to be inserted in the future.

Do not use a hash cluster to store a constantly growing table if the process of occasionally creating a new, larger hash cluster to hold that table is impractical.

Do not store a table in a hash cluster if your application often performs full scans of the table and you feel you must allocate a great deal of extra space to the hash cluster in anticipation of the table growing a great deal in the future. Such full table scans must read all blocks allocated to the hash cluster, even though some blocks may contain few rows. Storing the table alone would reduce the number of blocks read by full table scans.

Do not store a table in a hash cluster if your application frequently modifies the cluster key values. Modifying a row's cluster key value can take longer than modifying the value in an unclustered table because Oracle may have to migrate the modified row to another block to maintain the cluster.

Storing a single table in a hash cluster can be useful, regardless of whether the table is often joined with other tables, provided that hashing is appropriate for the table based on the previous points in this list.

Consider the benefits and drawbacks of hash clusters with respect to the needs of your application. You may want to experiment and compare processing times with a table both stored in a hash cluster and stored alone with an index. To create a hash cluster, use the CREATE CLUSTER command with the HASH and HASHKEYS parameters. For more information on creating hash clusters, see the Oracle7 Server Application Developer's Guide.

How to Determine How Many Hash Values to Use

When you create a hash cluster, you must use the HASHKEYS parameter of the CREATE CLUSTER statement to specify the number of hash values for the hash cluster. For best performance of hash scans, choose a HASHKEYS value that is at least as large as the number of cluster key values. Such a value reduces the chance of collisions, or multiple cluster key values resulting in the same hash value. Collisions force Oracle to test the rows in each block for the correct cluster key value after performing a hash scan. Collisions reduce the performance of hash scans.

Oracle always rounds up the HASHKEYS value that you specify to the nearest prime number to obtain the actual number of hash values. This rounding is designed to reduce collisions.

How to Choose an Optimization Approach

This section discusses

when to use the cost-based approach

how to choose a goal for the cost-based approach

when and how to generate statistics for the cost-based approach

when to use the rule-based approach

When to Use the Cost-Based Approach

In general, you should use the cost-based approach for all new applications. 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 for you to tune your SQL statements yourself.

To enable cost-based optimization for a statement, collect statistics for the tables accessed by the statement and be sure the OPTIMIZER_MODE initialization parameter is set to its default value of CHOOSE.

You can also enable cost-based optimization in these ways:

To enable cost-based optimization for your session only, issue an ALTER SESSION statement with an OPTIMIZER_GOAL option value of ALL_ROWS or FIRST_ROWS.

To enable cost-based optimization for an individual SQL statement, use the ALL_ROWS or FIRST_ROWS hint. For information on hints, see the section "How to Use Hints" .

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, you should 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:

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 with a 20% sample size 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 indexed 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 statement with the OPTIMIZER_GOAL option.

To specify the goal of the cost-based approach for an individual SQL statement, use the ALL_ROWS or FIRST_ROWS hint. For information on hints, see the section "How to Use Hints" .

Example

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

ALTER SESSION
   SET OPTIMIZER_GOAL = FIRST_ROWS;

When to Use Rule-Based Optimization

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

If you neither collect statistics nor add hints to your SQL statements, your statements will continue to 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.

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_GOAL parameter 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.

How to Use 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

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. For more information on comments, see Chapter 2, "Elements of SQL", of the Oracle7 Server 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.

where:

DELETE SELECT UPDATE Is a DELETE, SELECT, or UPDATE keyword that begins a statement block. Comments containing hints can only appear after these keywords.
+ Is a plus sign that causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after 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 environments that use PL/SQL Version 1, such as SQL*Forms Version 3 triggers.

The optimizer only recognizes hints 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. 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_GOAL parameter of the ALTER SESSION command.

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

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. Since these estimates may not be as accurate as those generated by the ANALYZE command, 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.

In the following statement, if statistics are present for the EMP table, the optimizer uses the cost-based approach. If no statistics for the EMP table exist in the data dictionary, the optimizer uses the rule-based approach.

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

RULE

The RULE hint explicitly chooses rule-based optimization for a statement block. This hint also causes the optimizer to ignore any other hints specified for the statement block. 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, will not be available in future versions 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 the syntactic constructs of the SQL statement. For a discussion of access methods and a list of constructs and the access paths they make available, see Chapter 5, "The Optimizer". 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 name or alias must represent a table or a synonym for a table on your local database.

FULL

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

FULL(table)

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

ROWID(table)

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. The syntax of the CLUSTER hint is

CLUSTER(table)

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. The syntax of the HASH hint is

HASH(table)

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. 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';

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, since Oracle Corporation does not guarantee that the default behavior for an index range scan will remain the same in future versions of Oracle, you may want to use the INDEX_ASC hint to specify ascending range scans explicitly, should the default behavior change.

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. The syntax of the INDEX_DESC 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. For example, consider this table, which contains the temperature readings of a tank of water holding marine life:

CREATE TABLE tank_readings
   (time         DATE    CONSTRAINT un_time UNIQUE,
    temperature  NUMBER );

Each of the table's rows stores a time and the temperature measured at that time. A UNIQUE constraint on the TIME column ensures that the table does not contain more than one reading for the same time.

Oracle enforces this constraint with an index on the TIME column. Consider this complex query, which selects the most recent temperature reading taken as of a particular time T. The subquery returns either T or the latest time before T at which a temperature reading was taken. The parent query then finds the temperature taken at that time:

SELECT temperature
   FROM tank_readings
   WHERE time = (SELECT MAX(time)
      FROM tank_readings
         WHERE time <= TO_DATE(:t) );

The execution plan for this statement looks like the following figure:

Figure 7 - 1. Execution Plan without Hints

To execute this statement, Oracle performs these operations:

Steps 4 and 3 execute the subquery:

Step 4 performs a range scan of the UN_TIME index to return all the TIME values less than or equal to T.

Step 3 chooses the greatest TIME value from Step 4 and returns it.

Steps 2 and 1 execute the parent query:

Step 2 performs a unique scan of the UN_TIME index based on the TIME value returned by Step 3 and returns the associated ROWID.

Step 1 accesses the TANK_READINGS table using the ROWID returned by Step 2 and returns the TEMPERATURE value.

In Step 4, Oracle scans the TIME values in the index in ascending order beginning with the smallest. Oracle stops scanning at the first TIME value greater than T and then returns all the values less than or equal to T to Step 3. Note that Step 3 needs only the greatest of these values. Using the INDEX_DESC hint, you can write an equivalent query that reads only one TIME value from the index:

SELECT /*+ INDEX_DESC(tank_readings un_time) */ temperature
   FROM tank_readings
   WHERE time <= TO_DATE(:t)
      AND ROWNUM = 1
   ORDER BY time DESC;

The execution plan for this query looks like the following figure:

Figure 7 - 2. Execution Plan wile Using the INDEX_DESC Hint

To execute this statement, Oracle performs these operations:

Step 3 performs a range scan of the UN_TIME index searching for TIME values less than or equal to T and returns their associated ROWIDs.

Step 2 accesses the TANK_READINGS table by the ROWIDs returned by Step 3.

Step 1 enforces the ROWNUM=1 condition by requesting only one row from Step 2.

Because of the INDEX_DESC hint, Step 3 scans the TIME values in the index in descending order beginning at T. The first TIME value scanned is either T (if the temperature was taken at T) or the greatest TIME value less than T. Since Step 1 requests only one row, Step 3 scans no more index entries after the first.

Since the default behavior is an ascending index scan, issuing this query without the INDEX_DESC hint would cause Oracle to begin scanning at the earliest time in the table, rather than at the latest time less than or equal to T. Step 1 would then return the temperature at the earliest time. You must use this hint to make this query return the same temperature as the complex query described earlier in this section.

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.

Hint for Join Orders

The ORDERED hint suggests a join order.

ORDERED

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. 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.

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, you must use the alias rather than the table name in the hint. The name or alias must represent a table or a synonym for a table on your local database.

The USE_NL and USE_MERGE hints must be used 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.

Hints for Parallel Query Execution

Each hint described in this section determines how statements are parallelized or not parallelized when using the parallel query option. Refer to Chapter 6, "Parallel Query Option", for more information on the parallel query option.

PARALLEL

The PARALLEL hint allows you to specify the desired number of concurrent query servers that can be used for the query. The syntax is

The PARALLEL hint must use the table alias if an alias is specified in the query. The PARALLEL 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 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;

NOPARALEL

The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clause. 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) */.

CACHE

The CACHE hint specifies that the blocks retrieved for the table in the hint 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. 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 following example illustrates the NOCACHE hint:

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

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 will have no effect if the subquery is applied to a remote table or one that is joined using a merge join.

Considering Alternative Syntax

Because SQL is a flexible language, more than one SQL statement may meet the needs of your application. Although two SQL statements may produce the same result, Oracle may process one faster than the other. You can use the results of the EXPLAIN PLAN statement to compare the execution plans and costs of the two statements and determine which is more efficient.

This example shows the execution plans for two SQL statements that perform the same function. Both statements return all the departments in the DEPT table that have no employees in the EMP table. Each statement searches the EMP table with a subquery. Assume there is an index, DEPTNO_INDEX, on the DEPTNO column of the EMP table.

This is the first statement and its execution plan :

SELECT dname, deptno
   FROM dept
   WHERE deptno NOT IN
      (SELECT deptno FROM emp);

Figure 7 - 3. Execution Plan with Two Full Table Scans

Step 3 of the output indicates that Oracle executes this statement by performing a full table scan of the EMP table despite the index on the DEPTNO column. This full table scan can be a time-consuming operation. Oracle does not use the index because the subquery that searches the EMP table does not have a WHERE clause that makes the index available.

However, this SQL statement selects the same rows by accessing the index :

SELECT dname, deptno
   FROM dept
   WHERE NOT EXISTS
      (SELECT deptno
         FROM emp
			WHERE dept.deptno = emp.deptno);

Figure 7 - 4. Execution Plan with a Full Table Scan and an Index Scan

The WHERE clause of the subquery refers to the DEPTNO column of the EMP table, so the index DEPTNO_INDEX is used. The use of the index is reflected in Step 3 of the execution plan. The index range scan of DEPTNO_INDEX takes less time than the full scan of the EMP table in the first statement. Furthermore, the first query performs one full scan of the EMP table for every DEPTNO in the DEPT table. For these reasons, the second SQL statement is faster than the first.

If you have statements in your applications that use the NOT IN operator, as the first query in this example does, you should consider rewriting them so that they use the NOT EXISTS operator. This would allow such statements to use an index, if one exists.

better selectivity	Sometimes two or more columns, each with poor selectivity, can be combined in a composite index with good selectivity.
additional data storage	If all the columns selected by a query are in a composite index, Oracle can return these values from the index without accessing the table.

DELETE SELECT UPDATE	Is a DELETE, SELECT, or UPDATE keyword that begins a statement block. Comments containing hints can only appear after these keywords.
+	Is a plus sign that causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after 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.

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.