Sundeep, You couldn't have made a better case for using interMedia Text index(es) on your CLOB column(s) if you'd had help from Oracle Marketing! ;-)
interMedia Text is very powerful and efficient. We use it to index and search almost 15 million CLOB documents, many in HTML format. interMedia even supports section searching, so you can search only between selected tags within a CLOB. Check out the interMedia Text docs. It's even available in Standard Edition. You (and your users) will love it. Jack -------------------------------- Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -----Original Message----- maini Sent: Monday, January 21, 2002 3:06 PM To: Multiple recipients of list ORACLE-L 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.topic) LIKE '%:in_string%' OR UPPER(tab2.subtopic) LIKE '%:in_string%' OR ..... OR dbms_lob.instr(bodytext,:in_string,1,1) <> 0; Note: in_string is always upper case but the column contents are not. Concerns: 1. Poor index selection due to UPPER 2. CLOB searches are wrong as the CLOB contents are case sensitive. One way to remedy this would be to build a user defined function which will read the CLOB col. in chunks and look for string matches inside individual chunks till a success or end of CLOB. Either way that is a pretty intense way to search for a string among table data when a user is waiting for a quick response. Keyword searches are supported differently. This mechanism is only for random string searches. Any cues or suggestions? Especially for case sensitive searches inside CLOBS? TIA Sundeep ===== Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
