5 Replies Latest reply: Jul 25, 2011 4:08 AM by 578986 RSS

    Text Search help

    876250
      Hi All,

      Roger had helped me solve most of the issues in forum Text Search help , Had one more doubt which I tried asking in the earlier thread but that issue is solved and hence opening a new thread.


      1) Create a table
      create table test_sh (text_id number,text clob);
      2) Add a index
      CREATE INDEX ix_test_sh ON test_sh(text) INDEXTYPE IS CTXSYS.CONTEXT;
      3) Insert records
      insert into test_sh values (1,'Mark Antony');
      insert into text_sh values (2,'Mark-Antony');
      insert into text_sh values (3,'markantony' );
      insert into text_sh values (4,'markantony' );
      insert into test_sh values (4,'markanthony' );
      4) Gather Statistics
      begin
      DBMS_STATS.GATHER_TABLE_STATS('LOYALTY','TEST_SH', cascade=>TRUE);
      end;
      5) My requirement is when I query for antony like select * from test_sh where contains(text,'%antony%') then 'markanthony' should also get displayed .
      6) One more requirement is

      if I query for *'MARKANTONY'* we should get all the results

      1 Mark Antony
      2 Mark-Antony
      3 markantony
      4 markantony

      Any pointers here will be of great help.

      Thanks & Regards,
      Vikas
        • 1. Re: Text Search help
          Barbara Boehmer
          You can use the new 11g ndata feature, as demonstrated below. It requires either a multi_column_datastore or user_datastsore. You also need an ndata_section. Then you use the datastore and ndata_section in the index parameters. Then you can use ndata in your query. You also need to either insert your data before you create your index or set your index to sync on commit or synchronize after insert before you can query the indexed values.
          SCOTT@orcl_11gR2> -- table:
          SCOTT@orcl_11gR2> create table test_sh
            2    (text_id  number,
            3       text      clob)
            4  /
          
          Table created.
          
          SCOTT@orcl_11gR2> -- data:
          SCOTT@orcl_11gR2> insert all
            2  into test_sh values (1, 'Mark Antony')
            3  into test_sh values (2, 'Mark-Antony')
            4  into test_sh values (3, 'markantony' )
            5  into test_sh values (4, 'markantony' )
            6  into test_sh values (5, 'Cleopatra' )
            7  select * from dual
            8  /
          
          5 rows created.
          
          SCOTT@orcl_11gR2> -- multi_column_datastore and ndata_section:
          SCOTT@orcl_11gR2> begin
            2    ctx_ddl.create_preference ('nd_mcds', 'multi_column_datastore');
            3    ctx_ddl.set_attribute ('nd_mcds', 'columns', 'text nd');
            4    ctx_ddl.create_section_group ('nd_sg', 'basic_section_group');
            5    ctx_ddl.add_ndata_section ('nd_sg', 'nd', 'nd');
            6  end;
            7  /
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> -- index using multi_column_datastore and ndata_section:
          SCOTT@orcl_11gR2> CREATE INDEX ix_test_sh
            2  ON test_sh (text)
            3  INDEXTYPE IS CTXSYS.CONTEXT
            4  parameters
            5    ('datastore  nd_mcds
            6        section    group nd_sg')
            7  /
          
          Index created.
          
          SCOTT@orcl_11gR2> -- query:
          SCOTT@orcl_11gR2> column text format a30
          SCOTT@orcl_11gR2> select * from test_sh
            2  where  contains (text, 'ndata (nd, MARKANTONY)') > 0
            3  /
          
             TEXT_ID TEXT
          ---------- ------------------------------
                   1 Mark Antony
                   2 Mark-Antony
                   3 markantony
                   4 markantony
          
          4 rows selected.
          
          SCOTT@orcl_11gR2>
          • 2. Re: Text Search help
            876250
            This is exactly what I was looking for.

            Thanks a lot Barbara.

            Thanks & Regards,
            Vikas Krishna
            • 3. Re: Text Search help
              578986
              Thanks a lot Barbara.
              Do you know a workaround for Oracle 10G?

              Thnak and regards
              • 4. Re: Text Search help
                Barbara Boehmer
                Do you know a workaround for Oracle 10G?
                It depends on how much of the functionality you need to reproduce. If you just want to treat names with spaces or hyphens the same as names without spaces, then all you need to do is use a multi_column_datastore to create two virtual columns, one normal and the other removing the spaces and hyphens. Please see the demonstration below. This could also be done with a user_datastore and a procedure instead of a multi_column_datastore.
                SCOTT@orcl_11gR2> -- table:
                SCOTT@orcl_11gR2> create table test_sh
                  2    (text_id  number,
                  3       text      clob)
                  4  /
                
                Table created.
                
                SCOTT@orcl_11gR2> -- data:
                SCOTT@orcl_11gR2> insert all
                  2  into test_sh values (1, 'Mark Antony')
                  3  into test_sh values (2, 'Mark-Antony')
                  4  into test_sh values (3, 'markantony' )
                  5  into test_sh values (4, 'markantony' )
                  6  into test_sh values (5, 'Cleopatra' )
                  7  select * from dual
                  8  /
                
                5 rows created.
                
                SCOTT@orcl_11gR2> -- multi_column_datastore:
                SCOTT@orcl_11gR2> begin
                  2    ctx_ddl.create_preference ('nd_mcds', 'multi_column_datastore');
                  3    ctx_ddl.set_attribute
                  4        ('nd_mcds',
                  5         'columns',
                  6         'text normal, replace (replace (text, '' '', ''''), ''-'', '''') no_spaces');
                  7    ctx_ddl.set_attribute ('nd_mcds', 'delimiter', 'newline');
                  8  end;
                  9  /
                
                PL/SQL procedure successfully completed.
                
                SCOTT@orcl_11gR2> -- index using multi_column_datastore:
                SCOTT@orcl_11gR2> CREATE INDEX ix_test_sh
                  2  ON test_sh (text)
                  3  INDEXTYPE IS CTXSYS.CONTEXT
                  4  parameters
                  5    ('datastore  nd_mcds')
                  6  /
                
                Index created.
                
                SCOTT@orcl_11gR2> -- indexed tokens:
                SCOTT@orcl_11gR2> select token_text from dr$ix_test_sh$i
                  2  /
                
                TOKEN_TEXT
                ----------------------------------------------------------------
                ANTONY
                CLEOPATRA
                MARK
                MARKANTONY
                
                4 rows selected.
                
                SCOTT@orcl_11gR2> -- query:
                SCOTT@orcl_11gR2> column text format a30
                SCOTT@orcl_11gR2> select * from test_sh
                  2  where  contains (text, 'MARKANTONY') > 0
                  3  /
                
                   TEXT_ID TEXT
                ---------- ------------------------------
                         1 Mark Antony
                         2 Mark-Antony
                         3 markantony
                         4 markantony
                
                4 rows selected.
                
                SCOTT@orcl_11gR2>
                • 5. Re: Text Search help
                  578986
                  Great
                  Thanks a lot Barbara