Improving the Oracle Optimizer - Oracle Database 11gR2 Performance Tuning - page 291

F INDEX (table_name index_name) : Specifies to access the table_name using an
index (using index_name if specified);
F NO_INDEX (table_name index_name) : Specifies not to use an index (a specific
index, if index_name specified, otherwise any index) when accessing table_name
F LEADING (table_name1 table_name2 …) : Asks the optimizer to join
table_name1 and table_name2 in the given order
F ORDERED : Asks the optimizer to join the tables in the order in which they appear in
the FROM clause
F USE_NL (table_name1 table_name2 …) : Instructs the optimizer to use nested
loops join when joining table_name1 (and table_name2 ) to another row source
F USE_MERGE (table_name1 table_name2 …) : Instructs the optimizer to use a sort-
merge join when joining table_name1 (and table_name2 ) to another row source
F USE_HASH (table_name1 table_name2 …) : Instructs the optimizer to use a hash
join when joining table_name1 (and table_name2 ) to another row source
F PARALLEL (DEFAULT | AUTO | MANUAL | n) : Instructs the optimizer to use the
specified number of concurrent servers for a parallel operation
F STAR_TRANSFORMATION : The optimizer will chose the best execution plan obtained
through a star transformation
F REWRITE (materialized_view) : Instructs the optimizer to rewrite the query using
materialized views (if materialized_view is specified); the optimizer won't take
care of the costs involved in using the materialized view
F APPEND : Instructs the optimizer to use direct-path INSERT (data is appended to the
end of the table, regardless of whether there is free space in blocks below the high
watermark)
F CACHE (table_name) : Instructs the optimizer to place the blocks retrieved from
table_name at the top of the least recently-used list in the buffer cache when a full
table scan is performed; this hint is useful with a small lookup table
When specifying a hint related to a table (for example, the FULL hint used in step 4), if we
have used an alias in our query, we must use the alias in the hint too. This rule doesn't
apply to the schema. Even if we have specified the schema in our query, for example,
SH.CUSTOMERS , we must use only the table name (or the alias) in the hint. In the preceding
example, we used only CUSTOMERS .
The hint is, in effect, a suggestion, so if the optimizer is able to follow the suggestion, it will
generate a plan that follows the suggestion, even if performance is poor!
There are certain hints—such as, LEADING and ORDERER —that
the optimizer will always follow. The optimizer will ignore a hint
when there is a typo or when an incorrect hint is supplied.