Re: Performance Problem with Full Text Indexing

Hello Validimir,

Thanks for your suggestions and information.

I am thinkging of one index which could be a combination of length of address and address .
Something like this

Create index ind_aa on master(length(address),address) ; --- a simple B-Tree Index

Select * from TempTable A Where Exists
(
Select 1 From MasterTable B Where B.Address Like '%' || A.Address || '%'
And Length(B.Address)>= Length(A.Address) );

Based on the fact that only those ADRESS of row in MASTER can be contained in row from Temp Table if length of MASTER'S ADDRESS column is greater than length of TEMP Table' Address column.

With Warm regards
Jatinder Singh

Vladimir M. Zakharychev wrote:
> "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 Wed May 31 2006 - 00:25:03 CDT