Informix Experts

In IDS 11.10, all optimizer directives including external directives can be used in queries using ANSI joins.Using
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]