Create database indexes for class properties

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

NOTE  When a class property is set or unset for single indexing it is automatically set or unset in all subclasses.

To create database indexes for class properties

  1. From the Enterprise Manager tree view, 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. On the Properties dialog, click Set/Remove.
  6. On the Set/Remove Indexing dialog, select the Set option and check Single Indexed.
  7. Click OK to close all dialog boxes and apply the changes.

When selecting a property to index, the objectstore search must be case-sensitive, or the index does not create correctly. You must create additional indexes in Oracle and DB2 to avoid full table scans.

Oracle

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
COMPATIBLE=8.1.0 or higher

To create function-based indexes, use the following example:

CREATE INDEX lc_lname ON <tablename>(lower(lname));

NOTE  For the Enterprise Edition only: Set QUERY_REWRITE_INTEGRITY=TRUSTED during the functional index build. You can set the QUERY_REWRITE_ENABLED=TRUE in the init.ora. Then, when creating the functional index, followed by the analyze statement on the index and table, the functional index is used.

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>)

Substitute the place-holders, in brackets above, with the necessary values:

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 P8 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.

  1. If case-insensitive search is enabled, the P8 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 = "foo" was issued in a case-insensitive object store and the DB was Oracle or DB2, the actual DB query the Content Engine emits is similar to select id from docversion where lower(u55_title) = lower("foo").

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, as 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> OFF
ALTER TABLE <table> ADD COLUMN lc_<lname> GENERATED ALWAYS AS (lower(<lname>))
SET INTEGRITY FOR <table> IMMEDIATE CHECKED FORCE GENERATED
CREATE INDEX idx_lc_lname ON <table> (lc_<lname>)

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