Re: how to activate oracle text in 10g enterprise
Date: Wed, 10 Dec 2008 05:08:31 -0800 (PST)
On Dec 9, 10:35 am, "stc" <slowtraincom..._at_softhome.net> wrote:
> "BicycleRepairman" <engel.ke..._at_gmail.com> 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...');
> 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.
ok, first thing to mess around with is '%keyword% ... that's a bad
idea in oracle text without setting up some more stuff. See
for more info.
there are several ways to do this in a text-index-friendly manner, depending on some of the specifics of the data and the types of searches you want to support.
contains(searchcol, 'keyword')>0 may be sufficient -- that is, if the %s are only there to make the LIKE clause work. Received on Wed Dec 10 2008 - 07:08:31 CST