Re: Performance Problem with Full Text Indexing

"jsfromynr" <jatinder.1975_at_gmail.com> wrote in message news:1148972939.948894.129500_at_i40g2000cwc.googlegroups.com...
> Thanks Validimar,
>
> I specified NULL as there was no mixed query . The search was only on
> the specified column .
> I had choosen CTXCAT as it is transactional so indexes will be up to
> date, which would not be the case with CONTEXT.
>

You can create transactional CONTEXT indexes in 10g.

> I might be asking for more but could you explain "how parse searching
> happens in case fulltext index is used" ??
> In case of keywords , I assume this is what Oracle does.
> rowid column1
> 1 This is a big box.
> 2 This is not a big box.
>
> Full text Index would hold (my assumption can be totally wrong)
> This 1
> This 2
> is 1
> is 2
> a 1
> a 2
> big 1
> big 2
> not 2
> box 1
> box 2
> Now if predicate is " catsearch(column1,'"is box"',NULL)>0 " --- Again
> I used NULL as it is no mixed query and I wish to have transcational
> index management .
> Does oracle also stores keyword position in the index as well??
>

Distance between tokens is stored in the index somehow. The query above looks for documents with both "is" and "box" tokens where distance between them is 0. As a matter of fact, "is" is a stopword, so it will not be in the index by default. Anyway, I think the problem with your use of catsearch() is that you use A.address as text query without modifying it (enclosing it into double quotes, for example,) and the query becomes too complex. Using your own example above, catsearch(column1, 'is box', null) will look for documents with both "is" and "box" regardless distance between them. The more tokens you have in an address, the more complex the resulting text query will become. Try enclosing A.address in double quotes, like this:

where catsearch(B.address, '"'||A.address||'"', null) > 0

Also, make sure your index is synchronized, otherwise Oracle will spent a lot of time indexing unsynchronized rows in memory.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue May 30 2006 - 07:02:39 CDT