Query on LOB fields in 8.1.7.3 with many OR conditions

Hi,

We have a problem querying CLOB fields in oracle 8.1.7.3 . This is what we observe in the Query below on a table UETESTLIST1DATA which has a CLOB field UELIST:
SELECT count(*) FROM UETESTLIST1DATA where ( dbms_lob.instr (UELIST , 'TESTUSER_10202') <> 0 OR  dbms_lob.instr(UELIST , 'TESTUSER_10203') <> 0 OR

dbms_lob.instr(UELIST , 'TESTUSER_10204') <> 0 OR  
dbms_lob.instr(UELIST , 'TESTUSER_10205') <> 0 OR  
dbms_lob.instr(UELIST , 'TESTUSER_10206') <> 0 OR  
dbms_lob.instr(UELIST , 'TESTUSER_10207') <> 0 OR 
dbms_lob.instr(UELIST , 'TESTUSER_10208') <> 0 OR  
dbms_lob.instr(UELIST , 'TESTUSER_10209') <> 0)

As the number of OR conditions grow, Oracle takes exponentially more time to return the Query results. For example, for 5 ORs it takes 800 milliseconds
for 10 ORs it takes 1600 milliseconds
for 20 ORs it takes 3000 milliseconds
for 100 ORs it takes 8562 milliseconds

We also noticed that Oracle short-circuits the Query and it consistently returns in less than one second if all rows have the value "'TESTUSER_10202'".

The question is whether there is a way to speed up this response time ?
Can i, for instance, pass all the Query Strings ORed with the dbms_lob.instr like dbms_lob.instr(UELIST , 'TESTUSER_10202' OR 'TESTUSER_10203' OR 'TESTUSER_10203' ) <> 0 ? Or is there a better method to search for multiple strings across a CLOB column ?

TIA,
Jairam Received on Thu Nov 21 2002 - 20:25:40 CST