optimizer directives might enable you to force a favorable query execution path thus improving performance.
Here is a description of the different optimizer directives and how they work with ANSI join queries.
Optimization Goal Directives | FIRST_ROWS, ALL_ROWS | Directives enabled for ANSI join queries |
Access Method Directives | FULL, INDEX, AVOID_INDEX, AVOID_FULL,INDEX_SJ, AVOID_INDEX_SJ | Directives enabled for ANSI join queries |
Explain-Mode Directives | EXPLAIN, AVOID_EXECUTE | Directives enabled for ANSI join queries |
External Directives | Directives enabled for ANSI join queries | |
Join Method Directives | USE_HASH, USE_NL, AVOID_HASH, AVOID_NL, /BUILD, /PROBE | Directives enabled for ANSI Inner Joins Directives not enabled for ANSI Inner Joins in views Directives enabled for ANSI Outer Joins that can be transformed into ANSI Inner joins Directives disabled for all other ANSI Outer Joins |
Join Order Directives | Ordered | Directives enabled for ANSI Inner Joins Directives enabled for ANSI Left Outer joins if it doesn't conflict with the ordering requirement for Outer Joins Directives disabled for all other ANSI Outer Joins |
Suma Vinod[Read More]