CREATE INDEX

version 11

CREATE INDEX (aTable; fieldsArray; indexType; indexName{; *})

The CREATE INDEX command can be used to create:

• A standard index on one or more fields (composite index) or

• A keyword index on a field.

The index is created for the aTable table by using one or more fields designated by the fieldsArray pointer array. This array contains a single row when you want to create a simple index and two or more rows when you want to create a composite index (except in the case of a keyword index). In the case of composite indexes, the order of the fields in the array is important when the index is being built.

The indexType parameter is used to define the type of index to be created. You can pass one of the following constants, found in the "Index Type" theme:

• Keywords Index (-1): Keyword type index. Remember that keywords cannot be composite: you must pass only one field in the fieldsArray array.

• Default Index Type (0): In this case, 4D sets the index type (except for keyword indexes) that is optimal according to the contents of the field.

• Standard BTree Index (1): Index of the standard B-Tree type. This multipurpose index type is used in previous versions of 4D.

• Cluster BTree Index (3): Index of the B-Tree type using clusters. This index type is optimal when the index contains few keys, i.e. when the same values occur frequently in the data.

In the indexName parameter, pass the name of the index to be created. This name is mandatory; if you pass an empty string, an error is generated. If the indexName index already exists, the command does nothing.

The optional * parameter, when it is passed, is used to carry out indexing in asynchronous mode. In this mode, the original method continues its execution after the call from the command, regardless of whether or not the indexing is finished.

If the CREATE INDEX command encounters any locked records, they will not be indexed and the command will wait for them to be unlocked.

If a problem occurs during command execution (non-indexed field, attempt to create a keyword index on more than one field, etc.), an error is generated. This error can be intercepted using an errorhandlingmethod.

1. Creation of two standard indexes on the "Last Name" and "Telephone"fields of the [Customers] table:

   ARRAY POINTER(fieldPtrArr;1)
   fieldPtrArr{1}:=->[Customers]LastName
   CREATE INDEX([Customers];fieldPtrArr;Standard BTree Index;"CustLNameIdx")
   fieldPtrArr{1}:=->[Customers]Telephone
   CREATE INDEX([Customers];fieldPtrArr;Standard BTree Index;"CustTelIdx")

2. Creation of a keywords index on the "Observations" field of the [Customers] table:

   ARRAY POINTER(fieldPtrArr;1)
   fieldPtrArr{1}:=->[Customers]Observations
   CREATE INDEX([Customers];fieldPtrArr;Keywords Index;"CustObsIdx")

3. Creation of a composite index on the "City" and "Zipcode" fields of the [Customers] table:

   ARRAY POINTER(fieldPtrArr;2)
   fieldPtrArr{1}:=->[Customers]City
   fieldPtrArr{2}:=->[Customers]Zipcode
   CREATE INDEX([Customers];fieldPtrArr;Standard BTree Index;"CityZip")

DELETE INDEX, SET INDEX.