Create database indexes for class properties

You can create a database index for any class property except a system property. Database indexes, also known as single indexes, are stored in the object store database. For properties that users frequently search, single indexes reduce the time required for processing queries on this property. Creating a single index for a property increases the time required to add documents to an object store.

When the single indexing option is selected or cleared for a class property, it is automatically selected or cleared for all subclasses of the class.

To create database indexes for class properties

  1. In Enterprise Manager, right-click on the class containing the properties you want to index.
  2. Select Properties.
  3. Select the Property Definitions tab.
  4. Select the property you want to index and click Edit.
  5. In the Properties window, click Set/Remove.
  6. In the Set/Remove Indexing window, select the Set option and check Single Indexed.
  7. Click OK to close all windows and apply the changes.

When you select a property to index, the object store search must be case-sensitive, or the index does not create correctly.

You must create an index for a property in Oracle and DB2 to avoid full table scans when performing searches based on that property.

Oracle

You can create function-based indexes. Use the following syntax:

CREATE INDEX idx_name ON table_name(lower(column_name));

where::

For example, the following command creates an index that you can use for case-insensitive searches on a column called lname:

CREATE INDEX lc_lname ON DocVersion(lower(lname));

DB2
Use the following example to create additional indexes in DB2:

DB2 SET INTEGRITY FOR table_name OFF
DB2 ALTER TABLE table_name ADD COLUMN gen_col_name GENERATED ALWAYS AS (lower(orig_column_name))
DB2 SET INTEGRITY FOR table_name IMMEDIATE CHECKED FORCE GENERATED
DB2 CREATE INDEX idx_gen_name ON table_name (gen_col_name)

where::

For example:

DB2 set integrity for DocVersion OFF
DB2 ALTER TABLE DocVersion ADD COLUMN genDocTitle GENERATED ALWAYS AS (lower(u2e_documenttitle))
DB2 set integrity for DocVersion immediate checked force generated
DB2 create index I_genDocTitle on DocVersion (genDocTitle)

Steps to reproduce the field

  1. At the object store level, make the search case insensitive search for all non-system string metadata properties.

    This setting does not apply to content-based search. In addition, create indexes by selecting properties that optimize potential searches.

    If case-insensitive searching is enabled, Content Engine changes the SQL query sent to DB2, SQL or ORACLE by adding SQL lower() function calls around the property name and the text to search.

    For example, if the ad hoc query select Id from document where title = "title" is issued in a case-insensitive object store and the database is Oracle or DB2, the actual DB query that Content Engine submits is similar to select id from docversion where lower(u55_title) = lower("title").

    However, in both Oracle and DB2, if the administrator specifies building an index on u55_title, the index is not used during the query execution, because the index is case-sensitive.

Efficient case-sensitive RDBMS searching requires different indexing on Oracle and DB2:

  1. In Oracle, create a function-based index for each applicable non-system string column. For example:

CREATE INDEX docv.lc_lname ON table lower(lname);

  1. In DB2, create a generated column and index. For example:

SET INTEGRITY FOR table_name OFF
ALTER TABLE table_name ADD COLUMN lc_lname GENERATED ALWAYS AS (lower(lname))
SET INTEGRITY FOR table_name IMMEDIATE CHECKED FORCE GENERATED
CREATE INDEX idx_lc_lname ON table_name (lc_lname)

After these are created, the database uses the appropriate index automatically.