Search Query / Performance Problem

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