Define and Build Indexes

Every index has a unique name. This name is used for database administrative purposes (reporting, index building, dropping indexes, and so on). Like other SQL entities, an index has both an SQL index name and a corresponding index property name; these names differ in permitted characters, case-sensitivity, and maximum length. If defined using the SQL CREATE INDEX command, the system generates a corresponding index property name. If defined using a persistent class definition, the SqlName keyword allows the user to specify a different SQL index name (SQL map name). The Management Portal SQL interface Catalog Details displays the SQL index name (SQL Map Name) and the corresponding index property name (Index Name) for each index.

The index type is defined by two index class keywords, Type and Extent. The types of indexes available with InterSystems IRIS include:

• Standard Indexes (Type = index) — A persistent array that associates the indexed value(s) with the RowID(s) of the row(s) that contains the value(s). Any index not explicitly defined as a bitmap index, bitslice index, or extent index is a standard index.

• Bitmap Indexes (Type = bitmap) — A special kind of index that uses a series of bitstrings to represent the set of RowID values that correspond to a given indexed value; InterSystems IRIS includes a number of performance optimizations for bitmap indexes.

• Bitslice Indexes (Type = bitslice) — A special kind of index that enables very fast evaluation of certain expressions, such as sums and range conditions. Certain SQL queries automatically use bitslice indexes.

• Columnar Indexes (Type = columnar) — A special kind of index that enables very fast queries, especially ones involving filtering and aggregation operations, on columns whose underlying data is stored across rows. Columnar indexes are an experimental feature for 2022.2.

• Extent Indexes — An index of all of the objects in an extent. For more information, see the Extent index keyword page.

The maximum number of indexes for a table (class) is 400.

The index functionality described here applies to data stored in a persistent class. InterSystems SQL supports index functionality for data stored using the InterSystems IRIS default storage structure, %Storage.Persistent (%Storage.Persistent-mapped classes), and for data stored using %Storage.SQL (%Storage.SQL-mapped classes). You can define an index for a %Storage.SQL-mapped class using a functional index type. The index is defined in the same manner as an index in a class using default storage, with the following special considerations:

• The class must define the IdKey functional index, if it is not automatically system assigned. See Master Map, below.

• This functional index must be defined as an INDEX.

Refer to %Library.FunctionalIndexOpens in a new tab for further details.

The name of the subscripted global that stores index data is determined by the name of the global that stores the data in the table. These names are dependent on the values of the USEEXTENTSET and DEFAULTGLOBAL class parameters that define the table, either in a persistent class or by using the %CLASSPARAMETER key word in a CREATE TABLE statement. For more information about USEEXTENTSET and DEFAULTGLOBAL, see “Hashed Global Names” and “User-Defined Global Names” respectively.

The system automatically defines a Master Map for every table. The Master Map is not an index, it is a map that directly accesses the data itself using its map subscript field(s). By default, the master map subscript field is the system-defined RowID field. By default, this direct data access using the RowID field is represented with the SQL Map Name (SQL index name) IDKEY.

By default, a user-defined primary key is not the IDKEY. This is because Master Map lookup using RowID integers is almost always more efficient than lookup by primary key values. However, if you specify that the primary key is the IDKEY, the primary key index is defined as the Master Map for the table and SQL Map Name is the primary key SQL index name.

For a single-field primary key/IDKEY, the primary key index is the Master Map, but the Master Map data access column remains the RowID. This is because there is a one-to-one match between a record’s unique primary key field value and its RowID value, and RowID is the presumed more efficient lookup. For a multi-field primary key/IDKEY, the Master Map is given the primary key index name, and the Master Map data access columns are the primary key fields.

You can view the Master Map definition through the Management Portal SQL Catalog Details tab. This displays, among other items, the global name where the Master Map data is stored. For SQL and default storage, this Master Map global defaults to ^package.classnameD and the namespace is recorded to prevent ambiguity. For custom storage, no Master Map data storage global is defined; you can use the DATALOCATIONGLOBAL class parameter to specify a data storage global name.

For SQL and default storage, the Master Map data is stored in a subscripted global named either ^package.classnameD or ^hashpackage.hashclass.1 (refer to Index Global Names for more information). Note that the global name specifies the persistent class name, not the corresponding SQL table name, and that the global name is case-sensitive. You can supply the global name to ZWRITE to display the Master Map data.

Data access using a Master Map is inefficient, especially for large tables. For this reason, it is recommended that the user define indexes that can be used to access data fields specified in WHERE conditions, JOIN operations, and other operations.

A bitmap extent index is a bitmap index for the rows of the table, not for any specified field of the table. In a bitmap extent index, each bit represents a sequential RowID integer value, and the value of each bit specifies whether or not the corresponding row exists. InterSystems SQL uses this index to improve performance of COUNT(*), which returns the number of records (rows) in the table. A table can have, at most, one bitmap extent index. Attempting to create more than one bitmap extent index results in an SQLCODE -400 error with the %msg ERROR #5445: Multiple Extent indexes defined: DDLBEIndex.

• A table defined using CREATE TABLE automatically defines a bitmap extent index. This automatically-generated index is assigned the Index Name DDLBEIndex and the SQL MapName %%DDLBEIndex.

• A table defined as a persistent class does not automatically define a bitmap extent index. If you add a bitmap index to a persistent class that does not have a bitmap extent index, InterSystems IRIS automatically generates a bitmap extent index. This generated bitmap extent index has an Index Name and SQL MapName of $ClassName (where ClassName is the name of the table’s persistent class).

You can use the CREATE INDEX command with the BITMAPEXTENT keyword to add a bitmap extent index to a table, or to rename an automatically-generated bitmap extent index. For further details, refer to CREATE INDEX.

You can view a table’s bitmap extent index through the Management Portal SQL Catalog Details tab. Though a table can have only one bitmap extent index, a table that inherits from another table is listed with both its own bitmap extent index and the bitmap extent index of the table it extends from. For example, the Sample.Employee table extends the Sample.Person table; in the Catalog Details Maps/Indices Sample.Employee lists both a $Employee and $Person bitmap extent index.

In a table that undergoes many DELETE operations the storage for a bitmap extent index can gradually become less efficient. You can rebuild a Bitmap Extent index either by using the BUILD INDEX command or from the Management Portal by selecting the table’s Catalog Details tab, Maps/Indices option and selecting Rebuild Index.

The %SYS.Maint.BitmapOpens in a new tab utility methods compress the bitmap extent index, as well as bitmap indexes and bitslice indexes. For further details, see Maintaining Bitmap Indexes.

Invoking the %BuildIndices() method builds an existing bitmap extent index in any of the following cases: the %BuildIndices() pIndexList argument is not specified (build all defined indexes); pIndexList specifies the bitmap extent index by name; or pIndexList specifies any defined bitmap index. See Building Indexes Programmatically.

If you are using DDL statements to define tables, you can also use the following DDL commands to create and remove indexes:

• CREATE INDEX

• DROP INDEX

The DDL index commands do the following:

1. They update the corresponding class and table definitions on which an index is being added or removed. The modified class definition is recompiled.

2. They add or remove index data in the database as needed: The CREATE INDEX command populates the index using the data currently stored within the database. Similarly, the DROP INDEX command deletes the index data (that is, the actual index) from the database.

You can add index definitions to a %Persistent class definition by editing the text of the class definition. An index is defined on one or more index property expressions optionally followed by one or more optional index keywords. It takes the form:

INDEX index_name ON index_property_expression_list [index_keyword_list];

where:

• index_name is a valid identifier.

• index_property_expression_list is a list of the one or more comma-separated property expressions that serve as the basis for the index.

• index_keyword_list is an optional comma-separated list of index keywords, enclosed in square brackets. Used to specify the index Type for a bitmap or bitslice index. Also used to specify a Unique, IdKey, or PrimaryKey index. (An IdKey or PrimaryKey index is, by definition, also a Unique index.) The complete list of index keywords appears in the Class Definition Reference.

The index_property_expression_list argument consists of one or more index property expressions. An index property expression consists of:

• The name of the property to be indexed.

• An optional (ELEMENTS) or (KEYS) expression, which provide a means of indexing on collection subvalues. If the index property is not a collection, the user can use the BuildValueArray() method to produce an array containing keys and elements. For more information on keys and elements, see Indexing Collections.

• An optional collation expression. This consists of a collation name followed optionally by a list of one or more comma-separated collation parameters. You cannot specify an index collation for a Unique, IdKey, or PrimaryKey index. A Unique or PrimaryKey index takes its collation from the property (field) that is being indexing. An IdKey index is always EXACT collation. For a list of valid collation names, see Collation Types.

For example, the following class definition defines two properties and an index based on each of them:

Class MyApp.Student Extends %Persistent [DdlAllowed]
{
 Property Name As %String;
 Property GPA As %Decimal;

 Index NameIDX On Name;
 Index GPAIDX On GPA;
}

A more complex index definition might be:

 Index Index1 On (Property1 As SQLUPPER(77), Property2 AS EXACT);

Indexes on Combinations of Properties

You can define indexes on combinations of two or more properties (fields). Within a class definition, use the On clause of the index definition to specify a list of properties, such as:

Class MyApp.Employee Extends %Persistent [DdlAllowed]
{
 Property Name As %String;
 Property Salary As %Integer;
 Property State As %String(MAXLEN=2);

 Index MainIDX On(State,Salary);
}

An index on multiple properties may be useful if you need to perform queries that use a combination of field values, such as:

SELECT Name,State,Salary
  FROM Employee
  ORDER BY State,Salary

Index Collation

A Unique, PrimaryKey, or IdKey index cannot specify a collation type. For other types of indexes, each property specified in an index definition can optionally have a collation type. The index collation type should match the property (field) collation type when the index is applied.

1. If an index definition includes an explicitly specified collation for a property, the index uses that collation.

2. If an index definition does not include an explicitly specified collation for a property, the index uses the collation explicitly specified in the property definition.

3. If the property definition does not include an explicitly specified collation, then the index uses the collation that is the default for the property data type.

For example, the Name property is defined as a string, and therefore has, by default, SQLUPPER collation. If you define an index on Name, it takes, by default, the property’s collation, and the index would also be defined with SQLUPPER. The property collation and the index collation match.

However, if a comparison applies a different collation, for example, WHERE %EXACT(Name)=%EXACT(:invar), the property collation type in this usage no longer matches the index collation type. A mismatch between the property comparison collation type and the index collation type may cause the index to not be used. Therefore, in this case, you might wish to define the index for the Name property with collation EXACT. If an ON clause of a JOIN statement specifies a collation type, for example, FROM Table1 LEFT JOIN Table2 ON %EXACT(Table1.Name) = %EXACT(Table2.Name), a mismatch between the property collation type specified here and the index collation type may cause InterSystems IRIS to not use the index.

The following rules govern collation matches between an index and a property:

• Matching collation types always maximize use of an index.

• A mismatch of collation types, where the property is specified with EXACT collation (as shown above) and the index has some other collation allow the index to be used, but its use is less effective than matching collation types.

• A mismatch of collation types, where the property collation is not EXACT and the property collation does not match the index collation, causes the index to not be used.

To explicitly specify a collation for a property in an index definition, the syntax is:

Index IndexName On PropertyName As CollationName;

where

• IndexName is the name of the index

• PropertyName is the property being indexed

• CollationName is the type of collation being used for the index

For example:

Index NameIDX On Name As Exact;

Different properties can have different collation types. For example, in the following example the F1 property uses SQLUPPER collation while F2 uses EXACT collation:

Index Index1 On (F1 As SQLUPPER, F2 As EXACT);

For a list of recommended collation types, see Collation Types.

Note:

An index specified as Unique, PrimaryKey, or IdKey cannot specify an index collation. The index takes its collation from the property collations.

Using the Unique, PrimaryKey, and IdKey Keywords with Indexes

As is typical with SQL, InterSystems IRIS supports the notions of a unique key and a primary key. InterSystems IRIS also has the ability to define an IdKey, which is one that is a unique record ID for the instance of a class (row of a table). These features are implemented through the Unique, PrimaryKey, and IdKey keywords:

• Unique — Defines a UNIQUE constraint on the properties listed in the index’s list of properties. That is, only a unique data value for this property (field) can be indexed. Uniqueness is determined based on the property’s collation. For example, if the property collation is EXACT, values that differ in letter case are unique; if the property collation is SQLUPPER, values that differ in letter case are not unique. However, note that the uniqueness of indexes is not checked for properties that are undefined. In accordance with the SQL standard, an undefined property is always treated as unique.

• PrimaryKey — Defines a PRIMARY KEY constraint on the properties listed in the index’s list of properties.

• IdKey — Defines a unique constraint and specifies which properties are used to define the unique identity of an instance (row). An IdKey always has EXACT collation, even when it is of data type string.

The syntax of such keywords appears in the following example:

Class MyApp.SampleTable Extends %Persistent [DdlAllowed]
{
  Property Prop1 As %String;
  Property Prop2 As %String;
  Property Prop3 As %String;

  Index Prop1IDX on Prop1 [ Unique ];
  Index Prop2IDX on Prop2 [ PrimaryKey ];
  Index Prop3IDX on Prop3 [ IdKey ];
}

Note:

The IdKey, PrimaryKey, and Unique keywords are only valid with standard indexes. You cannot use them with bitmap or bitslice indexes.

It is also valid syntax to specify both the IdKey and PrimaryKey keywords together, such as:

 Index IDPKIDX on Prop4 [ IdKey, PrimaryKey ];

This syntax specifies that the IDPKIDX index is both the IdKey for the class (table), as well as its primary key. All other combinations of these keywords are redundant.

For any index defined with one of these keywords, there is a method that allows you to open the instance of the class where the properties associated with the index have particular values; for more information, see Opening an Instance by Index Key.

For more information on the IdKey keyword, see the IdKey page of the Class Definition Reference. For more information on the PrimaryKey keyword, see the PrimaryKey page of the Class Definition Reference. For more information on the Unique keyword, see the Unique page of the Class Definition Reference.

Storing Data with Indexes

You can specify that a copy of one or more data values be stored within an index using the index Data keyword:

Class Sample.Person Extends %Persistent [DdlAllowed]
{
 Property Name As %String;
 Property SSN As %String(MAXLEN=20);

 Index NameIDX On Name [Data = Name];
}

In this case, the index, NameIDX, is subscripted by the collated (uppercase) value of the various Name values. A copy of the actual (uncollated) value of the Name is stored within the index. These copies are maintained when changes are made to the Sample.Person table through SQL or to corresponding the Sample.Person class or its instances through objects.

Maintaining a copy of data along within an index can be helpful in cases where you frequently perform selective (selecting a few rows out of many) or ordered searches that return a few columns out of many.

For example, consider the following query against the Sample.Person table:

SELECT Name FROM Sample.Person ORDER BY Name

The SQL Engine could decide to satisfy this request entirely by reading from the NameIDX and never reading the master data for the table.

Note:

You cannot store data values with a bitmap index.

Indexing Collections

When a property is indexed, the value that is placed in the index is the entire collated property value. For collections, it is possible to define index properties that correspond to the element and key values of the collection by appending (ELEMENTS) or (KEYS) to the property name. (ELEMENTS) and (KEYS) allow you to specify that multiple values are produced from a single property value and each of these sub-values is indexed. When the property is a collection, then the ELEMENTS token references the elements of the collection by value and the KEYS token references them by position. When both ELEMENTS and KEYS are present in a single index definition. the index key value includes the key and associated element value.

For example, suppose there is an index based on FavoriteColors property of the Sample.Person class. The simplest form of an index on the items in this property’s collection would be either of:

 INDEX fcIDX1 ON (FavoriteColors(ELEMENTS));

or

 INDEX fcIDX2 ON (FavoriteColors(KEYS));

where FavoriteColors(ELEMENTS) refers to the elements of the FavoriteColors property and FavoriteColors(KEYS) refers to the keys of the FavoriteColors property. The general form is propertyName(ELEMENTS) or propertyName(KEYS), where that collection’s content is the set of elements contained in a property defined as a List Of or an Array Of some data type. For information on collections, see Working with Collections.

To index literal properties (described in Defining and Using Literal Properties), you can create an index value array as produced by a propertyNameBuildValueArray() method (described in the following section). As with collections proper, the (ELEMENTS) and (KEYS) syntax is valid with index value arrays.

If property-collection is projected as array, then the index must obey the following restrictions in order to be projected to the collection table. The index must include (KEYS). The index cannot reference any properties other than the collection itself and the object's ID value. If a projected index also defines DATA to be stored in the index, then the data properties stored must also be restricted to the collection and the ID. Otherwise the index is not projected. This restriction applies to an index on a collection property that is projected as an array; it does not apply to an index on a collection that is projected as a list. For further details, refer to Controlling the SQL Projection of Collection Properties.

Indexes that correspond to element or key values of a collection can also have all the standard index features, such as storing data with the index, index-specific collations, and so on.

InterSystems SQL can use a collection index by specifying the FOR SOME %ELEMENT predicate.

Indexing Data Type Properties with (ELEMENTS) and (KEYS)

For the purposes of indexing data type properties, you can also create index value arrays using the BuildValueArray() method. This method parses a property value into an array of keys and elements. It does this by producing a collection of element values derived from the value of the property with which it is associated. This method does not work on existing collection properties.

When you use BuildValueArray() to create an index value array, its structure is suitable for indexing.

The BuildValueArray() method has the name propertyNameBuildValueArray() and its signature is:

ClassMethod propertynameBuildValueArray(value, ByRef valueArray As %Library.String) As %Status

where

• The name of the BuildValueArray() method derives from the property name in the typical way for composite methods.

• The first argument is the property value.

• The second argument is an array that is passed by reference. This is an array containing key-element pairs where the array subscripted by the key is equal to the element.

• The method returns a %Status value.

Indexing Array Collections

To specify an index on an array collection property, you must include the keys in the index definition. For example:

Class MyApp.Branch Extends %Persistent [ DdlAllowed ]
{
  Property Name As %String;
  Property Employees As Array Of MyApp.Employee;

  Index EmpIndex On (Employees(KEYS), Employees(ELEMENTS));
}

These keys identify the RowID of the array element’s child table row. Without this key, the parent table does not project an index to the child table. Because this projection does not occur, INSERT operations into the parent table fail.

Indexing an Embedded Object (%SerialObject) Property

To index a property in an embedded object, you create an index in the persistent class referencing that embedded object. The property name must specify the name of the referencing field in the table (%Persistent class) and the property in the embedded object (%SerialObject), as shown in the following example:

Class Sample.Person Extends (%Persistent) [ DdlAllowed ]
{  Property Name As %String(MAXLEN=50);
   Property Home As Sample.Address;
   Index StateInx On Home.State;
} 

Here Home is a property in Sample.Person that references the embedded object Sample.Address, which contains the State property, as shown in the following example:

Class Sample.Address Extends (%SerialObject)
{  Property Street As %String;
   Property City As %String;
   Property State As %String;
   Property PostalCode As %String;
 }

Only the data values in the instance of the embedded object associated with the persistent class property reference are indexed. You cannot index a %SerialObject property directly. The SqlCategory for %Library.SerialObject (and all sub-classes of %SerialObject that do not define the SqlCategory explicitly) is STRING.

You can also define an index on an embedded object property using the SQL CREATE INDEX statement, as shown in the following example:

CREATE INDEX StateIdx ON TABLE Sample.Person (Home_State)

For more details, see Introduction to Serial Objects and Embedded Object (%SerialObject).

Bitmap indexes work in the following way. Suppose you have a Person table containing a number of columns:

Each row in this table has a system-assigned RowID number (a set of increasing integer values). A bitmap index uses a set of bitstrings (a string containing 1 and 0 values). Within a bitstring, the ordinal position of a bit corresponds to the RowID of the indexed table. For a given value, say where State is “NY”, there is a string of bits with a 1 for every position that corresponds to a row containing “NY” and a 0 in every other position.

For example, a bitmap index on State might look like this:

While an index on Age might look like this:

In addition to using bitmap indexes for standard operations, the SQL engine can use bitmap indexes to efficiently perform special set-based operations using combinations of multiple indexes. For example, to find all instances of Person that are 24 years old and live in New York, the SQL Engine can simply perform the logical AND of the Age and State indexes:

The resulting bitmap contains the set of all rows that match the search criteria. The SQL Engine uses this to return data from these rows.

The SQL Engine can use bitmap indexes for the following operations:

• ANDing of multiple conditions on a given table.

• ORing of multiple conditions on a given table.

• RANGE conditions on a given table.

• COUNT operations on a given table.

If you are using DDL statements to define tables, you can also use the following DDL commands to create and remove bitmap indexes for a table with a positive integer ID:

• CREATE INDEX

• DROP INDEX

This is identical to creating standard indexes, except that you must add the BITMAP keyword to the CREATE INDEX statement:

CREATE BITMAP INDEX RegionIDX ON TABLE MyApp.SalesPerson (Region)

If the table’s ID is a field with values restricted to unique positive integers, you can add bitmap index definitions to a class definition using either the New Index Wizard or by editing the text of the class definition in the same way that you would create a standard index. The only difference is that you need to specify the index Type as being “bitmap”:

Class MyApp.SalesPerson Extends %Persistent [DdlAllowed]
{
 Property Name As %String;
 Property Region As %Integer;

 Index RegionIDX On Region [Type = bitmap];
}

If the table’s ID is not restricted to positive integers, you can create a %BID field to use to create bitmap index definitions. You can use this option for a table with an ID field of any datatype, as well as an IDKEY consisting of multiple fields (which includes child tables). A %BID bitmap can be created for either data storage type: a default structure table or a %Storage.SQL table. This feature is referred to as “Bitmaps for Any Table,” or BAT.

To enable use of bitmap indexes on such a table, you must do the following:

1. Define the %BID field, or identify an existing field as the %BID field. The data type of this field must restrict values to unique positive integers. For example, in this table, the IDKey is a composite of two fields that are not restricted to positive integers. This makes the MyBID field, which has a positive integer data type (%Counter) a candidate for the %BID field.

   Class MyTable Extends %Persistent [ DdlAllowed ]
   {
     Property IdField1 As %Integer;
     Property IdField2 As %Integer;
     Property MyBID As %Counter; /* %BID Field */
   
     Index IDIdx On (IdField1, IdField2) [ IdKey, Unique ];
   }

   

2. Define the BIDField class parameter to identify the %BID field for the SQL compiler. Set its value to the SQLFieldName of the %BID field. For example:

   Class MyTable Extends %Persistent [ DdlAllowed ]
   {
     Parameter BIDField = "MyBID"; /* BIDField Class Parameter */
   
     Property IdField1 As %Integer;
     Property IdField2 As %Integer;
     Property MyBID As %Counter;
   
     Index IDIdx On (IdField1, IdField2) [ IdKey, Unique ];
   }

   

3. Define the BATKey index. This index acts as the master map and data map for the SQL query processor. Typically, the %BID field is the first subscript of the BATKey index. The map data can then include the IDKEY fields and any additional properties that you want to have the fastest access to. You must set the index on the %BID field. For example:

   Class MyTable Extends %Persistent [ DdlAllowed ]
   {
     Parameter BIDField = "MyBID";
   
     Property IdField1 As %Integer;
     Property IdField2 As %Integer;
     Property MyBID As %Counter;
   
     Index MyBATKey On MyBID [ Type = key, Unique ]; /* BATKey Index */
     Index IDIdx On (IdField1, IdField2) [ IdKey, Unique ];
   }

   

4. Define the BATKey class parameter identify the BATKey index for the SQL compiler. Set its value to the SQLFieldName of the BATKey index. For example:

   Class MyTable Extends %Persistent [ DdlAllowed ]
   {
     Parameter BIDField = "MyBID";
     Parameter BATKey = "MyBATKey"; /* BATKey Class Parameter */
   
     Property IdField1 As %Integer;
     Property IdField2 As %Integer;
     Property MyBID As %Counter;
   
     Index MyBATKey On MyBID [ Type = key, Unique ];
     Index IDIdx On (IdField1, IdField2) [ IdKey, Unique ];
   }

   

5. Define the %BID locator index, or identify an existing index as the %BID locator index. This index ties the %BID index to the IDKey fields of the table. For example:

   Class MyTable Extends %Persistent [ DdlAllowed ]
   {
     Parameter BIDField = "MyBID";
     Parameter BATKey = "MyBATKey";
   
     Property IdField1 As %Integer;
     Property IdField2 As %Integer;
     Property MyBID As %Counter;
   
     Index MyBATKey On MyBID [ Type = key, Unique ];
     Index IDIdx On (IdField1, IdField2) [ IdKey, Unique ];
     Index BIDLocIdx On (IdField1, IdField2, MyBID) [ Unique ]; /* %BID Locator Index */
   }

   

This table now supports bitmap indexes. You can define bitmap indexes as needed using standard syntax. For example: Index RegionIDX On Region [Type = bitmap];

Tables created in this way also support bitslice indexes, which can also be defined using standard syntax.

A bitmap index requires a bitmap extent index. Defining a persistent class only generates a bitmap extent index if one or more bitmap indexes are defined. Therefore, when compiling a persistent class that contains a bitmap index, the class compiler generates a bitmap extent index if no bitmap extent index is defined for that class. Tables defined with the CREATE TABLE DDL statement automatically generate a bitmap extent index.

If you delete all bitmap indexes from the persistent class definition, the bitmap extent index is automatically deleted. However, if you rename the bitmap extent index (for example, using the CREATE BITMAPEXTENT INDEX command) deleting the bitmap index does not delete the bitmap extent index.

When building indexes for a class, the bitmap extent index is built either if you explicitly build the bitmap extent index, or if you build a bitmap index and the bitmap extent index is empty.

A class inherits its bitmap extent index from the primary superclass if it exists, either defined or generated. A bitmap extent index inherited from a primary superclass is considered to be a bitmap index and will trigger a bitmap extent index to be generated in the subclass, if no bitmap extent index is explicitly defined in that subclass.A bitmap extent index is defined as follows:

Class Test.Index Extends %Registered Object
{
  Property Data As %Integer [ InitialExpression = {$RANDOM(100000)}];

  Index DataIndex On Data [ Type = bitmap ];
  Index ExtentIndex [ Extent, Type = bitmap ];
}

InterSystems IRIS does not generate a bitmap extent index in a superclass based on future possibility. This means that InterSystems IRIS does not ever generate a bitmap extent index in a persistent class unless an index whose type = bitmap is present. A presumption that some future subclass might introduce an index with type = bitmap is not sufficient.

The following is a general guideline for choosing between bitmap and standard indexes. In general, use standard indexes for indexing on all types of keys and references:

• Primary key

• Foreign key

• Unique keys

• Relationships

• Simple object references

Otherwise, bitmap indexes are generally preferable (assuming that the table uses system-assigned numeric ID numbers).

Other factors:

• Separate bitmap indexes on each property usually have better performance than a bitmap index on multiple properties. This is because the SQL engine can efficiently combine separate bitmap indexes using AND and OR operations.

• If a property (or a set of properties that you really need to index together) has more than 10,000-20,000 distinct values (or value combinations), consider standard indexes. If, however, these values are very unevenly distributed so that a small number of values accounts for a substantial fraction of rows, then a bitmap index could be much better. In general, the goal is to reduce the overall size required by the index.

All bitmap indexes have the following restrictions:

• You cannot define a bitmap index on a UNIQUE column.

• You cannot store data values within a bitmap index.

• You cannot define a bitmap index on a field unless the SqlCategory of the ID field is INTEGER, DATE, POSIXTIME, or NUMERIC (with scale=0).

• For a table containing more than 1 million records, a bitmap index is less efficient than a standard index when the number of unique values exceeds 10,000. Therefore, for a large table it is recommended that you avoid using a bitmap index for any field that contains (or is likely to contain) more than 10,000 unique values; for a table of any size, avoid using a bitmap index for any field that is likely to contain more than 20,000 unique values. These are general approximations, not exact numbers.

You must create a %BID property to support bitmap indexes on a table that:

• Uses a non-integer field as the unique ID key.

• Uses a multi-field ID key.

• Is a child table within a parent-child relationship.

You can use the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method SET status=$SYSTEM.SQL.Util.SetOption("BitmapFriendlyCheck",1,.oldval) to set a system-wide configuration parameter to check at compile time for this restriction, determining whether a defined bitmap index is allowed in a %Storage.SQL class. This check only applies to classes that use %Storage.SQL. The default is 0. You can use $SYSTEM.SQL.Util.GetOption("BitmapFriendlyCheck")Opens in a new tab to determine the current configuration of this option.

In a volatile table (one that undergoes many INSERT and DELETE operations) the storage for a bitmap index can gradually become less efficient. To maintain bitmap indexes, you can run the %SYS.Maint.BitmapOpens in a new tab utility methods to compress the bitmap indexes, restoring them to optimal efficiency. You can use the OneClass()Opens in a new tab method to compress the bitmap indexes for a single class or the Namespace()Opens in a new tab method to compress the bitmap indexes for an entire namespace. These maintenance methods can be run on a live system.

The results of running the %SYS.Maint.BitmapOpens in a new tab utility methods are written to the process that invoked the method. These results are also written to the class %SYS.Maint.BitmapResultsOpens in a new tab.

InterSystems SQL provides the following extensions to directly manipulate bitmap indexes:

• %CHUNK function

• %BITPOS function

• %BITMAP aggregate function

• %BITMAPCHUNK aggregate function

• %SETINCHUNK predicate condition

All of these extensions follow the InterSystems SQL conventions for bitmap representation, representing a set of positive integers as a sequence of bitmap chunks, of up to 64,000 integers each.

These extensions enable easier and more efficient manipulation of certain conditions and filters, both within a query and in embedded SQL. In embedded SQL they enable simple input and output of bitmaps, especially at the single chunk level. They support the processing of complete bitmaps, which are handled by %BITMAP() and the %SQL.Bitmap class. They also enable bitmap processing for non-RowID values, such as foreign key values, parent-reference of a child table, either column of an association, etc.

For example, to output the bitmap for a specified chunk:

SELECT %BITMAPCHUNK(Home_Zip) FROM Sample.Person
WHERE %CHUNK(Home_Zip)=2

To output all the chunks for the whole table:

SELECT %CHUNK(Home_Zip),%BITMAPCHUNK(Home_Zip) FROM Sample.Person
GROUP BY %CHUNK(Home_Zip) ORDER BY 1

After creating an index at the class or DDL level, you should build it using the BUILD INDEX command. This statement can be used to build all of the indexes in a namespace, all the indexes in a schema, or only the indexes specified in the command. By default, it acquires an extent lock on each table prior to building its indexes and releases it when it has finished, making it safe to use on an active system. Queries will not be able to use an index while it is being built. Any data that is inserted or updated in the table using the INSERT or UPDATE commands while the BUILD INDEX command is running will be included in the building process.

BUILD INDEX uses the journaling setting for the current process to log any errors. You can turn off locking and journaling behavior by supplying the %NOLOCK and %NOJOURN options, respectively.

The following examples build indexes for the MyApp.Salesperson class:

BUILD INDEX FOR TABLE MyApp.SalesPerson
BUILD INDEX FOR TABLE MyApp.SalesPerson INDEX NameIdx, SSNKey

The first statement builds all of the indexes for the specified table (or class) name. The second statement builds only the NameIdx and SSNKey indexes.

You can build existing indexes (rebuild indexes) for a table by doing the following:

1. From the Management Portal select System Explorer, then SQL. Select a namespace by clicking the name of the current namespace at the top of the page; this displays the list of available namespaces. After selecting a namespace, select the Schema drop-down list on the left side of the screen. This displays a list of the schemas in the current namespace with boolean flags indicating whether there are any tables or any views associated with each schema.

2. Select a schema from this list; it appears in the Schema box. Just above it is a drop-down list that allows you to select Tables, System Tables, Views, Procedures, or All of these that belong to the schema. Select either Tables or All, then open the Tables folder to list the tables in this schema. If there are no tables, opening the folder displays a blank page. (If you have not selected Tables or All, opening the Tables folder lists the tables for the entire namespace.)

3. Select one of the listed Tables. This displays the Catalog Details for the table.

   • To rebuild all indexes: click the Actions drop-down list and select Rebuild Table’s Indices.

   • To rebuild a single index: click the Indices button to display the existing indexes. Each listed index has the option to Rebuild Index.

You can also use the %BuildIndices() and %BuildIndicesAsync() methods provided by the %PersistentOpens in a new tab class for the table to build indexes. Note that these methods are only provided for classes that use InterSystems IRIS default storage structure. Calling these methods requires that at least one index definition has been added to a specified class and compiled. Read about the methods more fully in the class reference pages linked below.

• %Library.Persistent.%BuildIndices()Opens in a new tab: %BuildIndices() executes as a background process but the caller has to wait for %BuildIndices() to complete before receiving control back.

• %Library.Persistent.%BuildIndicesAsync()Opens in a new tab: %BuildIndicesAsync() initiates %BuildIndices() as a background process and the caller immediately receives control back. The first argument to %BuildIndicesAsync() is the queueToken output argument. The remaining arguments are the same as %BuildIndices().

%ValidateIndices() is supported for sharded classes and for shard-master class tables (Sharded=1). You can invoke %ValidateIndices, either directly as a class method, or from $SYSTEM.OBJ.ValidateIndices on the shard master class. Index validation is then performed on the shard local class on each shard, and the results are returned to the caller on the shard master. When using %ValidateIndices() on a sharded class, the verbose flag is forced to 0. There is no output to the current device. Any issues found/corrected are returned in the byreference errors() array.

To determine if adding an index improves query performance, run the query from the Management Portal SQL interface and note in Performance the number of global references. Add the index and then rerun the query, noting the number of global references. A useful index should reduce the number of global references. You can prevent use of an index by using the %NOINDEX keyword as preface to a WHERE clause or ON clause condition.

You should index fields (properties) that are specified in a JOIN. A LEFT OUTER JOIN starts with the left table, and then looks into the right table; therefore, you should index the field from the right table. In the following example, you should index T2.f2:

   FROM Table1 AS T1 LEFT OUTER JOIN Table2 AS T2 ON T1.f1 = T2.f2

An INNER JOIN should have indexes on both ON clause fields.

Run Show Plan and follow to the first map. If the first bullet item in the Query Plan is “Read master map”, or the Query Plan calls a module whose first bullet item is “Read master map”, the query first map is the master map rather than an index map. Because the master map reads the data itself, rather than an index to the data, this almost always indicates an inefficient Query Plan. Unless the table is relatively small, you should create an index so that when you rerun this query the Query Plan first map says “Read index map.”

You should index fields that are specified in a WHERE clause equal condition.

You may wish to index fields that are specified in a WHERE clause range condition, and fields specified in GROUP BY and ORDER BY clauses.

Under certain circumstances, an index based on a range condition could make a query slower. This can occur if the vast majority of the rows meet the specified range condition. For example, if the query clause WHERE Date < CURRENT_DATE is used with a database in which most of the records are from prior dates, indexing on Date may actually slow down the query. This is because the Query Optimizer assumes range conditions will return a relatively small number of rows, and optimizes for this situation. You can determine if this is occurring by prefacing the range condition with %NOINDEX and then run the query again.

If you are performing a comparison using an indexed field, the field as specified in the comparison should have the same collation type as it has in the corresponding index. For example, the Name field in the WHERE clause of a SELECT or in the ON clause of a JOIN should have the same collation as the index defined for the Name field. If there is a mismatch between the field collation and the index collation, the index may be less effective or may not be used at all. For further details, refer to Index Collation.

For details on how to create an index and the available index types and options, refer to the CREATE INDEX command, and Defining and Building Indexes.

The following system-wide configuration methods can be used to optimize use of indexes in queries:

• To use the PRIMARY KEY as the IDKey index, set the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("DDLPKeyNotIDKey",0,.oldval). The default is 1.

• To use indexes for SELECT DISTINCT queries set the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("FastDistinct",1,.oldval). The default is 1.

For further details, refer to SQL and Object Settings Pages listed in System Administration Guide.

The FROM clause supports the %ALLINDEX and %IGNOREINDEX optimize-option keywords as hints. These optimize-option keywords govern all index use in the query.

You can use the %NOINDEX condition-level hint to specify exceptions to the use of an index for a specific condition. The %NOINDEX hint is placed in front of each condition for which no index should be used. For example, WHERE %NOINDEX hiredate < ?. This is most commonly used when the overwhelming majority of the data is selected (or not selected) by the condition. With a less-than (<) or greater-than (>) condition, use of the %NOINDEX condition-level hint is often beneficial. With an equality condition, use of the %NOINDEX condition-level hint provides no benefit. With a join condition, %NOINDEX is supported for ON clause joins.

The %NOINDEX keyword can be used to override indexing optimization established in the FROM clause. In the following example, the %ALLINDEX optimization keyword applies to all condition tests except the E.Age condition:

  SELECT P.Name,P.Age,E.Name,E.Age
  FROM %ALLINDEX Sample.Person AS P LEFT OUTER JOIN Sample.Employee AS E
       ON P.Name=E.Name
  WHERE P.Age > 21 AND %NOINDEX E.Age < 65

You can analyze index usage for SQL queries from the Management Portal using either of the following:

• Select System Explorer, select Tools, select SQL Performance Tools, then select Index Analyzer.

• Select System Explorer, select SQL, then from the Tools drop-down menu select Index Analyzer.

The Index Analyzer provides an SQL Statement Count display for the current namespace, and five index analysis report options.

For ID key, primary key, or unique indexes, the indexnameOpen() method (where indexname is the name of the index) allows you to open the object whose index property value or values match supplied value or values. Because this method has one argument corresponding to each property in the index, the method has three or more arguments:

• The first argument(s) each correspond to the properties in the index.

• The penultimate argument specifies the concurrency value with which the object is to be opened (with the available concurrency settings listed in Object Concurrency).

• The final argument can accept a %Status code, in case the method fails to open an instance.

The method returns an OREF if it locates a matching instance.

For example, suppose that a class includes the following index definition:

 Index SSNKey On SSN [ Unique ];

then, if the referenced object has been stored to disk and has a unique ID value, you can invoke the method as follows:

  SET person = ##class(Sample.Person).SSNKeyOpen("111-22-3333",2,.sc)

Upon successful completion, the method has set the value of person to the OREF of the instance of Sample.Person whose SSN property has a value of 111–22–3333.

The second argument to the method specifies the concurrency value, which here is 2 (shared). The third argument holds an optional %Status code; if the method does not find an object that matches the supplied value, then an error message is written to the status parameter sc.

This method is implemented as the %Compiler.Type.Index.Open() method; this method is analogous to the %Persistent.Open()Opens in a new tab and %Persistent.OpenId()Opens in a new tab methods, except that it uses the properties in the index definition instead of the OID or ID argument.

The indexnameExists() method (where indexname is the name of the index) checks if an instance exists with the index property value or values specified by the method’s arguments. The method has one argument corresponding to each property in the index; its final, optional argument can receive the object’s ID, if one matches the supplied value(s). The method returns a boolean, indicating success (1) or failure (0). This method is implemented as the %Compiler.Type.Index.Exists() method.

For example, suppose that a class includes the following index definition:

 Index SSNKey On SSN [ Unique ];

then, if the referenced object has been stored to disk and has a unique ID value, you can invoke the method as follows:

  SET success = ##class(Sample.Person).SSNKeyExists("111-22-3333",.id)

Upon successful completion, success equals 1 and id contains the ID matching the object that was found.

This method returns values for all indexes except:

• bitmap indexes, or a bitmap extent index.

• when the index includes an (ELEMENTS) or (KEYS) expression. For more information on such indexes, see Indexing Collections.

The indexnameDelete() method (where indexname is the name of the index) is meant for use with a Unique, PrimaryKey, and or IdKey index; it deletes the instance whose key value matches the supplied key property/column values. There is one optional argument, which you can use to specify a concurrency setting for the operation. The method returns a %Status code. It is implemented as the %Compiler.Type.Index.Delete() method.