Re: SQL question related to searching Keywords

A copy of this was sent to "Alexandros Kotsiras, NYC" <alexandros_k_at_prodigy.net> (if that email address didn't require changing) On Tue, 14 Dec 1999 23:55:59 -0500, you wrote:

>Hello,
>I am developing a web-search engine for a web-site. Client is HTML and i
>connect to an Oracle database through JAVA Servlets/JDBC. Part of the
>web site is stored in a database so i decided instead of searching the
>full-text to add a "Keywords" attribute to my tables and search only the
>keywords. It is supposed to be more efficient...is it ?

no. read on.

>My question is how should i write the SQL statement that compares the
>user's entry in the HTML textbox to the Keywords column. If for example
>the keywords are stored in an VARCHAR2 column named KEYWORDS in a comma
>delimited fromat : keyword1,keyword2,keyword3 and the user enters in
>the text box 'Alex'.
>SELECT * FROM TableName WHERE 'Alex' LIKE .........what ?
>If i could parse and tokenize the KEYWORDS column values then i could
>for example write :
>

given that you have "keyword1,keyword2,keyword3" you should change that to:

",keyword1,keyword2,keyword3," (maybe consider using | instead of , as well)

and then query

select * from tablename where keywords like '%,' || :p_input || ',%';

that will always result in a FULL TABLE SCAN so will not be very efficient. Much much better would be:

select * from tablename where PRIMARY_KEY_OF_TABLE_NAME in ( select PRIMARY_KEY_OF_TABLE_NAME from keywords_table where theWord = :p_input );

That will do an index range scan on the index you put on theword in the keywords_table generating a set of primary keys that match your criteria (a small list). We then get just these rows from the tablename table. This, in general, will be much faster.

>SELECT * FROM TableName WHERE ('Alex' LIKE keyword1 OR 'Alex' LIKE
>keyword2 OR 'Alex' LIKE keyword3)
>Is there a SQL function that can help ?
>Or is there a different approach to searching keyword ?
>Not to mention the case where the user enters more than one word in the
>text box......
>
>I also thought of storing the Keywords values in a separate table which
>will have a many-to-one relationship to my master table. In that case
>every keyword willl be stored in a separate record and i don't need to
>parse and tokenize... but i will incurr the performance degradarion
>because of the join operation.
>

Why do people think joins are 'slow' anyway -- its what its all about :)

>Thanks,
>Alex.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 15 1999 - 08:04:06 CST