Re: how to activate oracle text in 10g enterprise

From: stc <slowtraincoming_at_softhome.net>
Date: Tue, 9 Dec 2008 16:35:20 +0100
Message-ID: <ghm37q$160$1@news.metronet.hr>

"BicycleRepairman" <engel.kevin_at_gmail.com> wrote in message news:cab70f1b-aad1-475a-ba68-a35a1a207711_at_h5g2000yqh.googlegroups.com...

>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
>performance)?

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:

begin
 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