Improving multi-valued property searches

Consider creating the following composite-properties index on the following database tables to improve Content Platform Engine performance during multi-valued property searches.

Table 1. Composite-properties index for the ListOfBoolean database table
Database table name Database column name FileNet® P8 property name
ListOfBoolean element_value, parent_prop_id, and parent_id Property Name
Table 2. Composite-properties index for the ListOfInteger32 database table
Database table name Database column name FileNet P8 property name
ListOfInteger32 element_value, parent_prop_id, and parent_id Property Name
Table 3. Composite-properties index for the ListOfFloat64 database table
Database table name Database column name FileNet P8 property name
ListOfFloat64 element_value, parent_prop_id, and parent_id Property Name
Table 4. Composite-properties index for the Datetime database table
Database table name Database column name FileNet P8 property name
ListOfDatetime element_value, parent_prop_id, and parent_id Property Name
Table 5. Composite-properties index for the ListOfId database table
Database table name Database column name FileNet P8 property name
ListOfId element_value, parent_prop_id, and parent_id Property Name
Table 6. Composite-properties index for the ListOfString database table
Database table name Database column name FileNet P8 property name
ListOfString element_value, parent_prop_id, and parent_id Property Name

Create these indexes only if the database table has at least several hundred rows and has enough distinct values relative to the number of rows in the table. (You can determine the cardinality by reviewing the table statistics or by using a group by query, for example select count(*), element_value from ListOfString group by element_value order by 1

ListOfString index
Use the following CREATE INDEX statement to create an index that improves Content Platform Engine performance during multi-valuedsearches:
create index I_MV on ListOfString (element_value, parent_prop_id, parent_id)

If the object store is not case-sensitive, create the index using the LOWER function on (element_value).

For more information about not case-sensitive searches, see the Indexing for IBM FileNet P8 Content Engine Searches technote (white paper).

ListOfString index for searches that begin with %wildcard% values
Use the following CREATE INDEX statement to create an index that improves Content Platform Engine performance during multi-valued property searches when an index on (element_value, parent_prop_id, parent_id) cannot be used because the search begins with a wildcard value, such as LIKE %value%: 
create index I_MV2 on ListOfString (parent_id, parent_prop_id, element_value)

If the object store is not case-sensitive, create the index by using the LOWER function on (element_value).

For more information about not case-sensitive searches, see the Indexing for IBM FileNet P8 Content Engine Searches technote (white paper).

Restriction:
  • Each database type has its own limit on index byte size, which can require extra work to create the index for string data types. By default, the ListOfString's element_value column is of character size 4000 (1333 on Oracle), might be longer than the maximum index length for the database.
  • 4000 characters consume 8000 bytes during value insertion on Microsoft SQL Server and 8000 bytes during column creation on DB2®. On an Oracle database, the varchar2 allocation is tripled. As a result, 3999 bytes are consumed for a 1333 character size column.

Use the guidelines in the following table. If the database does not allow the index to be created, reduce the size of the ListOfTable element_value column. However, reducing this value might not work if the values stored in the column are too large. If only a few values are too large, consider changing the data in those columns to allow the index to be created.

Table 7. Guidelines for creating indexes
Database Index MaxBytes Byte size of element_value Index allowed? Notes
Microsoft SQL Server 2012 900 8000 No  
Oracle 11g 75% of block size 3999 Under certain conditions The Oracle 11g block size is also minus processor usage, which is small.
DB2 9 8 KB 8000 Yes 8 KB is one-quarter the table space size. The DB2 8 KB block size is also minus processor usage, which is small.
Note:
For strings on Oracle and DB2, if Force Case Insensitivity is enabled, the index must be created on lower(element_value) along with the other composite columns.

For more information about not case-sensitive searches, see the Indexing for IBM FileNet P8 Content Engine Searches technote (white paper).

Persisting multi-valued properties in their own database table

You can improve the performance of multivalued property queries by assigning the Own Table persistence type to property templates.

Persistence types are assigned to property templates individually, and the Own Table persistence type isolates a multivalued property into its own database table so that only those values that are associated with the multivalued property are stored there. You can store multivalued properties by data type by selecting System Table instead of Own Table when you configure a property template.