INDEX

Purpose

Create an index for a table on a specified key

Syntax

INDEX ON <key expression> TO <.ndx filename> [FOR <condition>] [UNIQUE]

INDEX ON <key expression> TAG <tagname> [OF <.dbx filename>] [FOR <condition>] [UNIQUE] [DESCENDING]

See Also

CANDIDATE(), CLOSE INDEX, COPY INDEXES, COPY TAG, CREATE VIEW, DELETE TAG, DBXDESCEND(), DESCEND(), DTOS(), FILTER(), FIND, FOR(), ICACHE(), LOOKUP(), MDX(), LTOS(), REINDEX, RLOOKUP(), SEEK, SEEK(), SET FASTINDEX, SET FILTER, SET ICACHE, SET INDEX, SET ORDER, SET PCUNIQUE, SET UNIQUE, STR(), STRZERO(), TAG(), TAGCOUNT(), TAGNO(), UNIQUE(), USE

Description

Indexing provides the means of ordering the way in which records are viewed in a table without actually physically rearranging them. Indexing is far more efficient than sorting, and provides the same logical ordering of the table.

The INDEX command creates an index file based upon the evaluation of the specified <key expression>. The Recital/4GL allows you to index on any valid character, date or numeric expression. You can construct an index on mixed data types using the conversion functions STR(), DTOS() and LTOS(). If the STR() function is used on a numeric field in the index expression, the index key storage sequence is unaffected.

Two types of index exist within Recital, single indexes and multiple indexes. The INDEX ON...TO <ndx filename> command creates single index files with a default file extension of '.ndx'. With single indexes, each different sort order is stored in a separate file and each single index must be opened whenever the table is opened to ensure that the indexes are kept up to date. A table can have up to 20 open single index files. If data is modified when the index file is not opened, the index file will have to be reindexed using the REINDEX command. Once an index file has been created, you can use it at any time with your table by specifying the INDEX option with the USE command when you open the table, or using the SET INDEX TO command.

The INDEX ON...TAG <tagname> command creates multiple, or tagged, index files. A multiple index file may contain up to 128 index tags, each with a unique tagname. Each index tag represents a separate key expression. Multiple index filenames have .dbx as the file extension. Tagnames must be a maximum of 32 characters long. They must start with a letter or underscore and can include letters, underscores and digits 0-9.

There are two types of .dbx files. One type, known as the production index, is associated with a DBF file and is opened automatically when the .dbf file is opened. A production index file will always have the same basename as its table. The second type of .dbx file can be created with the OF <.dbx filename> qualifier. This creates a .dbx file that has a different name than the .dbf file, and may be opened separately. This type of multiple index file must be opened with the SET INDEX command, or the INDEX clause of the USE command.

The chief benefit of multiple index files is that many tags representing many <key expressions> are contained in one file. Each tag is updated as the table is updated, providing quick access to alternate ways of ordering records. The master order may be set to any of the index tags using the SET ORDER TO TAG, SET INDEX TO ...ORDER <tagname> commands, or the ORDER clause of the USE ... INDEX command.

If for any reason an index is not consistent with the table file, the Recital/4GL will display an appropriate error message. You can rebuild the index file using the REINDEX command.

To look up indexed records by their key fields, use the FIND or SEEK commands or the RLOOKUP(), LOOKUP() or SEEK() functions. The Recital/4GL performs partial key searches unless SET EXACT ON is in effect.

The key expression that you specify is stored inside the index file in an area known as the index file prologue. If you have used memory variables or alias pointers (->) inside the key expression, you must make sure that these are available any time the index is open. If User Defined Functions (UDFs) are present in the index expression, they must be activated before the index is opened and be available while the index is open. The expression that the UDF returns must be of fixed length. The RPAD() function can be used to make sure of this.

The default file extensions for single and tagged indexes can be changed using the SET COMPATIBLE and SET INDEXEXT commands.

DESCENDING

Indexing is always performed in ascending order, but if an index is created using the DESCENDING keyword, records are ordered in descending sequence. The DESCENDING option is only available for multiple index file tags, for single indexes, the DESCEND() function can be used within character key expressions. The DESCENDING keyword can be used on character string, numeric, date or logical index keys. The DESCENDING() function can be used to determine whether an index tag was created using the DESCENDING keyword. The DBXDESCEND() function must be used when searching in indexes created using the DESCENDING keyword.

FOR <condition>

The optional FOR clause is used to create an index that will contain those records that match the specified <condition>. The <condition> is saved with the index <key expression>, so further updates to the table will add only those records matching the <condition> to the index. When an index is created with a FOR <condition>, all currently open single indexes are closed. The SET INDEX TO command may be used to reopen the original index list. Conditional indexes are useful for taking a 'snapshot' of tables. Subsequent processing of files that are 'filtered' in this manner is dramatically accelerated. The FOR() function can be used to return the FOR <condition> of the active index.

UNIQUE

If SET UNIQUE ON is in effect or the UNIQUE keyword is specified, then only the first key of a given key expression will be added to the index. Duplicates will be discarded. The uniqueness of the records that you subsequently add to the table is checked, and an appropriate message displayed if the record cannot be added or updated because of a pre-existing key in the index file. If SET UNIQUE OFF is in effect and the UNIQUE option is not specified when the index is created, then duplicate keys are allowed. With SET PCUNIQUE ON, duplicate records will be hidden but can be added to unique indexes.

Example

use patrons
index on event tag event

Products

Recital Server, Recital