Re: Oracle Text Design Question

Thanks for the reply Giovanni,

Comments below:

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:3c4n9aF6hm86dU1_at_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:
>
> - You do not use the Oracle Text score in your queries,
> which is based on the Salton's IR "inverted-frequency" formula.
>
> - You do not make use of any of the Oracle Text-specific
> operators like: stemming, fuzzy, soundex, near, wildcard,
> weighting, schema search nor even boolean logic.
>
> Therefore you are probably better off with an ordinary and
> _standard_ wildcard query "like"?
>

I will be using more complex operators. I submitted the post with a simple example.

> 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.
>

This is something I could really use some help with. I'll look at the references you have made.

> ***** Possible quick solution 1 ********
>
> 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;
>

No change... CBO seems to ignore the hint.

> ***** Possible quick solution 2 ********
>
> 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;
> /

Doing this now... also rebuilding the index before hand, just to be sure. I've taken an export into another schema so I can play with a subset of my data (2 Million rows).
I'll try this suggestion out next and let you know how it goes.

It just seems strange to me, I guess I haven't really grasped the overall concept of how text indexes work.
From what I've read in this doc:
http://www.oracle.com/technology/products/text/x/FAQs/imt_Perf_Faq.html it seems that it is designed to work great for single table queries, especially when restricted by rownum.
What doesn't seem clear to me is how it can work efficiently when joining to other tables or making a query more complex by adding more clauses.

It seems the CTXCAT index gets around this with the structured query parameter, which will even allow you to specify things like 'column in (subquery)' but as I said above, I can't use this as I'm using CLOBs which aren't supported by the catalog index.

Anyway, thanks for the suggestions and any other pointer you have would be really appreciated.

Kevin

>
> ****************************************
>
> 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 - 09:52:39 CDT