IBM FileNet P8, Version 5.2.1            

Index tuning: List property search

A database index might improve the performance of a query that searches for a list property value.

For example, the following table shows a search for a list property value.
Table 1. Example query for a list property value
Content Platform Engine SQL Corresponding database SQL (continuable query)
SELECT Id 
FROM Document 
WHERE 'value1' 
IN ListProperty1 
SELECT object_id, object_class_id, security_id, ... 
FROM DocVersion 
WHERE T0.home_id IS NULL 
AND EXISTS 
(SELECT 1 
FROM ListOfString L 
WHERE L.parent_id = object_id 
AND L.parent_prop_id = ? 
AND LOWER(L.element_value) = LOWER(?))

By default, tables in the Content Platform Engine of the form object store of the form "ListOf<Table>" are not indexed for user searches. (But a system index exists that supports retrievals of values for a specific object.) The <Table> can designate String, Boolean, Integer32, Float64, Datetime, or Id.

If there are many rows in the ListOf<Table> , create an index with the element_value column in the first slot:
CREATE INDEX I_LOS 
ON ListOf<Table> (element_value, parent_prop_id, parent_id) 
If "Force Case-Insensitive Search" is enabled, so that the LOWER() function is applied for a ListOfString search, create the index in the following manner:
CREATE INDEX I_LOS 
ON ListOfString (lower(element_value), parent_prop_id, parent_id) 


Last updated: March 2016
p8ppt296.htm

© Copyright IBM Corporation 2016.