IBM Support

Extraneous indexes using rawtohex() degrade Content Engine performance.

Troubleshooting


Problem

Creating Oracle indexes on any column using rawtohex() is never necessary for the Content Engine (any version), and having them present only reduces ingestion, and update and delete performance. At SQL execution time, the CE binds ID data using setBytes() so that Oracle is passed the same datatype as the underlying column. The default indexes created by the CE will be used by the CE for any eligible ID predicate without any need for an index to include rawtohex(). The reason such indexes may have been erroneously added is explained here, as well as the proper method to issue direct SQL.

Symptom

Slow ingestion performance.

Cause

Creation of additional indexes with rawtohex() cause additional work during updates, deletes and inserts, having to maintain the index on the ID datatype column.

Indexes with rawtohex() are created sometimes by sites running queries such as the following, from Oracle SQLPlus, SQL Developer and similar tools from other vendors (this is just an example):

SELECT object_class_id, creator, create_date
FROM DocVersion WHERE object_id = '230947CA18154C4B980627557389779E'

The explain plan for that query will show a filter predicate on rawtohex("object_id"), and a full table scan (FTS). The FTS is because the rawtohex() predicate is a mismatch to the CE index on just ID.

Creating an index on rawtohex(ID) to obtain fast performance for that query from a SQL tool is not the solution because it degrades CE performance.

The solution is to issue the query like this when using SQL tools:

SELECT object_id, creator, create_date
FROM DocVersion WHERE object_id = hextoraw('230947CA18154C4B980627557389779E')

Background:
CE ID datatype values are stored in columns of type raw(16) and cannot be simply be used in a query from SQLPlus and related tools because these tools consider the ID predicates as string data. The passing of the ID values as strings by the query tools results in a datatype mismatch to an index, and the query plan will not use the index..

Any index with rawtohex() on an ID datatype column is never used by the CE when the column is used in a predicate. A CE query results in a FTS if it is the only index present on the column.

If there are two indexes present, one with rawtohex() and the other without, the index with rawtohex() is ignored by the CE. The CE uses the index on any ID column without rawtohex() because the CE at runtime binds ID data using setBytes() so that Oracle receives the same datatype as the underlying column.

Environment

Oracle only. Any platform, any version.

Diagnosing The Problem

Extract the DDL for the Object Store schema and look for any index with rawtohex() added to a column.

Resolving The Problem

Remove the indexes with rawtohex() in them, or recreate the index without rawtohex() if one is not present, and issue any direct SQL query (using SQL Tools) with hextoraw() around the ID datatype like this:

SELECT object_class_id, creator, create_date
FROM DocVersion WHERE object_id = hextoraw('230947CA18154C4B980627557389779E')

Any custom applications that do not use the CE API, but instead use direct SQL (for example a Java app using JDBC) and that also do not use binding, but instead use literal ID predicates in the query, must use hextoraw() around the value if an eligible index is expected to be used.

[{"Product":{"code":"SSNVNV","label":"FileNet Content Manager"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Content Engine","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"5.2.1;5.2.0;5.1.0;5.0;4.5.1;4.0.1;3.5.2","Edition":"All Editions","Line of Business":{"code":"LOB45","label":"Automation"}},{"Product":{"code":"SSGLW6","label":"IBM Content Foundation"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Content Engine","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
17 June 2018

UID

swg21469817