Re: Search Problem

In article <rjFL7.3$aE.1116_at_news0.optus.net.au>, "Matt says...
>
>Hi,
>I am trying to create a web based search of a simple table of a telephone
>directory. The search is a standard web search where you enter the search
>term and get the results that match. My problem is that cannot figure out
>how to search many small fields (20) for the one term without a massively
>inefficient use of OR's and LIKEs '%text%'.
>
>I looked at the interMedia stuff which seems okay but seems to require an
>index for each field. Since some of the fields are quite small (as little as
>20 characters) this seems like overkill and doesn't really solve the OR
>problem anyway.
>
>So any suggestions on where to look?
>
>Matt.
>
>

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:440419921146

It shows how to use interMedia to index a user defined function. This user defined function be passed a rowid and a clob -- the rowid is the row that needs indexing, the clob will be where you put the text to be indexed. Hence, you can take all fo the fields (read via rowid) - dbms_lob.writeappend them to the clob (with a space in between) and interMedia will index the clob as a single text field.

Then you can:

select * from t where contains( idx, 'some text' ) > 0

and that'll search across all of the fields.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp