Automatic Indexed Retrieval

How to:

Reference:

Automatic indexed retrieval (AUTOINDEX) optimizes the speed of data retrieval in FOCUS data sources. To take advantage of automatic indexed retrieval, a TABLE request must contain an equality or range test on an indexed field in the highest segment referenced in the request.

This method is not supported if a:

For related information on AUTOINDEX, see the Developing Reporting Applications manual.

Syntax: How to Use Indexed Retrieval 

SET AUTOINDEX = {ON|OFF}

where:

ON

Uses indexed data retrieval for optimized speed when possible. The request must contain an equality or range test on an indexed field in the highest segment referenced in the request. ON is the default value.

OFF

Uses sequential data retrieval unless a request specifies an indexed view (TABLE FILE filename.indexed_fieldname) and contains an equality test on indexed_fieldname. In that case, indexed data retrieval is automatically performed.

Reference: Usage Notes for Indexed Retrieval

  • AUTOINDEX is never invoked when the TABLE request contains an alternate file view (that is, TABLE FILE filename.fieldname).
  • Even if AUTOINDEX is ON, indexed retrieval is not performed when the TABLE request contains BY HIGHEST or BY LOWEST phrases.
  • When a request specifies an indexed view (as in TABLE FILE filename.indexed_fieldname), indexed retrieval is implemented under the following circumstances:
    • AUTOINDEX is OFF and the request contains an equality test on the indexed field.
    • AUTOINDEX is ON and the request contains either an equality or a range (FROM ... TO) test against the indexed field.

Example: Using Indexed Retrieval

The following Master File is referenced in the examples that follow: 

FILENAME=SALES,SUFFIX=FOC,
  SEGNAME=STOR_SEG,SEGTYPE=S1,
    FIELDNAME=AREA,ALIAS=LOC,FORMAT=A1,$
  SEGNAME=DATE_SEG,PARENT=STOR_SEG,SEGTYPE=SH1,
    FIELDNAME=DATE,ALIAS=DTE,FORMAT=A4MD, $
  SEGNAME=DEPT,PARENT=DATE_SEG,SEGTYPE=S1,
    FIELDNAME=DEPARTMENT,ALIAS=DEPT,FORMAT=A5,FIELDTYPE=I,$
    FIELDNAME=DEPT_CODE,ALIAS=DCODE,FORMAT=A3,FIELDTYPE=I,$
    FIELDNAME=PROD_TYPE,ALIAS=PTYPE,FORMAT=A10,FIELDTYPE=I,$
  SEGNAME=INVENTORY,PARENT=DEPT,SEGTYPE=S1,$
    FIELDNAME=PROD_CODE,ALIAS=PCODE,FORMAT=A3,FIELDTYPE=I,$
    FIELDNAME=UNIT_SOLD,ALIAS=SOLD,FORMAT=I5,$
    FIELDNAME=RETAIL_PRICE,ALIAS=RP,FORMAT=D5.2M,$
    FIELDNAME=DELIVER_AMT,ALIAS=SHIP,FORMAT=I5,$

The following procedure contains an equality test on DEPT_CODE and PROD_CODE. DEPT_CODE is used for indexed retrieval since it is in the higher of the referenced segments. 

SET AUTOINDEX=ON
TABLE FILE SALES
SUM UNIT_SOLD RETAIL_PRICE
IF DEPT_CODE EQ 'H01'
IF PROD_CODE EQ 'B10'
END

If your TABLE request contains an equality or range test against more than one indexed field in the same segment, AUTOINDEX uses the first index referenced in that segment for retrieval. The following stored procedure contains an equality test against two indexed fields. Since DEPT_CODE appears before PROD_TYPE in the Master File, AUTOINDEX uses DEPT_CODE for retrieval. 

SET AUTOINDEX=ON
TABLE FILE SALES
SUM UNIT_SOLD AND RETAIL_PRICE
IF PROD_TYPE EQ 'STEREO'
IF DEPT_CODE EQ 'H01'
END

Indexed retrieval is not invoked if the equality or range test is run against an indexed field that does not reside in the highest referenced segment. In the following example, indexed retrieval is not performed, because the request contains a reference to AREA, a field in the STOR_SEG segment: 

SET AUTOINDEX=ON
TABLE FILE SALES
SUM UNIT_SOLD AND RETAIL_PRICE
BY AREA
IF PROD_CODE EQ 'B10'
IF PROD_TYPE EQ 'STEREO'
END