db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <kleba...@Mutagen.Net>
Subject Re: How to configure Derby to be case-insensitive?
Date Mon, 24 Jan 2005 19:15:01 GMT
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