Using InterSystems Development Environments — Atelier and Studio
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:
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.
You can invoke the New Index wizard using the
. Alternatively, right-click the Class Inspector and select
or select the New Index
, 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).
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.
(optional) Description of the new index. This description is used when the class' documentation is displayed in the online class library documentation.
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 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.
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.
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.
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.
After you add an index definition to a class and compile, you can populate the index (place data into it) by using
, found in the Management Portal (Building Indices
The Studio does not automatically place data into indices.