A Caché of Tips:: CREATE.INDEX
Mike Moulckers
Since Caché MultiValue Query Language (CMQL) utilizes the standard Caché SQL engine, a sophisticated set of indexing capabilities can be used. Metadata for indexes on Multivalue data can be managed using the CREATE.INDEX, DELETE.INDEX, and LIST.INDEX commands, and with the MVBasic INDICES function.
The CREATE.INDEX command creates an
index on a file.
Command syntax: CREATE.INDEX filename indexfield [indexfield2 [...]]
You can use indexfield parameters to specify one field or more than one field to index; If you specify multiple fields, a separate index is created on each.
CREATE.INDEX creates a class with a property named ItemId, which describes the item id of the original MultiValue file. You can change the names of other properties in the generated class (assuming that you also change the name anywhere that the property is referenced by other properties, indices, or methods) but the ItemId property must be named ItemId. Otherwise subsequent CREATE.INDEX commands will fail and leave the class in an uncompileable state.
All properties created using CREATE.INDEX contain an MVAUTO parameter which is assigned the “I” letter code. MVAUTO prevents PROTOCLASS or CREATE.INDEX from overwriting any manual modifications that you may have made to a class. For more detail on the MVAUTO parameter, please see the PROTOCLASS documentation.
If the specified indexfield is composed entirely of virtual fields, CREATE.INDEX automatically creates an additional property if needed called dummyAttribute, so that the resulting class has at least one real (storage) attribute. If CREATE.INDEX adds real attributes later, dummyAttribute is automatically deleted. If you manually add real attributes later, you must manually delete dummyAttribute and associated storage.
Creating an index creates both a class property and an index name entry in the associated Caché class. The property is named by converting indexfield to a case-sensitive name by omitting punctuation and using an uppercase letter to indicate where the punctuation was removed. For example, the indexfield START.DATE would correspond to the property name StartDate. The index name is created by appending the string “index” to the indexfield property name, then truncating the resulting name at 31 characters. Therefore, an indexfield name must be unique within the first 26 characters.
When creating an index for a MultiValue file, it is strongly recommended that every unique attribute have a corresponding class property. Any field/attribute that does not have a corresponding class property will become empty when the %Save() method is invoked. (Attributes that are mere synonyms do not require a corresponding class property.)
You do not need to use CREATE.INDEX to add indices to a class. You can define indexes by either using CREATE.INDEX or by manually defining indexes by editing the class definition for the file using Caché Studio. But if you intend to use the indexes with MVBasic statements and functions (for example INDICES(), SELECTINDEX, BSCAN, OPENINDEX, and SELECT with the ATKEY clause) you must create them using the format that CREATE.INDEX generates. CREATE.INDEX does not create a new index if there is already an index with the same MVNAME class property attribute as the specified DICT item. If you manually define indexes in a class, it is preferable to avoid the use of CREATE.INDEX and DELETE.INDEX on that file to avoid any unintended deletions.
If you create an index on a file that contains data, you must populate the index using BUILD.INDEX. If you create an index on a file that is currently open, you must close and reopen the file for MultiValue to be aware of the index. This close/reopen is necessary to activate operations such as automatically updating the index when you perform a WRITE. These index activation steps are required for Caché MultiValue, UniVerse emulation, and jBASE emulation.
For a quick demonstration, I create a couple of indexes on
the MVDEMO.PERSON file. In order to force CREATE.INDEX to create the
class definition, I created my MVDEMO.PERSON file manually instead of via the
Studio Import. Your results will differ from mine since the system generates the values.
MV:CREATE.INDEX MVDEMO.PERSON Name Age
[866] Index(es) created.
This results in the following class definition being created. Note that the ItemId property is created as expected and that my indexed fields, Name and Age, have their MVAUTO parameter set to “I”.
Class MVFILE.MVDEMO2EPERSON Extends (%Persistent, %MV.Adaptor, %XML.Adaptor) [ ClassType = persistent, Inheritance = right, ProcedureBlock, SqlRowIdPrivate ]
{
Parameter MVAUTOLOCK = 0;
Parameter MVCLEARDICT = 0;
Parameter MVCREATE As BOOLEAN = 0;
/// Do not modify MVFILENAME if the file already exists.
Parameter MVFILENAME As STRING = "MVDEMO.PERSON";
Parameter MVREPOPULATE = 0;
Property Age As %String(COLLATION = "MVR", MVATTRIBUTE = 1, MVAUTO = "I", MVNAME = "Age", MVPROJECTED = 0, MVTYPE = "D");
Property ItemId As %String;
Property Name As %String(COLLATION = "SqlString(150)", MVATTRIBUTE = 3, MVAUTO = "I", MVNAME = "Name", MVPROJECTED = 0, MVTYPE = "D");
Index indexAge On Age As MVR [ Data = Age ];
Index indexItemId On ItemId [ IdKey, PrimaryKey ];
Index indexName On Name As SqlString(150) [ Data = Name ];
}
Executing CMQL queries before building the indices could result in erroneous results:
MV:LIST MVDEMO.PERSON Name Age WITH Name LIKE F...
[401] No items present.
MV:LIST MVDEMO.PERSON Name Age WITH Age < 65
[401] No items present.
Building the indices:
MV:BUILD.INDEX MVDEMO.PERSON Name Age
Building Indexes...
[867] Index Build Completed.
Results in a new multidimensional array being created for the two indexes:
^I.MVDEMO.PERSON("indexAge"," 6218",2) = 18
^I.MVDEMO.PERSON("indexAge"," 6242",1) = 42
^I.MVDEMO.PERSON("indexAge"," 6243",4) = 43
^I.MVDEMO.PERSON("indexAge"," 6247",3) = 47
^I.MVDEMO.PERSON("indexAge"," 6265",5) = 65
^I.MVDEMO.PERSON("indexAge"," 6268",9) = 68
^I.MVDEMO.PERSON("indexAge"," 6281",7) = 81
^I.MVDEMO.PERSON("indexAge"," 6281",8) = 81
^I.MVDEMO.PERSON("indexAge"," 6288",6) = 88
^I.MVDEMO.PERSON("indexAge"," 6288",10) = 88
^I.MVDEMO.PERSON("indexAge"," 6288",11) = 88
^I.MVDEMO.PERSON("indexName"," Faust,Phyllis Z.",5) = "Faust,Phyllis Z."
^I.MVDEMO.PERSON("indexName"," Frost,Sam M.",9) = "Frost,Sam M."
^I.MVDEMO.PERSON("indexName"," Idle,Jim",1) = "Idle,Jim"
^I.MVDEMO.PERSON("indexName"," Love,Alice W.",11) = "Love,Alice W."
^I.MVDEMO.PERSON("indexName"," Macrakis,Agnes S.",2) = "Macrakis,Agnes S."
^I.MVDEMO.PERSON("indexName"," Schulte,Joshua H.",3) = "Schulte,Joshua H."
^I.MVDEMO.PERSON("indexName"," Uhles,Belinda L.",4) = "Uhles,Belinda L."
^I.MVDEMO.PERSON("indexName"," Xavier,Orson M.",8) = "Xavier,Orson M."
^I.MVDEMO.PERSON("indexName"," Xiang,Norbert G.",10) = "Xiang,Norbert G."
^I.MVDEMO.PERSON("indexName"," Ximines,Nellie Q.",7) = "Ximines,Nellie Q."
^I.MVDEMO.PERSON("indexName"," Yoders,Elvira S.",6) = "Yoders,Elvira S."
And the CMQL queries return the expected results:
MV:LIST MVDEMO.PERSON Name Age WITH Name LIKE F...
@ID....... Name..................... Age.......
5 Faust,Phyllis Z. 65
9 Frost,Sam M. 68
2 Items listed.
MV:LIST MVDEMO.PERSON Name Age WITH Age < 65
@ID....... Name..................... Age.......
1 Idle,Jim 42
2 Macrakis,Agnes S. 18
3 Schulte,Joshua H. 47
4 Uhles,Belinda L. 43
4 Items listed.
Hint: Use the “(Z” query clause with your CMQL query to display the CMQL Query Execution Plan. This will help you to understand how the query is translated into SQL and where your indexes are leveraged.