db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: How to configure Derby to be case-insensitive?
Date Tue, 25 Jan 2005 18:49:58 GMT
I also believe there is no way to get derby to automatically do
case insensitive searching.

Someday I hope that someone builds indexes on a function in derby.
Building the index should not be too hard, I think the more
difficult/interesting work is to get the optimizer to choose the
index, and getting the execution engine to use the index.

Case insensitive search is one example of a feature which could be
addressed by functional indexes.

/mikem

Jack Klebanoff wrote:

> Jonathan Eric Miller wrote:
> 
>> I'm guessing that the answer to this is that you can't. So, I'm
>> guessing you just use the LOWER() function like the following.
>>
>> SELECT * FROM Person WHERE LOWER(firstName) = 'jonathan';
>>
>> However, if you do this, then it won't use an index if you have an
>> index on the column that you are filtering on, correct?
>>
>> Jon
>>
>> ----- Original Message ----- From: "Jonathan Eric Miller"
>> <jemiller@uchicago.edu>
>> To: "Derby User List" <derby-user@db.apache.org>
>> Sent: Monday, January 24, 2005 9:19 AM
>> Subject: Re: How to configure Derby to be case-insensitive?
>>
>>
>>> Does anyone know the answer to this?
>>>
>>> Jon
>>>
>>> ----- Original Message ----- From: "Jonathan Eric Miller"
>>> <jemiller@uchicago.edu>
>>> To: "Derby User List" <derby-user@db.apache.org>
>>> Sent: Thursday, January 20, 2005 3:36 PM
>>> Subject: How to configure Derby to be case-insensitive?
>>>
>>>
>>>> Does anyone know if it's possible to configure Derby so that
>>>> searches are case-insensitive?
>>>>
>>>> Jon
>>>>
>>>
>>
> I do  not think that you can configure Derby so that searches are
> case-insensitive.
> 
> I believe that you are also right that using the LOWER function in the
> WHERE clause will cause a table scan to be used. The index is not used
> because the Btree stores keys in upper and lower case so the matching
> keys are scattered around the Btree. The optimizer might be able to use
> the index by anding in the expression ((firstName >= 'j' AND firstName <
> 'k') OR (firstName >= 'J' AND firstName < 'K'), but I do not think that
> it does this. A project for someone who is interested in working on the
> optimizer.
> 
> A way of making searches case insensitive is for the application to
> convert all data to a canonical case (upper or lower). That is what
> Derby does internally to handle table and column names: it converts all
> unquoted table and column names to upper case.
> 
> Jack
> 

Mime
View raw message