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
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::
DocVersion
for documents, Generic
for custom objects, and Container
for folders.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::
DocVersion
for documents, Generic
for custom objects, and Container
for folders.gen_col_name
is a new name to be given for the generated column created by running the SQL commands.orig_column_name
is the original case-sensitive column in the table.idx_gen_name
is the index name to be given for the index created on the generated column.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
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:
CREATE INDEX docv.lc_lname ON table lower(lname);
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)
DocVersion
for documents, Generic
for custom objects, and Container
for folders.lname
is the original case-sensitive column in the table.After these are created, the database uses the appropriate index automatically.