Using Oracle Searches
Date: Thu, 20 Nov 2008 13:01:56 -0800 (PST)
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;Received on Thu Nov 20 2008 - 15:01:56 CST