All About Oracle Hints

If you are an Oracle DB customer, you should learn about Oracle hints. Oracle hints influence the Oracle optimizer to your advantage. Here is an example of a hint.

 

Example:

select /*+ ALL_ROWS */ emplid, name, salary
from employees
where emplid = 8675309;

Below is a complete list of Oracle hints and a description of what each does.

ALL_ROWS
Directs the optimizer to choose a cost-based execution plan that is designed to provide the best throughput (useful for batch processing)

FIRST_ROWS
Directs the optimizer to choose the best execution plan for retrieving the first row in a query (useful for online processing)

ROWID(table)
Forces a table scan by RowID for the specified table

HASH(table)
Chooses a hash scan to access the specified table

INDEX(table Index)
forces usage of a specific index

ORDERED
causes the optimizer to join tables in the order specified in the FROM clause

Full Table Scans
If your explain plan indicates that full table scans are occurring, here are some basic coding hints for avoiding these full table scans.

转载于:https://www.cnblogs.com/GoDevil/archive/2009/01/09/1372691.html