This discussion is archived
2 Replies Latest reply: May 2, 2013 3:07 AM by tomvdduin RSS

single index for multiple columns

tomvdduin Newbie
Currently Being Moderated
Hello all,

we have a requirement for our application, to add a single search box, that searches over 1 table for many different columns. The way we've now implemented it, is as follows (simplified example):
select *
from emp
where instr(ename,:SEARCH)>0
or instr(job,:SEARCH)>0
or ...

This aproach is relatively slow of course. I want to change that, using Oracle Text. Can you give me some direction what kind of index we have to use? Our requirement:
- index must continue be up to date (or within seconds)
- search must be fast
- preferably case insensitive
- number of columns to be indexed: aprox. 20 within one table

I once heard that you can make a function, with all the columns you want to index. that function generates an xml document and you can use that function to create an Oracle Text index. However, I can't seem to find in what way I can implement that. Or is there a better aproach?

Thanks again for your help!
  • 1. Re: single index for multiple columns
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    You can use a multi_column_datastore, which generates xml documents like you are describing behind the scenes. You can also use either the transactional option or sync(on commit) with some optimization. Please see the simplified example with just two columns below, that corresponds to what you provided. You can find other information and examples for the multi_column_datastore in the online documentation.
    SCOTT@orcl_11gR2> begin
      2    ctx_ddl.create_preference ('emp_ds', 'multi_column_datastore');
      3    ctx_ddl.set_attribute ('emp_ds', 'columns', 'ename, job');
      4  end;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> alter table emp add (any_column varchar2(1))
      2  /
    
    Table altered.
    
    SCOTT@orcl_11gR2> create index emp_idx
      2  on emp (any_column)
      3  indextype is ctxsys.context
      4  parameters
      5    ('datastore emp_ds
      6        transactional')
      7  /
    
    Index created.
    
    SCOTT@orcl_11gR2> variable search varchar2(100)
    SCOTT@orcl_11gR2> exec :search := 'CLARK'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> select ename, job from emp
      2  where  contains (any_column, :search) > 0
      3  /
    
    ENAME      JOB
    ---------- ---------
    CLARK      MANAGER
    
    1 row selected.
    
    SCOTT@orcl_11gR2> exec :search := 'CLERK'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> select ename, job from emp
      2  where  contains (any_column, :search) > 0
      3  /
    
    ENAME      JOB
    ---------- ---------
    SMITH      CLERK
    ADAMS      CLERK
    JAMES      CLERK
    MILLER     CLERK
    
    4 rows selected.
  • 2. Re: single index for multiple columns
    tomvdduin Newbie
    Currently Being Moderated
    Hi Barbara,

    Looks great! I'm going to deepen me into a multi_column_datastore index. Thanks for your suggestion and example!

    Regards,
    Tom

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points