Re: Search Query / Performance Problem

michael_bialik_at_my-deja.com schrieb:

> Hi.
>
> It's impossible to solve your problem as stated here:
> If you are using "fld_1 like '%parm1%'" ( with LEADING % sign )
> then optimizer is NOT able to use index on fld_1 ( even without
> additional ORs ).
> If you are working with 8i - look into INTERMEDIA/CONTEXT facilities.
>
> HTH. Michael.
>
> In article <38FAB138.A31C9E19_at_spin.ch>,
> sjo_at_spin.ch wrote:
> > Hi
> >
> > I have written a small set of procedures to perform
> > search operations in an Oracle 8 database. The search
> > mechanism is supposed to be made accessible to endusers
> > through the web. They can specify keywords and connect
> > them with AND or OR and use (). The whole expression is first
> > preprocessed and then converted to a normal SQL query.
> >
> > Now I have the problem that it is possible to start
> > search operations that make Oracle use extraordinary much
> > time, by using many OR operators and I'm not sure yet how
> > to solve this problem best.
> >
> > Internally the search operation is nothing else than a
> > select statement over one table which contains all the
> > keywords. If the expression consists of two keywords
> > connected by an operator, then the table is joined to
> > itself. Currently a match is not done by testing for
> > equality against the stored keywords, but by performing
> > a like operation, i.e.
> >
> > (t1.value like '%arg1%') or (t2.value like '%arg2%')
> >
> > Additionally some translations are done to deal with
> > special characters and with case sensitivity.
> >

The users are surely interested in getting the response fast. So it should not be asking too much to expect them to cooperate by optionally clicking the button "search string starts with ..." and / or "search string is case sensitive" when appropiate, provided you have such buttons on the HTML page. In this case the optimizer'd be able to make use of the indexes.

If the result set of the query contains thousands and thousands of rows, the user / or the browser would not like it neither. This is really a pain in the neck. Storing the whole result set for the user to scroll is damned storage intensive, on the one hand. On the other hand, rerun the query and select the next set of rows from the result set is slow and heavy for the database. I would like to know a good solution for this myself.

>
> > As far as I understand, the problem are the full table
> > scans that cost so much time. Basically the search operation
> > should do nothing else than simply iterating over the table
> > and check every item, whether it satisfies the whole
> > condition, instead of joining the same table multiple
> > times to itself and do full table scans multiple times.
> > Is something like that possible to do? Has anyone a better
> > idea how to write such a search statement?
> >
> > Thanks a lot in advance.
> >
> > bye
> > --
> > Sam Jordan
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Apr 17 2000 - 00:00:00 CDT