Tchater maintenant avec le support
Tchattez avec un ingénieur du support

Spotlight on Oracle 11.0 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Troubleshooting: Connection Problems

Optimizer Hints

Optimizer hints appear as a comment following the first word of the SQL statement (for example, SELECT, INSERT, DELETE, or UPDATE). Hints are differentiated from other comments by the presence of the plus sign (+) following the opening comment delimiter (/*). For instance, the FULL hint in the following example tells the optimizer to perform a full table scan when resolving the query:

SELECT /*+ FULL(E) */ *

FROM EMPLOYEE E

WHERE SALARY > 1000000

The following list shows the hints that can be used:

Hint Description

ALL_ROWS

Use the cost-based optimizer and optimize for the retrieval of all rows.

AND_EQUAL (table_name index_name index_name ....)

Retrieve rows from the specified table using each of the specified indexes and merge the results.

APPEND

Invokes a direct load insert. Only valid for insert … select from statements.

BITMAP (table_name index_name)

Retrieve rows from the specified table using the specified bitmap index.

CACHE (table_name)

Encourages rows retrieved by a full table scan to remain in the buffer cache of the SGA.

CHOOSE

If statistics have been collected for any table involved in the SQL statement, use cost-based or all-rows optimization, otherwise use rule-based optimization.

CLUSTER (table_name)

Uses a cluster scan to retrieve table rows.

DRIVING_SITE (table_name)

For a distributed SQL statement, this causes the site at which the specified table resides to be the driving site.

FIRST_ROWS

Specifies that the cost-based optimizer should optimize the statement to reduce the cost of retrieving the first row only.

FULL (table_name)

Use a full table scan to retrieve rows from the specified table.

HASH (table_name)

Use a hash scan to retrieve rows from the specified table. The table must be stored in a hash cluster.

HASH_AJ

Perform an anti-join using hash join methodology. This hint must appear after the select statement, not in sub-query.

HASH_SJ

Appears within an EXISTS sub-query. Invokes a hash semi-join.

INDEX (table_name [index_name])

Uses the specified index to retrieve rows from the table or, if no index is specified, uses any index.

INDEX_ASC (table_name [index_name])

Specifies an ascending index range scan using the specified index or, if no index is specified, any suitable index.

INDEX_COMBINE (table_name [index_name…])

Instructs the optimizer to combine the specified bitmap indexes. If no bitmap indexes are specified, the optimizer chooses suitable bitmap indexes.

INDEX_DESC (table_name [index_name])

Specifies a descending index range scan using the specified index or, if no index is specified, any suitable index.

INDEX_FFS (table_name [index_name])

Invokes a fast full index scan using the specified index or, if no index is specified, any suitable index. A fast full scan reads the entire index in block order, using multi-block reads and possibly parallel query.

MERGE

Instructs the optimizer to perform complex view merging when resolving a query based on a view, or one that includes a sub-query in the WHERE clause.

NO_MERGE

Instructs the optimizer not to perform complex view merging when resolving a query based on a view, or one that includes a sub-query in the WHERE clause.

MERGE_AJ

Performs an anti-join using sort-merge join method. This hint must appear after the SELECT statement, not in a sub-query.

MERGE_SJ

Appears within an EXISTS sub-query. Invokes a sort-merge semi-join.

NO_EXPAND (table_name)

Oracle sometimes expands statements with OR conditions into multiple SQL statements combined by a union operation. This hint instructs the optimizer not to do this, even if it calculates that such a transformation would be beneficial.

NO_INDEX (table_name [index_name] )

No index suppresses the use of the named indexes or, if no indexes are specified, all indexes on the named table.

NO_PUSH_PRED

Instructs not to push join conditions from the WHERE clause into a view or sub-query.

NOAPPEND

Suppresses direct load insert in an INSERT… SELECT FROM... statement.

NOCACHE (table_name)

Discourages Oracle from keeping rows retrieved by a full table scan in the buffer cache of the SGA. Overrides the cache setting on the CREATE or ALTER TABLE statement.

NOPARALLEL (table_name)

Do not use parallel processing for the SQL statement. Overrides the parallel setting on the CREATE or ALTER TABLE statement.

NOPARALLEL_INDEX (table_name index_name)

Suppresses parallelism in fast full index scans or in partitioned index access.

NOREWRITE

Prevents the SQL statement from being rewritten to take advantage of materialized views. It overrides the server parameter query_rewrite_enabled.

ORDERED

Instructs the optimizer to join the tables in exactly the left to right order specified in the FROM clause.

ORDERED_PREDICATES

Causes predicates in the WHERE clause to be evaluated in the order in which they appear in the WHERE clause.

PARALLEL (table_name , degree_of_parallelism)

Instructs the optimizer to perform parallel scans on the named table. If no degree of parallelism is specified, the default is used.

PARALLEL_INDEX (table_name [index_name])

Parallelizes a fast full index scan, or an index scan against a partitioned index.

PQ_DISTRIBUTE (table_name outer_distribution inner_distribution)

This query determines how a parallel join using table_name is executed. Valid options for outer_distribution and inner_distribution are (not all combinations are valid) hash, broadcast, none, partition.

PUSH_JOIN_PRED/

PUSH_PRED

Push join conditions from the WHERE clause into a view or sub-query.

PUSH_SUBQ

Causes sub-queries to be processed earlier in the execution plan. Normally, sub-queries are processed last, unless the SQL statement is transformed into join.

REWRITE (view_name [view_name…])

Restricts query rewrite to only those materialized views specified in the hint.

ROWID (table_name)

Performs a ROWID access.

RULE

Uses rule-based optimization.

STAR

Considers the STAR join methodology in preference to other methods.

STAR_TRANSFORMATION

Requests that the star transformation optimization be performed. This transforms a star query into an alternate form that can take advantage of bitmap indexes.

USE_CONCAT

Oracle sometimes expands statements with OR conditions into multiple SQL statements combined by union all. This hint instructs the optimizer to do this, even if it calculates that such a transformation would not be beneficial.

USE_HASH (table_name)

When joining to this table, use the hash join method.

USE_MERGE (table_name)

When joining to this table, use the sort-merge join method.

USE_NL (table_name)

When joining to this table, use the nested-loops join method.

 

Related Topics

Documents connexes

The document was helpful.

Sélectionner une évaluation

I easily found the information I needed.

Sélectionner une évaluation