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?
I believe that there's not a lot you can do, but here are a few suggestions
which may or may not be applicable:
- Try to narrow down the search in other ways - insist the used enter at
least one other indexed item in the search.
- 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.
- Separate your keywords out into a separate detail table, with a row for
each keyword, and search on those via an index.
- 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.
- 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.
- 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
Received on Sat Apr 29 2000 - 00:00:00 CDT
