Oracle Hints

Some of Frequently Used Oracle Hints:

Hints

Description

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)

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.

FULL

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

INDEX

The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B*-tree, and bitmap indexes.

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

USE_MERGE

The USE_MERGE hint causes Oracle to join each specified table with another row source with 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

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.

NOPARALLEL

You can use the NOPARALLEL hint to override a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.

Thanks

D.Sasi Kumar

 

0 comments: