Re: how to activate oracle text in 10g enterprise

From: BicycleRepairman <engel.kevin_at_gmail.com>
Date: Wed, 10 Dec 2008 05:08:31 -0800 (PST)
Message-ID: <a7d07c23-0abc-4d5a-ac82-bb747c6d11db@h5g2000yqh.googlegroups.com>


On Dec 9, 10:35 am, "stc" <slowtraincom..._at_softhome.net> wrote:
> "BicycleRepairman" <engel.ke..._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.

ok, first thing to mess around with is '%keyword% ... that's a bad idea in oracle text without setting up some more stuff. See http://download.oracle.com/docs/cd/B28359_01/text.111/b28304/cqoper.htm#i998461 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

Original text of this message