x
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