> I have a problem with some tables, which seem to have to do with the collation...
>
> 1. case:
> ========
> Database with UTF8 as character set has no default collation, collumns have UNICODE as collation:
>
> CREATE TABLE POSTALCODES
> (
> ID INTEGER NOT NULL COLLATE UNICODE,
> COUNTRY VARCHAR( 3) NOT NULL COLLATE UNICODE,
> ZIPCODE VARCHAR( 30) NOT NULL COLLATE UNICODE,
> CITY VARCHAR( 50) NOT NULL COLLATE UNICODE,
> REGION SMALLINT
> );
>
> ALTER TABLE POSTALCODES ADD CONSTRAINT PK_POSTALCODES PRIMARY KEY(ID);
> CREATE GENERATOR POSTALCODES_PRIMARYKEY;
>
> CREATE ASC INDEX SYS_POSTALCODES_ZIPCODE ON POSTALCODES (ZIPCODE);
> CREATE ASC INDEX SYS_POSTALCODES_CITY ON POSTALCODES (CITY);
>
>
>
> 2. case:
> Database with UTF8 as character set has UNICODE_CI_AI as default collation, collumns have no specific collation:
> CREATE TABLE POSTALCODES
> (
> ID INTEGER NOT NULL,
> COUNTRY VARCHAR( 3) NOT NULL,
> ZIPCODE VARCHAR( 30) NOT NULL,
> CITY VARCHAR( 50) NOT NULL,
> REGION SMALLINT
> );
>
> ALTER TABLE POSTALCODES ADD CONSTRAINT PK_POSTALCODES PRIMARY KEY(ID);
> CREATE GENERATOR POSTALCODES_PRIMARYKEY;
>
> CREATE ASC INDEX SYS_POSTALCODES_ZIPCODE ON POSTALCODES (ZIPCODE);
> CREATE ASC INDEX SYS_POSTALCODES_CITY ON POSTALCODES (CITY);
>
>
> The following query (search string can contain letters too theoretically)
>
> select ID, COUNTRY, ZIPCODE, CITY, REGION
> from POSTALCODES
> where (ID > 0) and (upper(ZIPCODE) like '12345%')
>
> is being executed immediately in the first case, but takes almost 2 minutes in the second case...
>
> If I remove the UPPER part, it is being executed immediately.
> If I specify UNICODE as a differing collation for the ZIPCODE column, it is being executed immediately as well.
>
>
> What is wrong with the second case and how can I fix it?

I'm confused ...

If you have a character field with a case-senstive collation, then
querying for UPPER without an function-based index shouldn't use an index.

If you have a character field with a case-insenstive collation, then my
understanding is that you don't have to use UPPER at all, to perform a
case-insensitive query, thus a regular, non-function-based index on the
field with a case-insensitve collation should be sufficient.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/