 | |
Using Oracle Searches
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Using Oracle Searches -
11-20-2008
, 03:01 PM
#2
| |||
| |||
|
|
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; |
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,
attern||'%', 1) > 0OR CONTAINS(p_desc,
attern, 2) > 0ORDER BY SCORE(1) DESC, SCORE(2) DESC
)
WHERE ROWNUM <= :maxrows;
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
#3
| |||
| |||
|
|
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; |
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,
attern||'%', 1) > 0OR CONTAINS(p_desc,
attern, 2) > 0ORDER BY SCORE(1) DESC, SCORE(2) DESC
)
WHERE ROWNUM <= :maxrows;
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
#4
| |||
| |||
|
|
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; |
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,
attern||'%', 1) > 0OR CONTAINS(p_desc,
attern, 2) > 0ORDER BY SCORE(1) DESC, SCORE(2) DESC
)
WHERE ROWNUM <= :maxrows;
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
#5
| |||
| |||
|
|
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; |
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,
attern||'%', 1) > 0OR CONTAINS(p_desc,
attern, 2) > 0ORDER BY SCORE(1) DESC, SCORE(2) DESC
)
WHERE ROWNUM <= :maxrows;
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.