Create function-based index on DBMS_LOB.COMPARE(customFunction(...)) ??

Sebino

Hello,

In 11.2 I found a query doing this:

Select count(1)
from T
where dbms_lob.compare(T.blobColumn, customPkg.encrypt(:1 , '<key>')) = 0
   and otherColumn != :2

which means I'm traversing the whole T minus 1 row (because otherColumn has unique values), taking every blobColumn and comparing it with :1 which is a clob, that gets encrypted and returned as a blob...

This table is constantly growing, and soon this query will take too much time...

Do you think there's a way of indexing these embedded calls DBMS_LOB.COMPARE(customPkg.encrypt()) ??

customPkg.encrypt() is deterministic.

Thanks a lot.

Sebino

Jonathan Lewis

You could use hashing (e.g. dbms_crypto.hash()) to create a hash value for the lob column - either as a function-based index or as a virtual column that you index, then change your code to have two predicate involving your input bind variable, something like:

where
        dbms_crypto.hash(blob column) = dbms_crypt.hash({your value})
and     dbms_Lob.compare(blob column, {your value} = 0

realistically you probably won't have any hash collisions, technically you might which is why you would use the indexed hash value as the first check then the actual value.

You may have to mess around a bit to work out the best way of setting this up (especially in 11.2). And you'll probably need to use substrb() on the hash value before you can index it.

See https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_crypto.htm

I don't think there are any licence implications of using dbms_crypto, but you'd need to check that.

Gathering stats on the table will become expensive if you do this (see https://jonathanlewis.wordpress.com/2014/03/02/auto-sample-size )

Regards

Jonathan Lewis

Jonathan Lewis

If dbms_crypto.hash doesn't accept a NULL input than use a CASE expression to generate the index, something like the following (1 is the value for the symbolic constant HASH_MD4):

create index t1_b1 on t1(case when b1 is not null then substrb(dbms_crypto.hash(b1,1),1,16) end)

The same messy expression will have to be used in your first predicate.

Or:

alter table t1 add hash_md4 generated always as (
        case when b1 is not null then substrb(dbms_crypto.hash(b1,1),1,16) end
) virtual
;

create index t1_b1 on t1(hash_md4);

Regards

Jonathan Lewis

Jonathan Lewis

You could use hashing (e.g. dbms_crypto.hash()) to create a hash value for the lob column - either as a function-based index or as a virtual column that you index, then change your code to have two predicate involving your input bind variable, something like:

where
        dbms_crypto.hash(blob column) = dbms_crypt.hash({your value})
and     dbms_Lob.compare(blob column, {your value} = 0

realistically you probably won't have any hash collisions, technically you might which is why you would use the indexed hash value as the first check then the actual value.

You may have to mess around a bit to work out the best way of setting this up (especially in 11.2). And you'll probably need to use substrb() on the hash value before you can index it.

See https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_crypto.htm

I don't think there are any licence implications of using dbms_crypto, but you'd need to check that.

Gathering stats on the table will become expensive if you do this (see https://jonathanlewis.wordpress.com/2014/03/02/auto-sample-size )

Regards

Jonathan Lewis

Sebino

Virtual columns do not seem to be null-friendly, or am I mistaken ?

Because this blobColumn may be null (and quite often is), which make index creation fail with:

ORA-01405: fetched column value is NULL

Well - actually I must find a way to sneak around this error that is returned by the call to DBMS_CRYPTO.HASH (nothing to do with virtual column)...

Sebino

Jonathan Lewis

If dbms_crypto.hash doesn't accept a NULL input than use a CASE expression to generate the index, something like the following (1 is the value for the symbolic constant HASH_MD4):

create index t1_b1 on t1(case when b1 is not null then substrb(dbms_crypto.hash(b1,1),1,16) end)

The same messy expression will have to be used in your first predicate.

Or:

alter table t1 add hash_md4 generated always as (
        case when b1 is not null then substrb(dbms_crypto.hash(b1,1),1,16) end
) virtual
;

create index t1_b1 on t1(hash_md4);

Regards

Jonathan Lewis