Re: hint needed to complete a single "tokens deduplication" update statement ...

Hello Morgan,

fyi

Regarding my "Oracle architecture decision" previous post you were willing to help, I found out that all the time in the query was being spent effectively on the fuzzy matching contains query, and changing the fuzzy expansions (500 -> 20) and tolerance (20 -> 70) of the query improved dramatically the performance, factor of  15x faster! so my "prejudice" was correct.

you wrote:

>I'm inclined to agree with your conclusion but not how you got there.
>
>But ... both you and your management ... are in no position to determine
>the best path if all you know is what you posted here. C/C++ might help
>but my guess is only in wasting money. If you don't have the expertise
>necessary to analyze the root cause hire an outside consultant that can:
>Perhaps Jonathan Lewis.
>
>You haven't got an argument ... you have a prejudice. You need to stop
>what you are doing, which appears to be a waste of time, and focus on
>identifying the underlying issue. Only then can a recommendation be
>made as to which way to turn.
>>
>> Once more I thank you very much for your support.
>>
>> Best Regards,
>> Giovanni
>>
>--
>Daniel A. Morgan
>University of Washington
>damorgan_at_x.washington.edu
>(replace 'x' with 'u' to respond)

The info I found right here:

http://www.oracle.com/technology/products/text/x/faqs/imt_perf_faq.html#q11

<quote>
Each distinct word used in a query will require at least one row to be fetched from the $I token table. It is therefore best to keep the number of expansions down as much as possible. You should not use expansions such as wild cards, stemming and fuzzy matching unless they are necessary to the task. In general, a few expansions (say up to 20) is OK, but you should try to avoid more than 100 or so expansions in a query. The query feedback mechanism may be used to establish the number of expansions for any particular query expression. </quote>

This information I could not obtain from the execution plan of the queries, not even using the 10046 tracing event set to level 12, because the high cost was distributed among all auxiliary queries generated by Oracle TEXT achieved vs its internal tables.

Best Regards,
Giovanni Received on Tue Oct 26 2004 - 04:18:45 CDT