2 Replies Latest reply: Jan 10, 2012 5:29 PM by 876250 RSS

    Search based on relevance.

    876250
      Hi Gurus,

      I would like to give more relevance(score) for exact search.
      SQL> create table test_sh2(text_id number,text clob);

      Table created.

      SQL> insert into test_sh2 values (1,'staples');

      1 row created.
      SQL> insert into test_sh2 values (1,'steaks');

      1 row created.

      SQL> commit;

      Commit complete.
      SQL> create index ix_tst_sh2 on test_sh2(text) indextype is ctxsys.context ;

      Index created.

      SQL> select score(1),text_id,text from test_sh2 where contains(text,'STAPLES or %STAPLES% or ?STAPLES or !STAPLES or $STAPLES',1) > 0 ;

      SCORE(1) TEXT_ID
      ---------- ----------
      TEXT
      --------------------------------------------------------------------------------
      4 1
      staples

      4 1
      steaks
      If we see here both exact search and fuzzy search returns the same score. Is there any way by which we can get a higher score for exact searches ?
        • 1. Re: Search based on relevance.
          Barbara Boehmer
          I have provided two methods in the demonstration below.

          The first method uses weighting. By multiplying by 100, you guarantee that the exact match will have a score of 100. Since it cannot be higher than 100, any resulting scores higher than 100 become 100. Although the highest number that you can multiply by is 10, you can multiply by it twice to accomplish multiplying by 100.

          The second method uses DEFINESCORE and DISCRETE, which causes the exact match to return a score of 100.

          SCOTT@orcl_11gR2> create table test_sh2 (text_id number, text clob);
          
          Table created.
          
          SCOTT@orcl_11gR2> insert into test_sh2 values (1, 'staples');
          
          1 row created.
          
          SCOTT@orcl_11gR2> insert into test_sh2 values (1, 'steaks');
          
          1 row created.
          
          SCOTT@orcl_11gR2> create index ix_tst_sh2 on test_sh2 (text) indextype is ctxsys.context;
          
          Index created.
          
          SCOTT@orcl_11gR2> column text format a30
          SCOTT@orcl_11gR2> select score(1), text_id, text
            2  from   test_sh2
            3  where  contains
            4             (text,
            5              'STAPLES
            6            or %STAPLES% or ?STAPLES or !STAPLES or $STAPLES',
            7            1) > 0
            8  /
          
            SCORE(1)    TEXT_ID TEXT
          ---------- ---------- ------------------------------
                   4          1 staples
                   4          1 steaks
          
          2 rows selected.
          
          SCOTT@orcl_11gR2> select score(1), text_id, text
            2  from   test_sh2
            3  where  contains
            4             (text,
            5              'STAPLES * 10 * 10
            6            or %STAPLES% or ?STAPLES or !STAPLES or $STAPLES',
            7            1) > 0
            8  /
          
            SCORE(1)    TEXT_ID TEXT
          ---------- ---------- ------------------------------
                 100          1 staples
                   4          1 steaks
          
          2 rows selected.
          
          SCOTT@orcl_11gR2> select score(1), text_id, text
            2  from   test_sh2
            3  where  contains
            4             (text,
            5              'DEFINESCORE (STAPLES, DISCRETE)
            6            or %STAPLES% or ?STAPLES or !STAPLES or $STAPLES',
            7            1) > 0
            8  /
          
            SCORE(1)    TEXT_ID TEXT
          ---------- ---------- ------------------------------
                 100          1 staples
                   4          1 steaks
          
          2 rows selected.
          • 2. Re: Search based on relevance.
            876250
            Thanks a lot Barbara. That was exactly what I was looking for.