The index

A persistent class can define one or more indexes; Other data structures are used to improve the efficiency of operations, such as sorting or conditional searching. InterSystems SQL uses these indexes when executing queries. InterSystems IRIS objects and SQL automatically maintain the correct values in the indexes when performing INSERT, UPDATE, and DELETE operations.

Standard index storage structure

A standard index associates an ordered set of one or more attribute values with the object ID value of the object containing the attribute.

For example, suppose we define a simple persistent myapp. Person class with two text attributes and an index for its Name attribute:

Class MyApp.Person Extends %Persistent
{
Index NameIdx On Name;

Property Name As %String;
Property Age As %Integer;
}
Copy the code

If we create and save multiple instances of this Person class, the generated data and index global variables look like this:

 // data global
 ^MyApp.PersonD = 3  // counter node
 ^MyApp.PersonD(1) = $LB("".34."Jones")
 ^MyApp.PersonD(2) = $LB("".22."Smith")
 ^MyApp.PersonD(3) = $LB("".45."Jones")


 // index global
 ^MyApp.PersonI("NameIdx"." JONES".1) = ""
 ^MyApp.PersonI("NameIdx"." JONES".3) = ""
 ^MyApp.PersonI("NameIdx"." SMITH".2) = ""
Copy the code

Please note the following about global indexes:

1. By default, it is placed in a global variable whose name is appended later"I"Class name (representing index).
2. By default, the first subscript is the index name; This allows multiple indexes to be stored in the same global without conflict.
3. The second subscript contains the collated data values. In this case, use the defaultSQLUPPERSorting functions sort data. This converts all characters to uppercase (sorting regardless of case) and precedes them with a space character (forcing all data to be sorted as a string).
4. The third subscript contains the object ID value of the object that contains the index data value.
5. The node itself is empty; All required data is stored in subscripts. Note that if the index definition specifies that the data should be stored with the index, it is placed in the node of the global index.

The index contains enough information to satisfy many queries, such as listing all Person classes by name.

Bitmap indexes

A bitmap index is similar to a standard index except that it uses a series of bitstrings to store a set of object ID values corresponding to the index value.

Logical operation of bitmap index

A bit string is a string containing a special set of bits (0 and 1 values) in a compressed format. InterSystems IRIS includes a set of functions for efficiently creating and using bitstrings. These are listed in the table below:

Bit operation

In a bitmap index, the sequential positions in the bitstring correspond to rows (object ID numbers) in the index table. For a given value, the bitmap index maintains a bit string containing 1 in every line where a given value exists and 0 in every line where no given value exists. Note that bitmap indexes only apply to objects that use the default storage structure assigned by the system, numeric object ID values.

For example, suppose we have a table like the following:

If the State and Product columns have a graph index, they contain the following values:

The bitmap index on the State column contains the following bitstring values:

MA	1	0	0	1	1
NY	0	1	1	0	0
Copy the code

Note that for the value “MA”, there is a 1 in the positions (1, 4, and 5) corresponding to the table rows whose State is equal to “MA”.

Similarly, the bitmap index on the Product column contains the following bitstring values (note that these values are sorted in uppercase in the index):

CHAIR	0	0	1	1	0
HAT	1	1	0	0	1
Copy the code

InterSystems SQL Engine performs many operations by iterating over the bitstrings maintained by these indexes, calculating in-bit numbers, or performing logical combinations (AND, or). For example, to find all rows where State is equal to “MA” and Product is equal to “HAT”, the SQL engine can simply combine the appropriate bit string with the logical and.

In addition to these indexes, the system maintains an additional index, called an “extent index,” that contains 1 for every row that exists and 0 for non-existent rows (such as deleted rows). This is used for certain operations, such as negation.

Storage structure of bitmap index

A bitmap index associates an ordered collection of one or more attribute values with one or more bit strings containing the object ID value corresponding to the attribute value.

For example, suppose we define a simple persistent MyApp. The Person class has two text attributes and a bitmap index on the Age attribute:

Class MyApp.Person Extends %Persistent
{
Index AgeIdx On Age [Type = bitmap];

Property Name As %String;
Property Age As %Integer;
}
Copy the code

If we create and save several instances of the Person class, the resulting data and index global variables look something like this:

 // data global
 ^MyApp.PersonD = 3  // counter node
 ^MyApp.PersonD(1) = $LB("".34."Jones")
 ^MyApp.PersonD(2) = $LB("".34."Smith")
 ^MyApp.PersonD(3) = $LB("".45."Jones")

 // index global
 ^MyApp.PersonI("AgeIdx".34.1) = 110.. ^MyApp.PersonI("AgeIdx".45.1) = 001..// extent index global
 ^MyApp.PersonI("$Person".1) = 111.. ^MyApp.PersonI("$Person".2) = 111..Copy the code

Regarding global indexes, note the following:

1. By default, it is placed in a global variable whose name is the class name, followed by one"I"(saidIndex).
2. By default, the first subscript is the index name; This allows multiple indexes to be stored in the same global without conflict.
3. The second subscript contains the collated data values. In this case, the sorting function is not applied, because this is an index of numeric data.
4. The third subscript contains the block number; To improve efficiency, bitmap indexes are divided into a series of bitstrings, each containing approximately the number of bits in the table64000Row information. Each of these bit strings is called a block.
5. The node contains a bit string.

Also note that because the table has a bitmap index, an extent index is automatically maintained. The partition index is stored in index GLOBAL and uses the class name prefixed with the “$” character as its first subscript.

Direct access to bitmap indexes

The following example uses the class area index to count the total number of object instances (rows) stored. Note that it uses $ORDER to iterate over the indexed blocks (each block contains about 64,000 lines of information) :

ClassMethod Count1(a) As %Integer
{
    New total,chunk,data
    Set total = 0
    
    Set chunk = $Order(^Sample.PersonI("$Person".""),1,data)
    While (chunk '= "") { Set total = total + $bitcount(data,1) Set chunk = $Order(^Sample.PersonI("$Person",chunk),1,data) } Quit total }Copy the code

DHC-APP>w ##class(PHA.TEST.SQL).Count1()
208
Copy the code