Re: Search Query / Performance Problem

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.
>
> 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