When TABLE-SCAN is not recommended to be used

Example 1: The following example demonstrates when TABLE-SCAN is not recommended instead of WHOLE-INDEX

DEFINE TEMP-TABLE mytable NO-UNDO
FIELD name AS CHARACTER
FIELD id AS INTEGER
INDEX nameix name.
FOR EACH mytable TABLE-SCAN BY WHERE mytable.name BEGINS "D":
DISPLAY mytable.
END.

When TABLE-SCAN is used on non WHOLE-INDEX, the AVM returns a compiler warning stating: "Using a TABLE-SCAN option on non WHOLE-INDEX query". During the runtime, the AVM uses a TABLE-SCAN instead of the nameix index, which would be more efficient.

Example 2: TABLE-SCAN keyword with BY expression, where expression is defined by an index

DEFINE TEMP-TABLE mytable NO-UNDO
FIELD name AS CHARACTER
FIELD id1 AS INTEGER
FIELD id2 AS INTEGER
INDEX idix id.
FOR EACH mytable TABLE-SCAN BY id:
DISPLAY mytable.
END.

In this case, the AVM returns a compiler warning stating: "Using a TABLE-SCAN option with BY expression which would have been optimized using the index that matches BY". When you specify the TABLE-SCAN option, the AVM scans the table to sort the records by the id column, and then displays each record. If you do not specify the TABLE-SCAN option, the AVM scans the entire table once, and sorting is not required as the id column has already been indexed.

Keep in mind the following when using TABLE-SCAN:

TABLE-SCAN provides the most efficient access method when retrieving rows of a temp-table or a database table in a Type II Storage area. If the table is of a Type I storage, the AVM ignores the TABLE-SCAN option and uses the USE-INDEX keyword to perform the scan.

To increase the performance of the EMPTY-TEMP-TABLE method and statement, the TABLE-SCAN option is turned on by default.

Do not use TABLE-SCAN on a non WHOLE-INDEX query. For example, if the query has "WHEREcustnum > 100" and custnum has an index, the AVM does not require to scan the whole table to satisfy the query. See also When TABLE-SCAN is not recommended to be used.

Do not use the TABLE-SCAN option in FOR EACH statements, if the BY expression option is specified and the expression is defined by the index. If you specify the TABLE-SCAN option, the AVM sorts all the records by expression. The AVM does not need to perform the sorting, as the expression column already has a sorted index. See also When TABLE-SCAN is not recommended to be used.

Avoid using the TABLE-SCAN option on any child table in a given join query, because the WHOLE-INDEX is not used to access the child table’s records.