Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query on LOB fields in 8.1.7.3 with many OR conditions
You might want to look at the Context option (or Intermedia, or OracleText - whichever they call it in your version). This is typically used for indexing and searching unstructured text.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____England______January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Jairam wrote in message <143d0399.0211211825.bfb8db5_at_posting.google.com>...Received on Fri Dec 06 2002 - 19:10:49 CST
>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