IFX_BATCHEDREAD_TABLE session environment option

Use the IFX_BATCHEDREAD_TABLE environment option of the SET ENVIRONMENT statement to enable or disable light scans on compressed tables, tables with rows that are larger than a page, and tables with VARCHAR, LVARCHAR, and NVARCHAR data during the current session.

A light scan bypasses the buffer pool by utilizing session memory to read directly from disk. Query execution plans that uses light scans on large tables can improve performance, compared to plans using full-table scans.

IFX_BATCHEDREAD_TABLE environment option

SET ENVIRONMENT IFX_BATCHEDREAD_TABLE { '1' | '0' }

Usage

When the BATCHEDREAD_TABLE configuration parameter is not enabled, enabling the IFX_BATCHEDREAD_TABLE session environment variable 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"
Enable light scans during the session for query execution on compressed tables, on tables with rows larger than a page, and on tables with VARCHAR, LVARCHAR, and NVARCHAR columns
'0' or "0"
Prevent the query optimizer from using these light scans for the session.
For example, the following statement enables queries to perform light scans on compressed tables, on tables with rows larger than a page, and on tables with character data types of variable length: 
SET ENVIRONMENT IFX_BATCHEDREAD_TABLE '1';
The following statement restores the default restriction on light scans of tables with those attributes:
SET ENVIRONMENT IFX_BATCHEDREAD_TABLE "0";

Light scans

Light scans can provide performance advantages over other query execution plans that use the buffer pool for sequential scans or for skip scans of large tables. The following requirements, however, restrict the contexts in which the query optimizer can consider execution paths that include a light scan:
  • The optimizer must choose a sequential scan or a skip-scan of the table.
  • The table must store at least a megabyte (MiB) of data.
  • One of the following locking conditions must be satisfied:
    • The isolation level is not Cursor Stability, and you hold at least a shared lock on the entire table.
    • The isolation level is Dirty Read, or the database transaction logging mode is WITH NO LOG.
But even if all of the optimizer plan, table size, and locking requirements listed above are satisfied, the following additional table restrictions can prevent light scans on tables with any of the following attributes. The tables cannot be
  • compressed tables,
  • tables with rows that are larger than a page,
  • tables of variable row length.
The variable row length restriction prohibits tables with VARCHAR, LVARCHAR, or NVARCHAR columns, or with ROW-type columns that include VARCHAR, LVARCHAR, or NVARCHAR fields, or with data types DISTINCT of these variable-length types.

Although these three table restrictions are in effect by default, they do not prevent light scans on qualifying large tables in databases where the BATCHEDREAD_TABLE configuration parameter is enabled, or in sessions where the BATCHEDREAD_TABLE session environment option is enabled.

In both examples, the double quotation-mark character ( " ) could be substituted as the delimiters, but as in any SET ENVIRONMENT operation, an undelimited digit for the setting returns an error:
SET ENVIRONMENT IFX_BATCHEDREAD_TABLE 0;  --Fails to disable light scans
                                          --on qualifying large tables

In sessions where the IFX_BATCHEDREAD_TABLE configuration parameter and the IFX_BATCHEDREAD_TABLE 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_TABLE session environment variable is reset.

Important:
The lists of table attributes identified above as preventing light scans are not exhaustive. Tables with any of the following attributes cannot be processed using light scans in query execution plans, regardless of the current setting of the IFX_BATCHEDREAD_TABLE configuration parameter or the IFX_BATCHEDREAD_TABLE session environment variable:
  • tables with data stored in blobspaces,
  • tables with data stored in smart blobspaces,
  • tables of variable row length.