Re: Search Method without %these% or ConText?

Cansu and Simon,

There is a 3rd party product called OMNIDEX from DISC that will allow you to do what you are asking, WITHOUT a table scan and with little overhead.

OMNIDEX advanced indexing allows full and partial keyword lookups using inverted list indexing technology, and it works on existing databases such as Oracle.

Please contact me for further information.

Cheryl Grandy
DISC
cgrandy_at_disc.com
303 444-4000
www.disc.com/home

OMNIDEX - for the fastest applications ever!

>
> Cansu Baykan <cansubaykan_at_netscape.net> wrote in message
> news:8e5av0$b0f$1_at_nnrp1.deja.com...
> > Hi,
> >
> > I have a web application (cgi written in perl) that searches thru an
> > oracle database, two of the fields searched are keyword types, where
 I
> > would like the functionality of doing a double percent pattern
 match.
> > e.g.
> >
> > SELECT something, anything, everything
> > FROM sometable WHERE something = 'exact',
> > AND anything like '%close but not exact%'
> >
> > I was told by DBAs that this is unacceptable because it does a full
> > table scan. We were going to use ConText searches for these fields
 but
> > apparently the overhead from that is also high.
> >
> > Does anynone have any experience or suggestions? Some 3rd party
> > product, or a neat trick?

In article <8edc93$4j3$1_at_newsg4.svr.pol.co.uk>,   "Simon Hedges" <shedges_at_hhhh.freeserve.co.uk> wrote:
>
> I believe that there's not a lot you can do, but here are a few
 suggestions
> which may or may not be applicable:
>
> 1. Try to narrow down the search in other ways - insist the used
 enter at
> least one other indexed item in the search.
>
> 2. If the text records are duplicates (i.e. if 5% of the records
 contain
> the text 'My Mother is My Aunt', and 3% of the record contain the text
 'My
> Father is my Uncle' etc, you could create a summary table, join that
 to the
> main table using the primary key, and do the pattern matching on the
 summary
> table and use the index to join back. This will only work if there
 are
> relatively few variations of text, but if successful, you may end up
 with
> 1000 rows int he summary table compared to 1000000 in the main table.
>
> 3. Separate your keywords out into a separate detail table, with a
 row for
> each keyword, and search on those via an index.
>
> 4. If your text field is in a table with a lot of data in, then you
 could
> separate it out into a separate table containing only the text and a
 PK with
> a one-to-one relationship to the main table. This will speed up a full
 table
> scan a little.
>
> 5. Don't necessarily believe your DBA. He/She is correct in
 principle - a
> full table scan is relatively slow, but has the DBA tried it? It may
 give
> an acceptable response to your users. I find that some (but by no
 means
> all) DBA get ideas into their heads and pass them on as gospel without
> having tried the alternative.
>
> 6. If the 'close but not exact' bit means that you have leading or
 trailing
> spaces etc, you could consider trimming these out.
>
> Just some thoughts
>
> Good luck,
>
> Simon Hedges
> Gloucester
> UK
>
>

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever


Sent via Deja.com http://www.deja.com/
Before you buy.