Consider creating the following composite-properties index on the following database tables to improve Content Platform Engine performance during multi-valued property searches.
Database table name | Database column name | FileNet® P8 property name |
---|---|---|
ListOfBoolean | element_value, parent_prop_id, and parent_id | Property Name |
Database table name | Database column name | FileNet P8 property name |
---|---|---|
ListOfInteger32 | element_value, parent_prop_id, and parent_id | Property Name |
Database table name | Database column name | FileNet P8 property name |
---|---|---|
ListOfFloat64 | element_value, parent_prop_id, and parent_id | Property Name |
Database table name | Database column name | FileNet P8 property name |
---|---|---|
ListOfDatetime | element_value, parent_prop_id, and parent_id | Property Name |
Database table name | Database column name | FileNet P8 property name |
---|---|---|
ListOfId | element_value, parent_prop_id, and parent_id | Property Name |
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
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).
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).
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.
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. |
For more information about not case-sensitive searches, see the Indexing for IBM FileNet P8 Content Engine Searches technote (white paper).