db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Query and uppercases
Date Wed, 26 Apr 2006 16:31:19 GMT
Although this won't help anyone solve the immediate problem immediately, 
this issue would be addressed by DERBY-455 (expression indexes).

Regards,
-Rick

Craig L Russell wrote:

> If you are concerned about performance, please note that there is a  
> huge difference between these two statements:
>
> 1. SELECT * FROM CITY_INFO WHERE UPPER(?) = CITY_NAME
>
> and
>
> 2. SELECT * FROM CITY_INFO WHERE ? = UPPER(CITY_NAME)
>
> Query 1 will work if your data is already upper case. You can do an  
> index scan of the table very quickly.
>
> If your data is stored in mixed case, you need query 2, and this will  
> need to do a table scan because there is no index on the UPPER 
> (CITY_NAME) values. [AFAIK there is no way to implement such an index  
> on the column. So the index needs to be created with the upper case  
> names. If you need mixed case in the database, you might need another  
> column just for the upper case index.
>
> Craig
>
> On Apr 26, 2006, at 8:52 AM, Bryan Pendleton wrote:
>
>>> Is there a way to bypass this, so when querying the database it  
>>> does not look at uppercases or lowercases. è Detroit = detroit =  
>>> dEtRoIt = …
>>
>>
>> Try using the UPPER function:
>>
>> select * from city_info where UPPER(city_name) = 'DETROIT';
>>
>> thanks,
>>
>> bryan
>>
>>
>


Mime
View raw message