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(option) ] [,DISTRIBUTED] [,FILEPOSITION( [ flag] ) ] [, MAXLENGTH[(value)] ]);
attr := INDEX(index,newindexfile [, MAXLENGTH[(value)] ]);
| attr | The name of the INDEX for later use in other attributes. |
| baserecset | Optional. The set of data records for which the index file has been created. If omitted, all fields in the keysand payloadparameters must be fully qualified. |
| keys | The RECORD structure of key fields that reference into the baserecset (the "search terms" for the INDEX). Key fields may be baserecset fields or computed fields. REAL and DECIMAL types are not supported as "search term" fields. If omitted, all fields in the baserecset are used. This RECORD structure is typically defined inline within the INDEX using curly braces ({}), but may also be a separately defined RECORD structure. If the RECORD structure is separately defined it must meet the same requirements as used by the TABLE() function (the RECORD structure must define the type, name, and source of the data for each field), otherwise the BUILD action will not syntax check. |
| payload | The RECORD structure of the indexfile that contains additional fields not used as "search term" keys. This may contain fields from the baserecordset and/or computed fields. If the name of the baserecset is in this structure, it specifies "all other fields not already named in the keys parameter" are added. The payload fields do not take up space in the non-leaf nodes of the index and cannot be referenced in a KEYED() filter clause. Any field with the {BLOB} modifier (to allow more than 32K of data per index entry) is stored within the indexfile, but not with the rest of the record; accessing the BLOB data requires an additional seek. This RECORD structure is typically defined inline within the INDEX using curly braces ({}), but may also be a separately defined RECORD structure. If the RECORD structure is separately defined it must meet the same requirements as used by the TABLE() function (the RECORD structure must define the type, name, and source of the data for each field), otherwise the BUILD action will not syntax check. |
| indexfile | A string constant containing the logical filename of the index. See the Scope & Logical Filenames section for more on logical filenames. |
| SORTED | Optional. Specifies that when the index is accessed the records come out in the order of the keys. If omitted, the returned record order is undefined. |
| OPT | Optional. Specifies that using the index when the indexfile doesn't exist results in an empty recordset instead of an error condition. |
| COMPRESSED | Optional. Specifies the index should be compressed using the type of compression specified. If omitted, the default is LZW, a variant of the Lempel-Ziv-Welch algorithm. |
| option | See Indexes and Compression for options. |
| DISTRIBUTED | Optional. Specifies that the index was created with the DISTRIBUTED option on the BUILD action or the BUILD action simply referenced the INDEX declaration with the DISTRIBUTED option. The INDEX is therefore accessed locally on each node (similar to the LOCAL function, which is preferred), is not globally sorted, and there is no root index to indicate which part of the index will contain a particular entry. This may be useful in Roxie queries in conjunction with ALLNODES use. |
| FILEPOSITION | Optional. If flag is FALSE, prevents the normal behavior of implicit fileposition field being created and will not treat a trailing integer field any differently from the rest of the payload. |
| flag | Optional. TRUE or FALSE, indicating whether or not to create the implicit fileposition field. |
| index | The name of a previously defined INDEX attribute to duplicate. |
| newindexfile | A string constant containing the logical filename of the new index. See the Scope & Logical Filenames section for more on logical filenames. |
| MAXLENGTH | Optional. This option is used to create indexes that are backward compatible for platform versions prior to 3.0. Specifies the maximum length of a variable-length index record. Fixed length records always use the minimum size required. If the default maximum length causes inefficiency problems, it can be explicitly overridden. |
| value | Optional. An integer value indicating the maximum length. If omitted, the maximum size is calculated from the record structure. Variable-length records that do not specify MAXLENGTH may be slightly inefficient |
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);