IBM FileNet P8, Version 5.2.1            

Index tuning: Case-insensitive search

When Force Case-Insensitive Search is enabled on an object store, Content Platform Engine applies the LOWER() function to String properties. A database index that uses the LOWER() function might improve query performance.

The following database SQL shows how Content Platform Engine applies the LOWER()in the WHERE clause:
WHERE LOWER(uxy_Property) = LOWER(?)

Because of the presence of the LOWER() function, the query execution plan does not use an index on uxy_Property. The solution is to create an index that uses the LOWER() function. The steps for doing so are unique to the database vendor. For more information, see Create database indexes for class properties.

The original index on uxy_Property can be dropped.

To support searches for subfolders by using a parent condition, create composite index on the Container table with the parent_container_id and name columns. For example:
CREATE INDEX Idx_Container ON Container (parent_container_id, LOWER(name))
In addition, you can also add a composite index on the Relationship table with the tail_id and name columns. For example:
CREATE INDEX Idx_Relationship ON Relationship (tail_id, LOWER(name))
Note: Do not drop the original system indexes for any tables. The system indexes contain the same columns as the added indexes, but they do not use the LOWER() function.


Last updated: March 2016
p8ppt297.htm

© Copyright IBM Corporation 2016.