IBM FileNet P8, Version 5.2.1            

Index tuning: Simple property search (continuable query)

For a continuable query that performs a simple property search, a composite index might improve query performance.

The following table shows an example of such a query.
Table 1. Example of a simple property search
Content Platform Engine SQL Corresponding database SQL (continuable query)
SELECT Id 
FROM Document 
WHERE Property1 = 'value'
SELECT object_id, object_class_id, security_id, ... 
FROM DocVersion 
WHERE home_id Is Null 
AND uxy_Property1 = ? 
ORDER BY object_id
If the query returns many rows, request the database administrator to create a composite database index as shown in the following example:
CREATE INDEX I_Property1_Id 
ON DocVersion (uxy_Property1, object_id)
Tip: For information about the uxy_ prefix for uxy_Property1, see Database SQL.

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

By scanning the composite index, the query can directly return the rows that satisfy Property1. No sort step is required in the database query execution plan because the rows in the index are also in object_id order. The object_id order is necessary because Content Platform Engine must add the "ORDER BY object_id" clause to the database SQL to make the query continuable.

The composite index is necessary for the following reason: when there are many rows that satisfy the Property1 = 'value' condition, the "ORDER BY object_id" clause adds a significant sort cost to the query. Each returned page of rows must be sorted by object_id. Also, scanning the property1 index requires a slow row ID look-up for each row found. And so a database index on Property1 alone might not result in the inclusion of the index in the query execution plan. Instead, the query issues a table scan and then sorts the table on object_id or scans the primary key on the table.

If the query does not return many rows, the original database index on Property1 is sufficient because the sort cost is small. In this case, an alternative composite index is not best because composite indexes reduce the performance of object creation and add to storage costs.



Last updated: March 2016
p8ppt291.htm

© Copyright IBM Corporation 2016.