Re: Search Query / Performance Problem

What you are doing now of course also involves cartesian products between the several conditions.
I would try either of the following approaches. If the condition columns are the same, transform the OR's in an IN operator. This will limit the number of operators to 255, but I don't think that's a problem.
The other approach is performing UNIONs like this select ...
from table
where condition1
union
select ...
from table
where condition2
This would of course still result in full table scans but you don't have a cartesian product anymore. This example is replacing ORs, ANDs can be replaced by INTERSECTs.

Hth,

Sybrand Bakker, Oracle DBA

Sam Jordan <sjo_at_spin.ch> schreef in berichtnieuws 38FAB138.A31C9E19_at_spin.ch...
> 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
Received on Mon Apr 17 2000 - 00:00:00 CDT