iAnywhere.com - Elimination of unnecessary case translation

Elimination of unnecessary case translation

By default, Adaptive Server Anywhere databases support case-insensitive string comparisons. Occasionally the optimizer may encounter queries where the user is explicitly forcing text conversion through the use of the UPPER() or LOWER() built-in functions when such conversion is unnecessary. Adaptive Server Anywhere will automatically eliminate this unnecessary conversion when the database's collating sequence permits it. Eliminating the case translation may then permit the comparison predicate to be used for indexed retrieval of the corresponding table.

Example

On a case insensitive database, the query

SELECT *
FROM customer
WHERE UPPER(lname) = 'SMITH'

is rewritten internally as

SELECT *
FROM customer
WHERE lname = 'SMITH'

and the optimizer can now consider using an index on customer.lname.