How to set up a declarative index

Use a declarative index — the rows of database tables that are maintained by Declare Index rules — to speed access to instances that have specific property values, especially embedded properties that can't be exposed. This is an alternative to the approach used by the Property Optimization tool.

After you implement a declarative index, reports can use the indexed property value as a selection criteria. For example, if an array of embedded Universal Product Code (UPC) codes in a work item are indexed, you can report on all work items containing a specific UPC code. Reference the index class on the Contents and Join tabs of the List View form or Summary View form.

Working together, PRPC developers and a database administrator (DBA) can define an Index- class and a Declare Index rule to improve access to data not in an exposed column of a database table.

1. Identify the property or properties on the source object that are to make up the index instance.
2. Create a concrete class derived from the Index- base class that will contain the new index instances. For concrete classes derived from the Index- base class, the key consists of three properties pxInsIndexedKey, pxIndexCount, and pxIndexPurpose, in that order.
3. Determine which database table will hold the new indexes. Create the database table if necessary and associate the table with the class through a Database Table instance. The three key properties must be exposed columns in the table.
4. Define Single Value properties in the new index class to hold the values of the source instance properties. Confirm that all these properties are exposed columns in the database, or modify the database schema as necessary.
5. Choose a purpose name for the Declare Index rule.
6. Create a Declare Index rule, recording the source of each property value and its corresponding index property name.

Index processing starts as soon as you save the Declare Index rule.

Notes

As a best practice, do not create declarative indexes for top-level Single Value properties.

	declaration, Index base class
	About Declare Index rules About the Property Optimization tool How to detect when the number of declarative indexes exceed a specified threshold
	Standard classes derived from the Index- base class

SysAdmin category