ENVIRONMENT Options

Use the ENVIRONMENT Options clause of the SET OPTIMIZATION statement to define attributes of the optimization environment in the current session. These attributes persist until the session ends, or until another SET OPTIMIZATION ENVIRONMENT statement resets an optimization directive.

For some data warehousing applications, session environment settings that you specify in this clause can improve the performance of queries that join fact tables with dimension tables, in databases where the primary key of each dimension table corresponds to a foreign key of the fact table.

The DB-Access utility of Informix® supports the ENVIRONMENT Options clause of the SET OPTIMIZATION statement.

Syntax

ENVIRONMENT { STAR_JOIN' { ENABLED | DISABLED | FORCED } ' | { FACT | AVOID_FACT | NON_DIM } { ' table ' | { DEFAULT | '' } } }


Element	Description	Restrictions	Syntax
table	Table, view, or synonym	Must exist in the database	Identifier

Usage

This syntax diagram is simplified, and does not show the double ( " ) quotation mark option for delimiters enclosing the list of one or more table objects, or enclosing the DISABLED, ENABLED, or FORCED keyword that you specify as the optimization environment setting.

Important: Do not use quotation marks to delimit the STAR_JOIN, FACT, AVOID_FACT, NON_DIM, or DEFAULT keywords. In the ENVIRONMENT Options clause, the DEFAULT keyword and the empty string ( '' or "") are equivalent.

The following table lists each ENVIRONMENT option, and describes their effect on whether the query optimizer considers star-join execution plans.


Keywords	Effect	Optimizer Action
`STAR_JOIN`	The `'ENABLED'` setting turns on (and `'DISABLED'` turns off) star-join support for the current session. The `'FORCED'` setting favors a star-join execution path, when possible, for all queries	For `'ENABLED'`, the optimizer considers the possibility of a star-join execution plan. For `'FORCED'`, a star-join plan will be chosen, if available. For `'DISABLED'`, star-join is not considered.
`FACT`	Identifies tables that correspond to fact tables in a star schema. If an `AVOID_FACT` table is also specified in the same session as `FACT`, then `FACT` takes precedence. `DEFAULT` or an empty string turns off the `FACT` directive for the session.	Only tables in the `FACT` list are considered as fact tables in star-join optimization. Multiple tables can be listed as `FACT`.
`AVOID_FACT`	Do not use the table (or any table in the list of tables) as a fact table in star-join optimization. `DEFAULT` or an empty string turns off the `AVOID_FACT` directive for the session.	Tables in the `AVOID_FACT` list are not considered as fact tables in star-join optimization. Multiple tables can be listed as `AVOID_FACT`.
`NON_DIM`	Identifies tables that do not correspond to dimension tables in a star schema. `DEFAULT` or an empty string turns off the `NON_DIM` directive for the session.	Tables in the `NON_DIM` list are not considered as dimension tables in star-join optimization. Multiple tables can be listed as `NON_DIM`.

Unless you explicitly set the FACT, AVOID_FACT, or NON_DIM optimizer directive to DEFAULT, a valid SET OPTIMIZATION ENVIRONMENT statement must include exactly one quoted string that defines an optimization environment setting. When the setting for the directive that follows the ENVIRONMENT keyword is not DEFAULT, a pair of single ( ' ) or double ( " ) quotation mark symbols must delimit the last specification:

Either the 'ENABLED', 'DISABLED', or 'FORCED' keyword that follows the STAR_JOIN directive,
or the comma-separated list of one or more table identifiers that specifies the setting of an optimizer environment attribute.
If the setting is the empty string or the DEFAULT keyword, the FACT, AVOID_FACT, or NON_DIM optimizer directive that follows the ENVIRONMENT keyword does not affect the query optimizer during subsequent queries in the same session. You can use this option to disable a previously specified FACT, AVOID_FACT, or NON_DIM optimizer directive.

The following restrictions apply to blank spaces within the SET OPTIMIZATION ENVIRONMENT statement:

If a comma-separated list of more than one table identifier follows the FACT, AVOID_FACT, or NON_DIM keywords, do not include blank spaces between any of the items in the list.
Similarly, do not include blank characters between the two single ( '' ) or two double ( "" ) delimiters of the empty string that is a synonym for the DEFAULT keyword.

For example, the following statements override any previous directives to prevent the query optimizer from considering any specific tables as fact tables or as dimension tables:

SET OPTIMIZATION ENVIRONMENT AVOID_FACT ""; 
SET OPTIMIZATION ENVIRONMENT NON_DIM '';

By associating the AVOID_FACT, or NON_DIM keywords with an empty set of tables, the statements in the examples above allow any table to be considered as a fact table or as a dimension table in a star-join execution path.

Configuring optimization at connection time

The DBA can use an sysdbopen( ) routine to define an optimization environment that takes effect when the user connects to the database. For example, to specify an optimizer environment that always favors a star-join execution plan, the sysdbopen( ) routine should include these SQL statements:

SET OPTIMIZATION ENVIRONMENT STAR_JOIN 'FORCED'; 
SET OPTIMIZATION ENVIRONMENT FACT 'table1,table2, ... tableN';

In the SET OPTIMIZATION ENVIRONMENT FACT statement, the tables listed after the FACT keyword should all be fact tables.

Additional methods for enabling join directives

The SET OPTIMIZATION ENVIRONMENT statement is one of several ways to specify FACT optimizer directives:

Directives within the SELECT statement, embedded comment-like. The scope is the SQL statement.
The SET OPTIMIZATION ENVIRONMENT FACT statement. The scope is the session.
The SAVE EXTERNAL DIRECTIVES statement. The scope is the database. This can define FACT directives and save them in the sysdirectives system catalog table.

Three mechanisms exist for enabling the query optimizer to consider external directives in the sysdirectives system catalog table, including any FACT directives. This is the (approximately) ascending order of precedence:

The EXT_DIRECTIVES configuration parameter. If this is set to 2, external directives are enabled for the database server, unless the IFX_EXTDIRECTIVES environment variable on the client system is set to 0.
The IFX_EXTDIRECTIVES environment variable. If this is set to 1 on the client system, external optimizer directives are enabled for the database server, unless the EXT_DIRECTIVES configuration parameter is set to 0.
The SET ENVIRONMENT statement. To enable the query optimizer to consider the external optimizer directives in sysdirectives when it chooses an execution path for queries during the current session, the DBA can issue any of these statements:
```
SET ENVIRONMENT EXTDIRECTIVES on;
SET ENVIRONMENT EXTDIRECTIVES ON;
SET ENVIRONMENT EXTDIRECTIVES '1';
SET ENVIRONMENT EXTDIRECTIVES "1";
```
To prevent the query optimizer from considering external optimizer directives in sysdirectives, the DBA can issue any of these statements:
```
SET ENVIRONMENT EXTDIRECTIVES off;
SET ENVIRONMENT EXTDIRECTIVES OFF;
SET ENVIRONMENT EXTDIRECTIVES '0';
SET ENVIRONMENT EXTDIRECTIVES "0";
```
These statements override (for the current session only) any conflicting settings of the EXT_DIRECTIVES configuration parameter or of the IFX_EXTDIRECTIVES environment variable.

For information about the SET ENVIRONMENT EXTDIRECTIVES statement, see EXTDIRECTIVES session environment option.

For a table showing the effects of various combinations of EXT_DIRECTIVES settings and IFX_EXTDIRECTIVES settings on enabling or disabling external optimizer directives, see Enabling or disabling external directives for a session.

For information about query optimizer directives that can favor or avoid star-join execution plans, see Star-Join Directives.

For information about how to use the built-in sysdbopen( ) routine to define the session environment at the time of connection for a specified user, for the PUBLIC group, or for a role, see Session Configuration Procedures.