| 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
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
-----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
mainis_at_mfldclin.edu
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: japplewhite_at_inetprofit.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-LReceived on Mon Jan 21 2002 - 16:05:26 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |