Re: Search Query / Performance Problem

From: Dave Wotton <Dave.Wotton_at_dwotton.nospam.clara.co.uk>
Date: 2000/04/17
Message-ID: <xwLK4.13958$jk1.270683@nnrp4.clara.net>#1/1

Sam Jordan wrote in message <38FAB138.A31C9E19_at_spin.ch>...
>
>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%')
>

Would it be possible to modify your pre-processor to generate a PL/SQL block instead of a simple query? If so, you could do it in one scan of the table. I haven't got an instance available at the moment, so I can't check the following for syntax, but what you want is something like:

DECLARE
  CURSOR c1 is

     SELECT col1, col2, col3 ....
       FROM table;

BEGIN
  FOR row in C1
  LOOP

      IF 1 = 0
      OR instr(row.col1, 'arg1') > 0
      OR instr(row.col2, 'arg2') > 0
      OR .....

         dbms_output.put_line ( row.col1 || ' ' || row.col2 || ' ' ||
                                row.col3 || .... );
      END IF;

  END LOOP;
END; (The 1=0 is simply to make the generation of the OR statements easier.)

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.

Received on Mon Apr 17 2000 - 00:00:00 CDT