Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Mon, 25 Jul 2005 07:41:18 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Full Text Indexing
In-reply-to: <1cbd6f83050724134413667268@mail.gmail.com>
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <42E4FA0E.9010905@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
References: <1cbd6f83050724134413667268@mail.gmail.com>

Hi Mag,

Thanks for bringing up this issue. It certainly deserves an enhancement 
request. I'll log one later on today.

Technically, you can build your own poor-man's solution to this problem 
today:

1) You can build your own inverted index (as a full-fledged table) on 
the text column you need to search.

2) You can maintain rows in that pseudo-index by hiding all 
insert/update/delete access (to the base table) inside some set of 
application-layer methods.

3) Similarly, you can manage searches by an application-layer query 
generator which knows how to build joins between the base table and the 
inverted index.

A couple years ago I tried something like this with the free-license 
version of Cloudscape which IBM exposed. The performance disappointed 
me. I didn't look deeply into the performance issue. Instead, I tried 
another solution, which outperformed the inverted index: Full table 
scans on the base (text bearing) table supported by a search function 
evaluated, per query, on each text column. To date this hack has 
performed adequately on a dataset of 12K rows. However, it would be 
pretty slow on datasets an order of magnitude larger.

So...we need to log an enhancment request for text search support.

Regards,
-Rick


Mag Gam wrote:

>Is it possible to have searchable data type with indexed access in
>Cloudscape/Derby?
>
>I am looking for something similar to tsearch2
>(http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/)
>  
>