Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Text Design Question

Re: Oracle Text Design Question

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Wed, 13 Apr 2005 15:57:31 +0200
Message-ID: <3c4n9aF6hm86dU1@individual.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US