1 Reply Latest reply: Apr 29, 2014 8:23 PM by Barbara Boehmer RSS

    NDATA does not give steven / stephen

    spur230

      I am using 11.2.0.3. 

       

      create table table1
        (column1         varchar2(60),
         indexed_column  varchar2(1))
      /
      
      insert all
      into table1 values ('stephen', null)
      into table1 values ('steven', null)
      select * from dual
      /
      
      create or replace procedure mystery_proc
        (p_rowid in            rowid,
         p_clob  in out nocopy clob)
      as
      begin
        select '<tag1>' || column1 || '</tag1>' 
        into   p_clob
        from   table1
        where  rowid = p_rowid; 
      end mystery_proc;
      
      
      begin
        ctx_ddl.create_preference ('mystery_ds', 'user_datastore');
        ctx_ddl.set_attribute ('mystery_ds', 'procedure', 'mystery_proc');
        ctx_ddl.create_preference ('mystery_lex', 'basic_lexer');
        ctx_ddl.set_attribute ('mystery_lex', 'base_letter', 'YES');
        ctx_ddl.create_preference ('mystery_wl', 'basic_wordlist');
        ctx_ddl.set_attribute ('mystery_wl', 'substring_index', 'true');
        ctx_ddl.create_section_group ('mystery_sg', 'basic_section_group');
        ctx_ddl.add_ndata_section ('mystery_sg', 'tag1', 'tag1');   
      end;
      /
      
      drop index test_idx;
      
      create index test_idx
      on table1 (indexed_column)
      indextype is ctxsys.context
      parameters
        ('datastore      mystery_ds
          lexer          mystery_lex
          wordlist       mystery_wl
          section group  mystery_sg
          stoplist       ctxsys.empty_stoplist')
      /
      
      select token_text from dr$test_idx$i
      /
      
      select column1, score(1) from table1
      where  contains (indexed_column, 'ndata(tag1, stephen)', 1) > 0
      order  by score(1) desc
      /
      

       

      It only return stephen.  I wanted it to give both stephen and steven.

        • 1. Re: NDATA does not give steven / stephen
          Barbara Boehmer

          I don't have an answer for why ndata doesn't find steven and stephen similar enough, but it looks like both fuzzy and soundex do, as shown below.

           

          SCOTT@orcl12c> create table table1

            2    (column1     varchar2(60))

            3  /

           

          Table created.

           

          SCOTT@orcl12c> insert all

            2  into table1 values ('stephen')

            3  into table1 values ('steven')

            4  select * from dual

            5  /

           

          2 rows created.

           

          SCOTT@orcl12c> create index test_idx

            2  on table1 (column1)

            3  indextype is ctxsys.context

            4  /

           

          Index created.

           

          SCOTT@orcl12c> select column1, score(1) from table1

            2  where  contains

            3            (column1,

            4             '!stephen or

            5          (fuzzy(stephen,1,5000,w))',

            6             1) > 0

            7  order  by score(1) desc

            8  /

           

          COLUMN1                                                        SCORE(1)

          ------------------------------------------------------------ ----------

          stephen                                                              39

          steven                                                               18

           

          2 rows selected.

           

          SCOTT@orcl12c> select column1, score(1) from table1

            2  where  contains

            3            (column1,

            4             '!steven or

            5          (fuzzy(steven,1,5000,w))',

            6             1) > 0

            7  order  by score(1) desc

            8  /

           

          COLUMN1                                                        SCORE(1)

          ------------------------------------------------------------ ----------

          steven                                                               39

          stephen                                                              18

           

          2 rows selected.

           

          SCOTT@orcl12c> select column1, score(1) from table1

            2  where  contains

            3            (column1,

            4             '!stephen',

            5             1) > 0

            6  order  by score(1) desc

            7  /

           

          COLUMN1                                                        SCORE(1)

          ------------------------------------------------------------ ----------

          steven                                                                4

          stephen                                                               4

           

          2 rows selected.

           

          SCOTT@orcl12c> select column1, score(1) from table1

            2  where  contains

            3            (column1,

            4             '!steven',

            5             1) > 0

            6  order  by score(1) desc

            7  /

           

          COLUMN1                                                        SCORE(1)

          ------------------------------------------------------------ ----------

          steven                                                                4

          stephen                                                               4

           

          2 rows selected.

           

          SCOTT@orcl12c> select column1, score(1) from table1

            2  where  contains

            3            (column1,

            4             '(fuzzy(stephen,1,5000,w))',

            5             1) > 0

            6  order  by score(1) desc

            7  /

           

          COLUMN1                                                        SCORE(1)

          ------------------------------------------------------------ ----------

          stephen                                                              39

          steven                                                               18

           

          2 rows selected.

           

          SCOTT@orcl12c> select column1, score(1) from table1

            2  where  contains

            3            (column1,

            4             '(fuzzy(steven,1,5000,w))',

            5             1) > 0

            6  order  by score(1) desc

            7  /

           

          COLUMN1                                                        SCORE(1)

          ------------------------------------------------------------ ----------

          steven                                                               39

          stephen                                                              18

           

          2 rows selected.