IFMX_OPT_FACT_TABS (XPS)

The IFMX_OPT_FACT_TABS environment variable specifies a list of fact tables that should be used in push-down hash joins whenever possible.

                               .-,-------------------------------------.
                               V                                       |
>>-setenv--IFMX_OPT_FACT_TABS----fact_table--+-----------------------+-+-><
                                             '-+-----------+--owner.-'
                                               '-database:-'
 

database

is name of the database.

fact_table

is name of the fact table.

owner

is name of the table owner.

If you do not specify a database name or owner, the fact table can be in any database or belong to any owner.

The environment variable lists fact tables for which you want to encourage the optimizer to choose push-down hash-join plans. If you do not specify the database name or owner, the table can be in any database or belong to any owner.

When this environment variable is set, push-down hash-join restrictions for the specified fact tables are relaxed to allow the optimizer to choose a push-down plan even when the fact table is not larger than the dimension table or when the dimension-table join columns are not unique.

You can use IFMX_OPT_FACT_TABS alone to increase the possibility of push-down hash joins. You can also use it in conjunction with the IFMX_OPT_NON_DIM_TABS environment variable to fine tune the use of push-down hash joins.