Best practice settings for Oracle Case Insensitive in Oracle Database.

Case-insensitive search is one of those features that Oracle users often underestimate — until they migrate from SQL Server or need to meet global application requirements involving mixed-case data. Although Oracle introduced proper support for case-insensitive collation in 12.1, it’s still not as seamless as Microsoft SQL Server, which has had it since the early 2000s.

That said, Oracle’s support team and community have provided solid workarounds and configuration guidance that can make case-insensitive implementations reliable — especially on Exadata Cloud Service (ExaCS) running 19c.

Why Case-Insensitive Is Tricky in Oracle

Unlike MSSQL, Oracle’s default comparison semantics are case-sensitive, meaning 'abc' != 'ABC'. To make searches case-insensitive, developers often rely on functions like UPPER() or LOWER() in predicates — but these break index usage, leading to poor performance on large tables.

Oracle 12.1 and above introduced linguistic sorting and collation-based case-insensitive search, but few customers globally use it in production due to migration complexity, compatibility, and optimizer behavior changes.

When Collation Is Not Used

If your database, schema, or table is not created using a case-insensitive collation, follow the below “recipe” to achieve stable case-insensitive behavior using functional indexes and session-level parameters.

Step 0: Check Your Setup

If you have not explicitly defined NLS_COLLATION at the table or schema level, proceed with this configuration. Otherwise, Oracle will handle case-insensitive logic internally, and these steps are unnecessary.

Step 1: Use Functional Indexes

Convert all normal indexes on VARCHAR2 or CHAR columns into function-based indexes using NLSSORT with BINARY_CI (case-insensitive binary sort).

CREATE INDEX idx_emp_name_ci 
    ON employees (NLSSORT(employee_name, 'NLS_SORT = BINARY_CI'));

This ensures that predicates like WHERE employee_name = 'John' can still use the index efficiently, even if users input 'john' or 'JOHN'.

Step 2: Enable Case-Insensitive Sessions Automatically

Create a logon trigger to set session parameters for the desired users or schemas:

CREATE OR REPLACE NONEDITIONABLE TRIGGER SYS.LOGON_CASESENSITIVE
AFTER LOGON ON DATABASE
BEGIN
   IF UPPER(USER) IN ('SCHEMA1', 'USER1') THEN
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC';
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI';
   END IF;
END;
/
ALTER TRIGGER SYS.LOGON_CASESENSITIVE ENABLE;

This ensures all connections for specified users automatically use case-insensitive comparisons.

Step 3: Tune System Settings (Test First!)

Before applying these parameters at the system level, test them thoroughly at session level to confirm query stability and plan consistency.
Below settings work well on ExaCS with Oracle 19c but may vary based on workload and hardware profile:

ALTER SYSTEM SET query_rewrite_enabled='FALSE' SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET cursor_sharing='FORCE' SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET open_cursors=800 SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET session_cached_cursors=2000 SCOPE=SPFILE CONTAINER=ALL SID='*';
ALTER SYSTEM SET commit_wait='NOWAIT' SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET ddl_lock_timeout=600 SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET "_rowsets_enabled"=FALSE SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET "_fast_full_scan_enabled"=FALSE SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET "_optimizer_cbqt_or_expansion"=OFF SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET parallel_force_local=TRUE SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET "_optimizer_ansi_rearchitecture"=FALSE SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET "_optimizer_multi_table_outerjoin"=FALSE SCOPE=BOTH CONTAINER=ALL SID='*';
ALTER SYSTEM SET "_fix_control"='11814337:0','12618642:0','33649782:1','23223113:1','31143146:ON,34764262:ON'
   SCOPE=BOTH SID='*';

⚠️ Caution:

Apply these only after validation on non-production environments.

Some underscore (“_”) parameters are undocumented and should only be changed under Oracle Support guidance.

Always gather AWR baselines before and after changes to verify improvements.

make changes only on session level if you are satisfied with desired results then apply to system level.

Lessons Learned

From experience and Oracle’s recommendations:

Use collation-based case-insensitivity (BINARY_CI) at the schema or table level in new deployments instead of triggers and functional indexes.
For existing databases, functional indexes + logon trigger approach provides a stable workaround.
Avoid unnecessary system-wide parameter changes without AWR validation.
Always coordinate with Oracle Support for best parameter alignment on ExaCS or Exadata environments.

DBADEEDS