Skip to main content

INDEX

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)] ]);

attrThe name of the INDEX for later use in other attributes.
baserecsetOptional. 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.
keysThe RECORD structure of the fields in the indexfile that contains key and file position information for referencing into the baserecset. Field names and types must match the baserecset fields (REAL and DECIMAL type fields are not supported). This may also contain additional fields not present in the baserecset (computed fields). If omitted, all fields in the baserecset are used.
payloadThe RECORD structure of the indexfile that contains additional fields not used as keys. If the name of the baserecset is in the structure, it specifies "all other fields not already named in the keys parameter." This may contain fields not present in the baserecordset (computed fields). 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.
indexfileA string constant containing the logical filename of the index. See the Scope & Logical Filenames section for more on logical filenames.
SORTEDOptional. 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 type of compression used. If omitted, the default is LZW, a variant of the Lempel-Ziv-Welch algorithm. Specifying ROW compresses index entries based on differences between contiguous rows (for use with fixed-length records, only), and is recommended for use in circumstances where speedier decompression time is more important than the amount of compression achieved. FIRST compresses common leading elements of the key (recommended only for timing comparison use).

DISTRIBUTEDOptional. 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.
FILEPOSITIONOptional. 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.
flagOptional. 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.

MAXLENGTHOptional. 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.
valueOptional. 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.

Keyed Access INDEX

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);

Payload INDEX

This form defines an index file containing extra payload fields in addition to the keys. The payload may contain fields with the {BLOB} modifier to allow more than 32K of data per index entry. These BLOB fields are stored within the indexfile, but not with the rest of the record; accessing the BLOB data requires an additional seek.

This form is used primarily by "half-key" JOIN operations to eliminate the need to directly access the baserecset, thus increasing performance over the "full-keyed" version of the same operation (done with the KEYED option on the JOIN). By default, payload fields are not sorted during the BUILD action to minimize space on the leaf nodes of the key. This sorting behavior can be controlled by using sortIndexPayload in a #OPTION statement.

Example:

Vehicles := DATASET('vehicles',
          {STRING2 st,STRING20 city,STRING20 lname,
          UNSIGNED8 fpos{virtual(fileposition)}},FLAT);

VehicleKey := INDEX(Vehicles,{st,city},{lname,fpos},'vkey::st.city'); 
BUILDINDEX(VehicleKey);

Duplicate INDEX

This form defines a newindexfile that is identical to the previously defined index.

Example:

NewVehicleKey := INDEX(VehicleKey,'NEW::vkey::st.city');
          //define NewVehicleKey like VehicleKey

See Also: DATASET, BUILDINDEX, JOIN, FETCH, KEYED/WILD