How to Resolve ORA-02327: cannot create index on expression with datatype LOB

ORA-02327

When we tried to create an index on a Character Large Object (CLOB) or Binary Large Object (BLOB) column, we got ORA-02327. Let's see the case.

SQL> create table t1 (c1 clob);

Table created.

SQL> create unique index x1 on t1 (c1);
create unique index x1 on t1 (c1)
                              *
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

ORA-02327 means that we cannot create an unique index on incomparable datatype like CLOB and BLOB. Comparison operators like UNION, IN, DISTINCT, etc cannot be used on LOB columns.

Large Objects are incomparable indeed. Another proof is that, if you performed comparison operations between Large Objects or group-by functions, it throws ORA-00932: inconsistent datatypes: expected - got CLOB.

Solutions

Since we cannot create an unique index on CLOB directly, so we create it indirectly, which means we create a function-based index instead.

Convert to String

We convert CLOB into VARCHAR2 for the first 1000 characters.

SQL> create unique index x1 on t1 (cast(c1 as varchar2(1000)));

Index created.

It's simple and understandable. But the drawback is that, the new index cannot uniquely identify strings which are different after 1000 characters, so it's an OK but not ideal solution.

DBMS_CRYPTO.HASH

A better solution is to hash CLOB into a fixed-length checksum to form a function-based index, which can differ one value from the other.

Here we use DBMS_CRYPTO.HASH to hash CLOB values into strings which have very little chances to violate uniqueness if the CLOB values are distinct.

SQL> create unique index x2 on t1 (DBMS_CRYPTO.HASH(c1, 'HASH_SH256'));

Index created.

In this case, we use HASH_SH256 algorithm to hash a CLOB, which produces a 256-bit hash.

The algorithms that can be used are listed below:

• HASH_MD4
• HASH_MD5
• HASH_SH1
• HASH_SH256
• HASH_SH384
• HASH_SH512

Since the value collision among hash strings has greatly reduced, it's a more ideal solution for keeping uniqueness. However, what hash functions do is to honor, not guarantee the uniqueness of the column. Maybe one in a trillion, you'll see an exception.