Re: how to activate oracle text in 10g enterprise

From: stc <>
Date: Tue, 9 Dec 2008 16:35:20 +0100
Message-ID: <ghm37q$160$>

"BicycleRepairman" <> wrote in message

>Now, to your performance problem.
>What is the type of column you are indexing? How many rows? What type
>of content is it?
>What was the query?
>Did the resultset you received match your expectations (other than

The table has 80 varchar2 columns with length varying from 200 to 2000 characters. Number of rows is 100k. I did following:

  1. grant ctxapp to my_user;
  2. alter table my_table add (search_cols varchar2(1) null);
  3. created preference and set attributes:

 ctx_ddl.create_preference ('test', 'multi_column_datastore');  ctx_ddl.set_attribute ('test', 'columns', 'column_1,column_2,column_3...'); end;

4. created index:

create index my_idx on my_table (search_cols) indextype is ctxsys.context parameters ('datastore test');

Old query which lasts approx. 8 seconds is:

select * from my_table where column_1 like '%keyword%' or column_2 like '%keyword%' or...

New query using Oracle Text lasts approx. 160 seconds:

select * from my_table where contains(search_cols, '%keyword%') > 0

Both queries return correct resultset. The same procedure worked in 10g XE and the latter query lasts under 2 seconds. Received on Tue Dec 09 2008 - 09:35:20 CST

Original text of this message