What is the benefit of using the TABLE-SCAN option?

In what version was the record phrase TABLE-SCAN option introduced?
What is the syntax of using the record phrase TABLE-SCAN option?

The TABLE-SCAN Option:

1. The record phrase TABLE-SCAN option was introduced in OpenEdge 11.0 which can be used for better performance as no index is used
2. The TABLE-SCAN option is used as a USE-INDEX alternative in FOR EACH statements.
3. A TABLE-SCAN query provides the most efficient access method when retrieving all records of a TEMP-TABLE or a database Table in a Type II Storage area. 
4. TABLE-SCAN returns the result set without using an index. When the TABLE-SCAN keyword is used, the AVM only accesses the record block instead of both record and index blocks.
5. For a table in a Type I storage, the AVM will still use the default index to perform the scan instead of TABLE-SCAN.To verify if we use type II storage area:

   FOR EACH _area NO-LOCK:
       DISPLAY _area._area-name _area._area-clustersize.
   END.

6. The syntax of the TABLE-SCAN option is demonstrated in the following code snippet:

FOR EACH Customer TABLE-SCAN:  
      /* Do some processing */
END.

If the physical storage order of the records created in a table closely match the ordering given by the index that would otherwise be used, then the TABLE-SCAN option may not result in better performance:
 

DEF BUFFER b_orderline FOR orderline.
DEF TEMP-TABLE ttorderline LIKE orderline.
DEF VAR cLogfile AS CHAR NO-UNDO INIT 'c:\temp\out.log'.

RUN main.

PROCEDURE main:
    OUTPUT TO VALUE(cLogfile).

    RUN testTableScan.
    RUN testNoLock.
    RUN testTableScan.
    RUN testNoLock.
    RUN testTableScan.
    RUN testNoLock.

    OUTPUT CLOSE.
END PROCEDURE.

PROCEDURE testNoLock:
    DEF VAR iCnt AS INT NO-UNDO INIT 0.
    EMPTY TEMP-TABLE ttorderline.

    ETIME(TRUE).
    FOR EACH b_orderline NO-LOCK:
        CREATE ttorderline.
        BUFFER-COPY b_orderline TO ttorderline.
        iCnt = iCnt + 1.
    END.
    RELEASE orderline.
    MESSAGE 'NO-LOCK: ' + STRING(iCnt) + ' results in ' + STRING(ETIME) + ' ms.'.
END PROCEDURE.

PROCEDURE testTableScan:
    DEF VAR iCnt AS INT NO-UNDO INIT 0.
    EMPTY TEMP-TABLE ttorderline.

    ETIME(TRUE).
    FOR EACH b_orderline TABLE-SCAN NO-LOCK:
        CREATE ttorderline.
        BUFFER-COPY b_orderline TO ttorderline.
        iCnt = iCnt + 1.
    END.
    RELEASE orderline.
    MESSAGE 'TABLE-SCAN: ' + STRING(iCnt) + ' results in ' + STRING(ETIME) + ' ms.'.
END PROCEDURE.

For known issues with TABLE-SCAN refer to the Articles reference in the Notes below.

It is not necessarily true that performance will be fast with table-scan. It all depends on the extra cost of scanning the records without an index. The smaller the number of records that it would need to scan, the less likely there will be a benefit. There is information that the DB engine needs to acquire to process a table-scan query, so the cost of that needs to be less than the cost of going through the index to fetch the records. In the case where the table has 2 records, it is not, so TABLE-SCAN is not a good option for that use case. Ideally, TABLE-SCAN on tables with lots of records should be used to see if it can give better performance than using the index. 

Progress Articles:

 TABLE-SCAN fails with inactive index error 995 [000081631]  
 COMPILE XREF of code using TABLE-SCAN crashes session  
If the TABLE-SCAN option for a query is used, the next query which uses a normal index will get also the table-scan option. [000052465]