| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Searching Inside CLOBs
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
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 Mon Jan 21 2002 - 15:26:01 CST
 |
 |