Switching to SQL and Oracle Text for Case Management Filters and Reports

A Switching to SQL and Oracle Text for Case Management Filters and Reports

This appendix describes how to use SQL and Oracle Text for Case Management filter and report execution.

Note:

This feature is applicable only for EDQ 12.2.1.4.1 release.

From this release, you can use pure SQL in conjunction with Oracle Text and JSON indices for Case Management filter and report execution. Note that switching from Lucene to Oracle Text is a considerable change and may require tuning to improve search performance. Oracle recommends that you test all aspects of search behavior and performance before you configure your production systems to use Oracle Text.

Follow the below procedure to enable SQL filtering and disable Lucene.

Note:

To use this option, the EDQ repository database must be running Oracle database 19.10 or later.

It includes the following sections:

A.1 Working of Case Management Filters

Case management filters are mapped to SQL searches on the dn_case and supporting tables. Filters on the case key, case description and comment text are mapped to Oracle Text searches. Filters on source data are mapped to Oracle JSON Text searches on a new column in the supplementary data table which contains JSON encoded source data. All other fields do not support free text searches and are mapped to simple SQL predicates.

A.2 Understanding Oracle Text Expressions

There are very few differences between Lucene filter expressions and Oracle Text Context Grammar. For more guidance on the Oracle Text search syntax, refer to The CONTEXT Grammar documentation.

The existing filters use Lucene search syntax and you have to modify them to work correctly with Oracle Text. Asterisk (“*” - wild card) characters in filters are replaced with % automatically.

Refer to Key differences in Search Functionality between Lucene and Oracle Text in EDQ Case Management for more information.

A.3 Using Oracle Text Options

Indexes created for searches using Oracle Text have a large number of configuration options. The best options to use for any installation greatly depends on the data and typical search patterns.

For example, an index can be enhanced to improve the performance of prefix searches such as A%. The length of the prefix can also be configured. Improving prefix search performance entails an additional cost in index maintenance and storage size. These options can be set on the EDQ repository database housing the EDQCONFIG schema.

EDQ provides a script that sets these options for typical use cases, for example - to include a prefix optimization for prefixes up to 3 characters long when searching on source attributes, but this may be tuned for individual requirements. For more information on this, refer to Oracle Text Indexing Elements.

The performance of comment searches depends on the volume of comment data. You can do one of the following to optimize search performance:

Uncheck the Comment option when using Quick Search.
Add the following indexes:
create index idx_comment_cid on dn_casecomment(case_id)

create index idx_comment_del on dn_casecomment(deleted_flag)

A.4 Updating Schema

To enable Oracle Text functionality, you must grant the CTXAPP role for the EDQ configuration schema user.

You must also grant the CREATE JOB system privilege to the user. Use the following SQL commands:

GRANT "CTXAPP" TO "USERNAME";
GRANT CREATE JOB TO "USERNAME";

where USERNAME is the EDQ configuration schema user name.

A.5 Creating Helper Index and Column

Case and alert permissions are checked directly in the SQL filters.

You have to create a new index on the case table to improve performance for these checks:

CREATE INDEX idx_dn_case_permission ON dn_case(permission);

Source attribute searches are performed using a new JSON data column in the supplementary data table.

Use the following command to add the new column:

ALTER TABLE dn_supplementarydata ADD json BLOB CONSTRAINT jcheck CHECK (json IS JSON);

A.6 Creating Text Indices

The following SQL script creates the required Oracle Text and JSON indexes:

BEGIN
  CTX_DDL.create_preference('dn_textpref', 'BASIC_LEXER');
  CTX_DDL.create_stoplist('dn_textstop', 'BASIC_STOPLIST');
  CTX_DDL.create_preference('dn_wordlist', 'BASIC_WORDLIST'); 
  CTX_DDL.set_attribute('dn_wordlist', 'PREFIX_INDEX', 'TRUE');
  CTX_DDL.set_attribute('dn_wordlist', 'PREFIX_MAX_LENGTH', '3');
END;
/

CREATE INDEX dn_case_key_text ON dn_case (key_label)           
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS('sync (every "freq=secondly;interval=20") lexer dn_textpref stoplist dn_textstop wordlist dn_wordlist')
/

CREATE INDEX dn_case_desc_text ON dn_case (description)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS('sync (every "freq=secondly;interval=20") lexer dn_textpref stoplist dn_textstop wordlist dn_wordlist')
/

CREATE INDEX dn_casecomment_text ON dn_casecomment (case_comment)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS('sync (every "freq=secondly;interval=20") lexer dn_textpref stoplist dn_textstop wordlist dn_wordlist')
/

CREATE SEARCH INDEX dn_supp_json ON dn_supplementarydata (json) FOR JSON  PARAMETERS('sync (every "freq=secondly;interval=20") wordlist dn_wordlist')
/

The indexes are updated asynchronously every 20 seconds. By default, prefix searches are optimized for prefix lengths up to 3 characters. No special language analysis is enabled.

You can find the script in the cmsql directory of the EDQ Home directory after updating to this release.

A.7 Populating JSON

Use the tool sdjson.jar to populate the supplementary data JSON column.

Execute the following command to run the tool:

$ java -jar sdjson.jar oracle:#service@HOST:PORT/USER/PW

Tests have shown that the overall time for the conversion and indexing is much less if the JSON index is created before the population step.

A.8 Enabling SQL and Oracle Text Usage for Filtering and Reports

A new setting cm.filter.sql in director.properties file controls the usage of SQL or Lucene for filters and reports.

cm.filter.sql = off

Lucene is used for all filters and reports. This is the default for compatibility with earlier versions.

cm.filter.sql = on

SQL and Oracle Text is used for all filters and reports. Lucene indexing is disabled.

cm.filter.sql = optional

A Use SQL option is available in the Case Management UI. Lucene indexing is enabled. You can use this setting to compare results and timing between SQL and Lucene searches.

Note:

Use this option only for testing.

A.9 Additional Considerations

The extended attribute (custom flags) columns in the case table do not have database indexes by default. If searches on extended attributes are common without other search filters, it is necessary to create additional indices. This depends on the individual requirements.