Prevent or Encourage Push-Down Hash Joins

Home | Previous Page | Next Page Tuning Specific Queries and Transactions > Tuning DSS Queries >

Prevent or Encourage Push-Down Hash Joins

For some star and snowflake schemas, you might want to manage push-down hash joins explicitly.

Tip:

To turn off push-down hash joins for a session, execute SET ENVIRONMENT PUSHDOWN OFF. For more information, see the IBM Informix: Extended Parallel Server Administrator's Reference.

Two environment variables allow users to specify when the optimizer can and cannot choose a push-down hash join. For information about push-down hash joins, see Push-Down Hash Join.

Encourage push-down hash joins on specific fact tables
To specify a list of fact tables that the optimizer should choose for push-down hash joins whenever possible, enter these table names as the argument to the IFMX_OPT_FACT_TABS environment variable. For example, you might encourage the optimizer to use push-down hash joins for the orders fact table in the sales database as follows:
```
IFMX_OPT_FACT_TABS sales:orders
```
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.
Prevent push-down hash joins on specific dimension tables
To specify a list of dimension tables that can never be used in push-down hash join plans, enter these table names as the argument to the IFMX_OPT_NON_DIM_TABS environment variable.

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.

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 either or both of the environment variables. That is, to increase the possibility of push-down hash joins, you might use only the IFMX_OPT_FACT_TABS environment variable. To decrease the possibility of push-down hash joins, you might use only the IFMX_OPT_NON_DIM_TABS environment variable. To fine-tune use of push-down hash joins, you might use both environment variables.

The format for these environment variables is as follows:

VARIABLE [<database_name:][<owner>.]<table_name>, ...

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

Important:

IFMX_OPT_FACT_TABS and IFMX_OPT_NON_DIM_TABS are global environment variables. To set or reset these variables, you must restart the database server before changes take effect.