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>...
>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 Fri Dec 06 2002 - 19:10:49 CST
