Contents Index Discovery of exploitable conditions Subquery and function caching

ASA SQL User's Guide
  Query Optimization and Execution
    Semantic query transformations
      Types of semantic transformations

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.


Contents Index Discovery of exploitable conditions Subquery and function caching