Forum Stats

  • 3,769,346 Users
  • 2,252,951 Discussions
  • 7,874,992 Comments

Discussions

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

Sebino
Sebino Member Posts: 41 Red Ribbon
edited Jul 2, 2021 2:15PM in General Database Discussions

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

Tagged:

Best Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,786 Gold Crown
    Accepted Answer

    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
    Jonathan Lewis Member Posts: 9,786 Gold Crown
    Accepted Answer

    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

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,786 Gold Crown
    Accepted Answer

    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
    Sebino Member Posts: 41 Red Ribbon
    edited Jul 5, 2021 1:26PM

    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
    Jonathan Lewis Member Posts: 9,786 Gold Crown
    Accepted Answer

    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