IFMX_OPT_NON_DIM_TABS (XPS)

The IFMX_OPT_NON_DIM_TABS environment variable specifies a list of dimension tables that cannot be used in push-down hash-join query plans. If the optimizer detects a fact-dimension table query that joins one of these dimension tables, it does not choose a push-down hash-join plan.

                                  .-,------------------------------------.
                                  V                                      |
>>-setenv--IFMX_OPT_NON_DIM_TABS----dim_table--+-----------------------+-+-><
                                               '-+-----------+--owner.-'
                                                 '-database:-'
 

database

is name of a database.

dim_table

is name of a dimension table.

owner

is name of table owner.

If the database name or owner is not specified, the table can be in any database or can belong to any owner.

When this environment variable is set, if a query joins one of the dimension tables in this list with any fact table, the optimizer never selects a push-down hash join for the query, even if the fact table is included in the IFMX_OPT_FACT_TABS list.

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