Re: Using Oracle Searches

On Nov 21, 12:01 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Hi,
>
> We have an application on our web site where users can enter a product
> code into a search box.  The box works similar to Yahoo's financial
> page where you can enter tickers into a search box.   As you enter
> letters, tickers that start with those letters appear in a drop down.
>
> We have the same thing, and on top of that, we pass the number or
> records we want returned.  If there are not enough products with that
> code pattern, we look at the product description for words with that
> pattern.
>
> To do this I am using Oracle text search.  I've run into a problem.
> The query below works fine if I am searching for a word within a
> description, but it does not match on the product codes that START
> with the pattern.  Does the query below do that?  Or do I need to add
> something which does BOTH a LIKE and a CONTAINS?
>
> And, since each key stroke refreshes the list, will the indexes with
> regards to the text search be fast enough?  It is only about 100,00
> records.
>
>      SELECT * FROM (
>       SELECT p_code p_desc FROM (
>        SELECT p_code p_desc, 1 ord
>        FROM master_table
>        WHERE CONTAINS (p_code, ''' || p_pattern || ''') > 0
>       UNION
>        SELECT p_code, p_desc, 2 ord
>        FROM master_table
>        WHERE CONTAINS (p_desc, ''' || p_pattern || ''') > 0)
>       ORDER BY ord
>       )
>      WHERE rownum <= ' || p_num_recs;

You are using LITERAL arguments in your web-facing program?!?! Ever heard of SQL injection attacks? Yours is a good example of vulnerable, and non-scalable, code. Rewrite it to use bind variables ASAP, and please take a course on safe coding.

As for your question, here's what might be of use as the foundation:

SELECT * FROM
 (SELECT /*+ FIRST_ROWS(10) */ p_code, p_desc

    FROM master_table

   WHERE CONTAINS(p_code, :pattern||'%', 1) > 0
      OR CONTAINS(p_desc, :pattern, 2) > 0
   ORDER BY SCORE(1) DESC, SCORE(2) DESC

It is not clear if you want partial matches in descriptions to produce hits so I assumed you don't. That is, partial match on the product code will produce a hit, and only a full word match of a word in the description will produce a hit. The results will be ordered so that hits from p_code will come first (what you tried to achieve with extra ORD column.) The above query assumes you have Text indexes on both columns, from your original query I got the impression this is the case.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Nov 21 2008 - 06:09:05 CST