Performance Problem with FULL TEXT Indexing

Hello All,

I am trying to find duplicate rows that exists in one table against all

the rows in another table.
The table with 10000 Rows is to be compared with master table having 30,00,000 Rows .
Structure is somewhat

temptable
Cname varchar(100)
address varchar(400)
......

mastertable
Cname varchar(100)
address varchar(400) --- an index is created of type ctxcat

The query is written as

Select * from temptable A where exists
(

Select 1 from mastertable B where catsearch(B.address,A.address,NULL)>0

);

The query goes on and on ....
Plan shows that Domain Index will be used but the performance is pathetic.
I was first using "LIKE '%aaa'%' sort of predicate its performance was poor so I shifted to
full text index search.
Is there any way these searches for "Like %" can be improved ?? Any help is greatly appreciated.

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 Received on Fri Jun 02 2006 - 00:08:32 CDT