Re: Help ! search question...

From: <mhr_at_NOSPAMramboll-it.dk>
Date: Mon, 18 Sep 2000 10:36:45 +0200
Message-ID: <39C5D41C.A43DE8B5@NOSPAMramboll-it.dk>

Hi,

Forgive me if I'm stupid, but I don't get the point in using all these sophisticated techniques, you mention...??

It can be because I don't know the environment from where your queries are entered by users, but what you describe as the scope seems quite simple for me to implement in pure sql.

1. Use the the replace function to replace any '*' with '%' like this: select replace('IBM*','*','%') from dual;

REPL

---

IBM% 2) Fire the query.

The Oracle optimizer will accept to use its indexes - eventually in terms of an index scan - as long as any wildcards are not placed in the FRONTEND of the strings to match upon.

So if you DON'T have to query like '%IBM' ('*IBM'), you don't need to worry beforehand.

jackbenimble_at_my-deja.com wrote:

> Hi all,
>
> I'd like to ask about how to conduct a search in Oracle which does the
> following:
>
> 1) If the user enters a single word, such as "IBM", retrieve all
> records which contain the string "ibm" as a standalone word.
>
> 2) If the user enters a single word plus "*" (eg. "IBM*") , retrieve
> all records which contain a word which starts with the letters "IBM",
> but is not necessarily the entire word.
>
> Is there a way this can be done with regular SQL?
>
> Barring that, my first thought was to to a select on "%IBM%", and then
> do a perl regex search on it using the oroinc.com package. But this
> would require two "passes", and since the database contains 300,000
> records, I don't know yet what kind of performance hit this would
> involve.
>
> The second idea is to use the Oracle ConText Cartridge (Intermedia Text
> in 8i). I think we may end up going this way, but I'd like to avoid it
> if possible because it apparently has a history of problems.
>
> The third idea is to use a package called "Omnidex". I know very
> little about it.
>
> Yet another idea is to use something called the "OWS" package, which
> includes the "OWA_PATTERN" package. Again, I don't know anything about
> it.
>
> Does anyone have suggestions? This is unfamiliar territory for me, and
> we're in a time crucnch.
>
> Thanks,
> Jack
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Sep 18 2000 - 03:36:45 CDT