Re: how to activate oracle text in 10g enterprise
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:
- grant ctxapp to my_user;
- alter table my_table add (search_cols varchar2(1) null);
- 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