Re: Search Problem

Thanks Thomas.

The article was probably a bit over my head but using the ideas in it I have been able to create a trigger to populate a CLOB with the concatenated fields I want to search and then used an InterMedia index on the CLOB. Seems to work okay at the moment but I haven't stretched it any so fingers crossed.

Anyways, thanks for the tip.

Matt.

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9tol9o01g97_at_drn.newsguy.com...
> 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:440419921 146
>
> 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
>
Received on Sat Nov 24 2001 - 20:52:07 CST