Adding Indices to a Class
This chapter discusses how to add and edit index definitions to a persistent class definition.
An index definition instructs the Caché class compiler to create an index for one or more properties. Indices are typically used to make SQL queries more efficient.
You can add an index to a class definition in two ways:
Editing the class definition using the Class Editor.
Using the New Index wizard
To add an index using the Class Editor, position the cursor on a blank line in the Class Editor and enter an index declaration:
Index NameIndex On Name;
Alternatively, copy and paste an existing index declaration and then edit it.
For details on index definitions, see “Class Definitions” in the reference “Class Definitions” in Caché Class Definition Reference.
New Index Wizard
You can invoke the New Index wizard using the Class > Add > Index. Alternatively, right-click the Class Inspector and select Add Index or select the New Index icon, , from the toolbar.
The New Index wizard prompts you for information. To end, select Finish (default values are provided for any information you have not specified).
Name and Description Page
The New Index wizard prompts you for the following information (you can later modify any of these values):
(required) Name of the new index. This name must be a valid index name and must not conflict with the name of a previously defined index.
For a discussion of valid names, see the chapter “Caché Classes” in Using Caché Objects.
(optional) Description of the new index. This description is used when the class' documentation is displayed in the online class library documentation.
A description can include HTML formatting tags. For details, see “Using HTML Markup in Class Documentation” in the chapter “Defining and Compiling Classes” in Using Caché Objects.
Index Type Page
Caché supports the following types of indices.
A normal index is used for indexing on property values. You can further qualify a normal index by selecting one of the following:
|The set of properties associated with this index must have a combined value that is unique in the extent of objects of this class.
|The set of properties associated with this index are used to create the Object ID value used to store instances of this class in the database. You cannot modify the values of properties that are part of an IDKEY definition once an object has been saved. IDKEY implies that the property or properties are unique (as with a Unique Index).
|SQL Primary Key
|The set of properties associated is reported as the SQL Primary Key for the SQL table projected for this class. This implies that the property or properties are unique (as with a Unique Index).
An extent index is used to keep track of which objects belong to a specific class in a multiclass extent of objects. It differs from a so-called normal index in that you cannot specify additional characteristics for it.
You can also select how the index is physically implemented in the database:
This index is a traditional cross-index on the specified property or properties.
A bitmap index uses a compressed representation of a set of object ID values that correspond to a given indexed value. See Bitmap Indices in Caché SQL Optimization Guide for more information.
A bitslice index is a specialized form of index that enables very fast evaluation of certain expressions, such as sums and range conditions. Bitslice indices are currently automatically used in certain Caché SQL queries. Future versions of Caché SQL will make further use of bitslice indices to optimize additional queries.
Index Properties Page
On the Index Properties page, you can enter a list of one or more properties on which the index is based. For each property you can override the default collation function used to transform values stored in the index as well as any parameters for the collation function.
Important — There must not be a sequential pair of vertical bars (||) within the values of any property used by an IDKEY index, unless that property is a valid reference to an instance of a persistent class. This restriction is imposed by the way in which the Caché SQL mechanism works. The use of || in IDKey properties can result in unpredictable behavior.
Index Data Page
On the Index Data page, elect to store a copy of the data for any properties in the index.
You cannot store copies of data values with a bitmap index.
Results of Running the New Index Wizard
After running the New Index wizard, the Class Editor window is updated to include the new index definition. For example:
/// This is a Person class
class MyApp.Person extends %Persistent
Property Name As %String;
Index NameIndex On Name;
You can edit the index definition with either the Class Editor or the Class Inspector.
Populating an Index
After you add an index definition to a class and compile, you can populate the index (place data into it) by using Rebuild Indices, found in the Management Portal (Building Indices).
The Studio does not automatically place data into indices.