Oracle10g SQL optimizer hints

spread_min_analysis
With this hint, you can omit some of the compile time optimizations of the rules on spreadsheets, such as detailed dependency graph analysis.

For example:
SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

spread_no_analysis
With this hint, it's possible to have no spreadsheet analysis.
For example:
SELECT /*+ SPREAD_NO_ANALYSIS */ ...

use_nl_with_index
This hint causes the CBO to join the specified table to another row source with a nested loops join.

For example:
SELECT /*+ USE_NL_WITH_INDEX (polrec polrind) */ ...

CARDINALITY
This hint specifies the estimated cardinality returned by a query or portions of the query.
For example:
SELECT /*+ CARDINALITY ( [tablespec] card ) */

SELECTIVITY
This hint specifies the estimated selectivity of a query or portions of the query.
For example:
SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

However, note that if both CARDINALITY and SELECTIVITY hints are specified on the same set of tables, both hints will be ignored.

no_use_nl
The no_use_nl hint causes the CBO to exclude nested loops to join each specified table to another row source, using the specified table as the inner table.
For example:
SELECT /*+ NO_USE_NL ( employees ) */ ...

no_use_merge
This hint causes the CBO to exclude sort-merge to join each specified table to another row source, using the specified table as the inner table.

For example:
SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

no_use_hash
This hint causes the CBO to exclude hash joins to join each specified table to another row source, using the specified table as the inner table.

For example:
SELECT /*+ NO_USE_HASH ( employees dept ) */ ...

no_index_ffs
This hint causes the CBO to exclude a fast full-index scan of the specified indexes on the specified table.
Syntax: /*+ NO_INDEX_FFS ( tablespec indexspec ) */

no_index_ss
This hint causes the CBO to exclude a skip scan of the specified indexes on the specified table.
Syntax: /*+ NO_INDEX_SS (tablespec indexspec ) */

no_star_transformation
This hint causes the CBO to omit star query transformation.
Syntax: /*+ NO_STAR_TRANSFORMATION */

index_ss
This hint explicitly chooses an index skip scan for the specified table.

Syntax: /*+ INDEX_SS (tablespec indexspec) */

index_ss_asc
This hint explicitly chooses an index skip scan for the specified table.
Syntax: /*+ INDEX_SS_ASC (tablespec indexspec) */

index_ss_desc
This hint explicitly chooses an index skip scan for the specified table.

Syntax: /*+ INDEX_SS_DESC (tablespec indexspec) */

cpu_costing
This hint turns CPU costing on for the SQL statement.

 /*+ CPU_COSTING (tablespec indexspec) */

no_cpu_costing
This hint turns off CPU costing for the SQL statement.

Syntax: /*+ NO_CPU_COSTING */

REF:http://translate.google.com.tw/translate?hl=zh-TW&sl=en&u=http://articles.techrepublic.com.com/5100-10878_11-5219086.html&ei=fL8UTJfhBsWXccb8jLQM&sa=X&oi=translate&ct=result&resnum=1&ved=0CB0Q7gEwAA&prev=/search%3Fq%3Doracle10g%252Brulebase%26num%3D100%26hl%3Dzh-TW%26newwindow%3D1%26safe%3Dactive