IBM FileNet P8, Version 5.2.1            

Index tuning: Property range search (continuable query)

For a continable query that performs a property range search, a composite database index might improve query performance.

For example, the following query performs a property range search:
SELECT Id From Document 
WHERE Property1 > 'aValue' 
AND Property1 < 'zValue' 
If the query returns many rows, request the database administrator to create a 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. The database query optimizer is more likely to use this composite index than a simple index on Property1.

Also, add an ORDER BY clause to the query as shown in the following table.
Table 1. Example query
Content Platform Engine SQL Corresponding database SQL (continuable query)
SELECT Id 
FROM Document 
WHERE Property1 > 'aValue' 
AND Property1 < 'zValue' 
ORDER BY Property1
SELECT object_id, object_class_id, security_id, ... 
FROM DocVersion 
WHERE home_id Is Null 
AND uxy_Property1 > ? 
AND uxy_Property1 < ? 
ORDER BY uxy_Property1, object_id


Last updated: March 2016
p8ppt293.htm

© Copyright IBM Corporation 2016.