How to enable case insensitive search on case data for Oracle & PostgreSQL

Searching on case data is case sensitive when using an Oracle database, because Oracle has a case sensitive collation by default. SQL Server on the other hand is case insensitive by default. To overcome these differences, we have built case insensitive search, which enables searching case insensitive on case data regardless of the underlying database.

When running our own performance tests we discovered that under specific circumstances case insensitive search on case data can decrease performance on an Oracle database. For this reason we have disabled this feature by default on Oracle.

This article described how to enable case insensitive search on case data on an Oracle database.

Enabling case insensitive search on the runtime

For the runtime, we need to set a specific property and run database scripts to change indexes on a table.

Process engine/Process-sql-store

Runtime property

Add the following property to application.properties:

blueriq.process-sql-store.oracle.case-insensitive-search-enabled=true

Database script

Run the SQL script found in the release zip at: blueriq-component-process-sql-store\dbscripts\features\case-insensitive\oracle_enable_feature_case_insensitive.sql

Customerdata

For the customerdata only a property needs to be added to blueriq-customerdata-odata-service-v1.yml : 

blueriq:
  customerdata:
    oracle:
      case-insensitive-search-enabled: true

Disabling the feature

Once enabled, you can disable the feature by reversing the steps above, that is setting the properties to false (or remove them) and run the oracle_disable_feature_case_insensitive.sql script.

More information can be found at : 

Process SQL store component
Release 13.6.1 Upgrade Instructions
Blueriq 13.6.1 Release Notes