Searching Inside CLOBs

From: sundeep maini <sundeep_maini_at_yahoo.com>
Date: Thu, 24 Jan 2002 07:41:58 -0800
Message-ID: <F001.003F9E3A.20020124072027@fatcity.com>

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:

Poor index selection due to UPPER
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). Received on Thu Jan 24 2002 - 09:41:58 CST