Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Text Design Question
Hello Kevin,
I think I can help you here ...
"Kevin Crosbie" <caoimhinocrosbai_at_yahoo.com> wrote:
> My question is what is the best way to set up the index such
> that I can do the following query in the most efficient way:
> select i.title, i.date_added, it.content
> from items i, item_text it
> where i.item_id = it.item_id
> and i.date_added between :start_time and :end_time
> and contains(it.content, 'something') > 0;
>
> I have set up an index using a straight forward:
> create index content_ix on item_text(content) indextype is ctxsys.context;
>
... snip ...
>
> This query normally takes around 5 to 15 minutes to run on an alpha server
> so it's pretty slow.
>
> Can anyone suggest any way to speed this up?
>
First of all, I don't see why you really need Oracle Text ... because of the following:
Therefore you are probably better off with an ordinary and _standard_ wildcard query "like"?
Secondly, you are leaving all preferences to default values, which may have an impact on performance, it would be the best that you just have a look at each of the default values mainly for the "worldlist" and "lexer" (find these two on the "Text Reference pdf" somewhere in Oracle chapter indexing.
I did not see clearly you have paralelism enabled in your explain plan but from experience I tell you that parallel queries and Oracle Text is the worst you can have performance-wise ... try this:
select /*+ NOPARALLEL(i) NOPARALLEL(it) */ i.title, i.date_added, it.content
from items i, item_text it
where i.item_id = it.item_id
and i.date_added between :start_time and :end_time
and contains(it.content, 'something') > 0;
Compute statistics on schema level ... using the following:
Connect as the user and execute:
declare
v_definer_user VARCHAR2(100);
begin
select sys_context( 'userenv', 'current_user' )
into v_definer_user
from dual;
dbms_stats.gather_schema_stats(
ownname => v_definer_user , estimate_percent => NULL , degree => 5 , method_opt => 'for all columns for all indexed columns size auto' , cascade => true);end;
If this does not speed up then let me know and I will provide you with index preferences for testing further.
Best Regards,
Giovanni
Received on Wed Apr 13 2005 - 08:57:31 CDT