IBM FileNet P8, Version 5.2.1            

Index tuning: Selective search conditions

Sometimes search conditions require too much database row filtering. You can prevent excessive row filtering with a composite index.

Tip: For information about row filtering, see Row filtering.
The following query might be an example of when search conditions require too much filtering of database rows:
SELECT Id 
FROM Document 
WHERE Property1 = 'value1' 
AND Property2 = 'value2' 
ORDER BY Property2

Assume that an index is already present on Property1, and that the cardinality of Property1 is greater than Property2. (Cardinality is the number of unique values for a property.) As the index on Property1 is traversed, many rows might have to be filtered.

To eliminate filtering, request the database administrator to create a composite database index as shown in the following example.
Table 1. Composite database examples
Query type Example
A query that returns relatively few results
CREATE INDEX I_Prop1_Prop2 
ON DocVersion (uxy_Property1, uxy_Property2)
A query that returns many results
CREATE INDEX I_Prop1_Prop2 
ON DocVersion (uxy_Property1, uxy_Property2, object_id)

Use the administration console to drop the database index on Property1, if present.

This solution is for a relatively simple query. More complex queries usually have several other search conditions that apply to several different columns. Some of these search conditions might be only slightly selective; that is, they might filter relatively few rows. Adding only the more selective columns to a database index is typically best for the following reason: adding more columns to a composite index is costly in terms of both storage and object creation costs.



Last updated: March 2016
p8ppt294.htm

© Copyright IBM Corporation 2016.