IFX_BATCHEDREAD_INDEX session environment option

You can use the IFX_BATCHEDREAD_INDEX session environment option of the SET ENVIRONMENT statement of SQL to enable the query execution plans that execute light scans on the indexes of large tables, bypassing the buffer pool by using session memory to read directly from disk.

IFX_BATCHEDREAD_INDEX session environment option

1  SET ENVIRONMENT IFX_BATCHEDREAD_INDEX
2.1 '1'
2.1 '0'

Usage

When the IFX_BATCHEDREAD_INDEX configuration parameter not set, or is set to 0, enabling the IFX_BATCHEDREAD_INDEX session environment option can improve the performance of some DML operations on indexed tables.

To set this session environment variable, specify one of the following digits as a quoted string:

'1' or "1"
Enables the query optimizer to execute light scans for indexes during the session.
'0' or "0"
Prevents the query optimizer from executing light scans for indexes.
For example, to enable the optimizer use light scans for reading index keys, specify: 
SET ENVIRONMENT IFX_BATCHEDREAD_INDEX '1';
The following statement disables the same query optimizer feature:
SET ENVIRONMENT IFX_BATCHEDREAD_INDEX '0';

In sessions where the IFX_BATCHEDREAD_INDEX configuration parameter and the IFX_BATCHEDREAD_INDEX session environment variable have different settings, the setting of the session environment variable takes precedence over the configuration parameter setting for the duration of the session, or until the IFX_BATCHEDREAD_INDEX session environment variable is reset.

The default value of the IFX_BATCHEDREAD_INDEX configuration parameter in the onconfig.std file is 1. That value can be reset to zero, however, disabling light scans of indexes, by either of the following onmode commands:
EXECUTE FUNCTION task("onmode","wf","IFX_BATCHEDREAD_INDEX=0");
EXECUTE FUNCTION task("onmode","wm","IFX_BATCHEDREAD_INDEX=0");
When IFX_BATCHEDREAD_INDEX has been disabled by those function calls, running the SET ENVIRONMENT statement
SET ENVIRONMENT IFX_BATCHEDREAD_INDEX "1";
restores support for light scans on indexes during the current session, if you expect that light scans on indexes will be more efficient for some DML operations in your application.

For more information about the advantages of light scans in queries of large tables, and restrictions on light scans, see IFX_BATCHEDREAD_TABLE session environment option