4D v13.4

CREATE INDEX

Home

 
4D v13.4
CREATE INDEX

CREATE INDEX 


 

CREATE INDEX ( aTable ; fieldsArray ; indexType ; indexName {; *} ) 
Parameter Type   Description
aTable  Table in Table for which to create an index
fieldsArray  Pointer array in Pointer(s) to field(s) to be indexed
indexType  Longint in Type of index to create: -1 = Keywords, 0 = default, 1 = Standard B-Tree, 3 = Cluster B-Tree
indexName  Text in Name of index to create
Operator in If passed = asynchronous indexing

The CREATE INDEX command creates:

  • 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 sets the type of index to be created. You can pass one of the following constants, found in the Index Type theme:

Constant Type Value Comment
Cluster BTree Index Longint 3 B-Tree type index using clusters. This type of index is optimized when the index contains few keywords, i.e. when the same values occur frequently in the data.
Default Index Type Longint 0 4D specifies the index type (excluding keywords indexes) that is the most optimized according to the contents of the field.
Keywords Index Longint -1 Permits word-by-word indexing of field contents. This type of index can only be used with fields of the Text, Alpha or Picture type. Warning: Keywords indexes cannot be composite.
Standard BTree Index Longint 1 Standard B-Tree type index. This multi-purpose index type is used in previous versions of 4D

Note: A B-Tree index associated with a Text type field stores the first 1024 characters of the field (maximum). Therefore in this context, searches for strings containing more than 1024 characters will fail.

In the indexName parameter, you pass the name of the index to be created. Naming the index is necessary if several different types of indexes can be associated with the same field and if you want to be able to delete them individually using the DELETE INDEX command. If the indexName index already exists, the command does nothing.

The optional * parameter, when it is passed, performs 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 error-handling method.

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

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

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

 
PROPERTIES 

Product: 4D
Theme: Structure Access
Number: 966

 
INDEX

Alphabetical list of commands

 
HISTORY 

Created: 4D v11 SQL

 
SEE ALSO 

DELETE INDEX
SET INDEX