Skip to Main Content

searching 3 columns for best relevant match

635179Jan 17 2011 — edited Jan 20 2011

I need to improve on my current search functionality, which is less than effective. I have the following m.view
SELECT     pro_id
      ,pro.cla_id cla_id
      ,pro.title  title
      ,     art_id
      ,   artist
      ,     cor_id
      ,   corporate_group
      ,     dis_id
      ,   distributor
      ,     rec_id
      ,   recvid_co
      ,     lab_id
      ,   label
      ,pro.title || '|' || || '|' ||  search_string
      ,row_number() over (order by pro.title)           title_seq
      ,row_number() over (order by pro.title, title_artist_seq
FROM products         pro
    ,artists          art
    ,labels           lab
    ,recvid_co        rec
    ,corporate_groups cor
    ,distributors     dis
WHERE pro.art_id = (+) 
AND pro.lab_id =
AND pro.rec_id =
AND pro.cor_id =
AND pro.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

  OPEN p_data_cursor FOR SELECT pro_id
                         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
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Feb 17 2011
Added on Jan 17 2011