User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 25. Query Optimization       

Semantic query transformations

In order to operate efficiently, Adaptive Server Anywhere usually rewrites your query. It changes it, possibly in several steps, into a new form. It ensures that the new version computes the same result, even though the query is expressed in a new way. In other words, Anywhere rewrites your queries into semantically equivalent, but syntactically different, forms.

Anywhere can perform a number of different rewrite operations. If you read the access plans, you will frequently find that they do not correspond to a literal interpretation of your statement. For example, the optimizer tries as much as possible to rewrite subqueries with joins.The fact that the optimizer has the freedom to rewrite your commands and some of the ways in which it does so, are importance to you.

Example 

Unlike the SQL language definition, some languages mandate strict behavior for AND and OR operations. Some guarantee that the left-hand condition will be evaluated first. If the truth of the entire condition can then be determined, the compiler guarantees that the right-hand condition will not be evaluated.

This arrangement lets you combine conditions that would otherwise require two nested IF statements into one. For example, in C you can test whether a pointer is NULL before you use it as follows. You can replace the nested conditions

if ( X != NULL ) {
   if ( X->var != 0 ) {
      ... statements ...
   }
}

with the more compact expression

if ( X != NULL && X->var != 0 ) {
      ... statements ...
}

Unlike C, SQL has no such rules concerning execution order. Anywhere is free to rearrange the order of such conditions as it sees fit. The reordered form is semantically equivalent because the SQL language specification makes no distinction. In particular, query optimizers are completely free to reorder predicates in a WHERE or HAVING clause.

  Types of semantic transformations

The optimizer can perform a number of transformations in search of more efficient and convenient representations of your query. The following are common manipulations. Because the optimizer performs these transformations, the plan may look quite different than a literal interpretation of your original query.

• unnecessary DISTINCT elimination

• subquery unnesting

• predicate pushdown in UNION or GROUPed views

• join elimination

• optimization for minimum or maximum functions

• OR, in-list optimization

• LIKE optimizations

The following subsections discuss each of these operations.

  Unnecessary DISTINCT elimination

Sometimes a DISTINCT condition is not necessary. For example, the properties of one or more column in your result may contains a UNIQUE condition, either explicitly, or implicitly because it is in fact a primary key.

Examples 

1. The distinct keyword in the following command is unnecessary because the product table contains a primary key. This column is part of the result set.

   SELECT DISTINCT *
   FROM product p

   PLAN> p (seq)

2. Similarly, the result contains the primary keys of both tables so each row in the result must be distinct.

   SELECT DISTINCT o.id, o.cust_id
   FROM sales_order o JOIN customer c
      ON o.cust_id = c.id
   WHERE c.state = 'NY'

   PLAN> c (seq), o (ky_so_customer)

  Subquery unnesting

You may express statements as nested queries, given the convenient syntax provided in the SQL language. However, these can often be more efficiently executed and more effectively optimized if rewritten in the form of joins. By doing so, Anywhere can take better advantage of highly selective conditions in a subquery's WHERE clause.

Examples 

1. The subquery in the following example can match at most one row for each row in the outer block. Because it can match at most one row, Anywhere recognizes that it can convert it to an inner join.

   SELECT *
   FROM sales_order_items s
   WHERE EXISTS
       (   SELECT *
         FROM product p
         WHERE s.prod_id = p.id
            AND p.id = 300 )

   PLAN: p (product): s (ky_prod_id)

   Following conversion, this same statement is expressed using join syntax.

   SELECT *
   FROM product p JOIN sales_order_items s
      ON p.id = s.prod_id
   WHERE p.id = 300

   PLAN> p (product), s (prod-id)

2. Similarly, the following query contains a conjunctive EXISTS predicate in the subquery. This subquery can match more than one row.

   SELECT *
   FROM product p
   WHERE EXISTS
       (   SELECT *
         FROM sales_order_items s
         WHERE s.prod_id = p.id
            AND s.id = 2001 )

   PLAN> p (seq): s (ky_prod_id)

   Anywhere converts this query to a inner join.

   SELECT DISTINCT p.*
   FROM product p JOIN sales_order_items s
      ON p.id = s.prod_id
   WHERE s.id = 2001

   PLAN> TEMPORARY TABLE s (id_fk), p (product)

3. Anywhere can also eliminate subqueries in comparisons, when the subquery can match at most one row for each row in the outer block. Such is the case in the following query.

   SELECT *
   FROM product p
   WHERE p.id =
       (   SELECT s.prod_id
         FROM sales_order_items s
         WHERE s.id = 2001
            AND s.line_id = 1 )

   PLAN> p (seq): s (sales_order_items)

   Anywhere rewrites this query as follows.

   SELECT p.*
   FROM product p, sales_order_items s
   WHERE p.id = s.prod_id
      AND s.id = 2001
      AND s.line_id = 1

   PLAN> s (sales_order_items), p (product)