attr := INDEX([ baserecset, ] keys, indexfile [,SORTED] [,OPT] [,COMPRESSED( LZW | ROW | FIRST) ] [,DISTRIBUTED] [,FILEPOSITION( [ flag] ) ] [, MAXLENGTH[(**value**)] ] );
attr := INDEX([ baserecset, ] keys, payload, indexfile [,SORTED] [,OPT] [,COMPRESSED( LZW | ROW | FIRST) ] [,DISTRIBUTED] [,FILEPOSITION( [ flag] ) ] [, MAXLENGTH[(value)] ]);
attr := INDEX(index,newindexfile [, MAXLENGTH[(value)] ]);
INDEX declares a previously created index for use. INDEX is related to BUILD (or BUILDINDEX) in the same manner that DATASET is to OUTPUT--BUILD creates an index file that INDEX then defines for use in ECL code. Index files are compressed. A single index record must be defined as less than 32K and result in a less than 8K page after compression.
The Binary-tree metakey portion of the INDEX is a separate 32K file part on the first node of the Thor cluster on which it was built, but deployed to every node of a Roxie cluster. There are as many leaf-node file parts as there are nodes to the Thor cluster on which it was built. The specific distribution of the leaf-node records across execution nodes is undefined in general, as it depends on the size of the cluster on which it was built and the size of the cluster on which it is used.
These data types are supported in the keyed portion of an INDEX:
BOOLEAN
INTEGER
UNSIGNED
STRING
DATA
QSTRING
All STRINGs must be fixed length.
This form defines an index file to allow keyed access to the baserecset. The index is used primarily by the FETCH and JOIN (with the KEYED option) operations.
Example:
PtblRec := RECORD STRING2 State := Person.per_st; STRING20 City := Person.per_full_city; STRING25 Lname := Person.per_last_name; STRING15 Fname := Person.per_first_name; END; PtblOut := OUTPUT(TABLE(Person,PtblRec),,'RTTEMP::TestFetch'); Ptbl := DATASET('RTTEMP::TestFetch', {PtblRec,UNSIGNED8 RecPtr {VIRTUAL(fileposition)}}, FLAT); AlphaInStateCity := INDEX(Ptbl, {state,city,lname,fname,RecPtr}, 'RTTEMPkey::TestFetch'); Bld := BUILDINDEX(AlphaInStateCity);