| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Searching Inside CLOBs
Read the fine docs on Technet on Intermedia text, as well as the XML development kit.
-Joe
On Thu, 2002-01-24 at 10:20, sundeep maini wrote:
> Our developers store XML docuements inside CLOBS and
> want to search the CLOB contents based on a search
> string. I have lot of concern about such queries but
> could benefit from someone on the list who has already
> solved the performnce issues with such searches.
>
> Typical existing queries I have come across are:
>
> Select columns
> FROM list of tables
> WHERE join conditions
> AND UPPER(tab1.varcharcol) LIKE '%:in_string%'
> OR UPPER(tab2.varcharcol) LIKE '%:in_string%'
> OR .....
> OR dbms_lob.instr(lobcol,:in_string,1,1) <> 0;
>
> Note:
>
> in_string is always upper case but the columns are
> Concerns:
>
> 1. Poor index selection due to UPPER
> 2. CLOB searches are obviously wrong. To do a mixed
> case string match one needs to read chunks of the lob
> in a user defined function of some sort, convert it to
> upper and then do string matches. Any tips on this
> one?
>
>
>
> =====
>
> Sundeep Maini
> Consultant
> Currently on Assignement at Marshfield Clinic WI
> mainis_at_mfldclin.edu
>
> __________________________________________________
> Do You Yahoo!?
> Great stuff seeking new owners in Yahoo! Auctions!
> http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: sundeep maini
> INET: sundeep_maini_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: jraube_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Jan 24 2002 - 11:30:26 CST
 |
 |