Skip to Main Content

searching 3 columns for best relevant match

635179Jan 17 2011 — edited Jan 20 2011
Hi

I need to improve on my current search functionality, which is less than effective. I have the following m.view
CREATE MATERIALIZED VIEW CHARTDEV.MV_OL_SEARCH
AS
SELECT pro.id     pro_id
      ,pro.cla_id cla_id
      ,pro.title  title
      ,art.id     art_id
      ,art.name   artist
      ,cor.id     cor_id
      ,cor.name   corporate_group
      ,dis.id     dis_id
      ,dis.name   distributor
      ,rec.id     rec_id
      ,rec.name   recvid_co
      ,lab.id     lab_id
      ,lab.name   label
      ,pro.title || '|' || art.name || '|' || cor.name  search_string
      ,row_number() over (order by pro.title)           title_seq
      ,row_number() over (order by pro.title, art.name) title_artist_seq
FROM products         pro
    ,artists          art
    ,labels           lab
    ,recvid_co        rec
    ,corporate_groups cor
    ,distributors     dis
WHERE pro.art_id = art.id (+) 
AND pro.lab_id = lab.id
AND pro.rec_id = rec.id
AND pro.cor_id = cor.id
AND pro.dis_id = dis.id
/

CREATE PUBLIC SYNONYM mv_ol_search FOR chartdev.mv_ol_search

GRANT SELECT ON mv_ol_search TO topline;

EXEC ctx_ddl.create_index_set('ol_search_iset');

EXEC ctx_ddl.add_index('ol_search_iset','title_seq');

EXEC ctx_ddl.add_index('ol_search_iset','title_artist_seq');

EXEC ctx_ddl.create_stoplist('OL_SEARCH_STOPLIST')

CREATE INDEX ol_search_nuq ON mv_ol_search(search_string) INDEXTYPE IS ctxsys.ctxcat PARAMETERS ('index set ol_search_iset stoplist OL_SEARCH_STOPLIST');
This is used in an application, for users to enter a search term by song title, artist, or coporate group(i.e. 'Long Road To Ruin'), which will run the following code:-
p_string = ''Long Road To Ruin'

PROCEDURE get_search(p_string      IN  VARCHAR2
                             ,p_data_cursor OUT SYS_REFCURSOR) IS

BEGIN  
  OPEN p_data_cursor FOR SELECT pro_id
                               ,cla_id
                               ,title
                               ,artist
                               ,corporate_group
                         FROM mv_ol_search
                         WHERE CATSEARCH(search_string, UPPER(p_string), 'order by title_artist_seq') > 0
                         AND ROWNUM <840;
END get_search;
The problem is that thousands of results are returned with these words any where in the title, and the actual title which is closest to the search term buried among them somewhere. Can anybody recommend which road i should take in order for the query to return a better ordered result set. I was thinking something based on relevance, any ideas?

Many thanks
Comments
Post Details
Added on Jan 17 2011
4 comments
44 views