IBM FileNet P8, Version 5.2.1            

Index tuning: Simple property search with an ORDER BY clause

For a query that performs a simple property search and that also has an ORDER BY clause, 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' 
ORDER BY Property2
SELECT object_id, object_class_id, security_id, ... 
FROM DocVersion 
WHERE home_id Is Null And uxy_Property1 = ? 
ORDER BY uxy_Property2, object_id
If the query returns many rows, request the database administrator to create a composite database index as shown in the following example:
Table 2. Example SQL for creating a composite database index
Query type Database index
Continuable query
CREATE INDEX I_Prop1_Prop2_Id 
ON DocVersion (uxy_Property1, uxy_Property2, object_id)
Non-continuable query
CREATE INDEX I_Prop1_Prop2_Id 
ON DocVersion (uxy_Property1, uxy_Property2)
Tip: For information about the uxy_ prefix for uxy_Property1 and uxy_Property2, see Database SQL.

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

If the query does not return many rows, a database index is not required because the sort cost is small.



Last updated: October 2015
p8ppt292.htm

© Copyright IBM Corporation 2015.