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

Reading access plans

The optimizer can tell you the plan it has chosen in response to any statement. If you are using Interactive SQL, you can simply look to the Statistics window. Otherwise, you can use the PLAN function to ask Anywhere to return a plan

Commas separate tables within a join strategy 

Join strategies in plans appear as a list of correlation names. Each correlation name is followed immediately, in brackets, by the method to be used to locate the required rows. This method is either the word seq, which indicates that the table is to be scanned sequentially, or it is the name of an index. The name of a primary index is the name of the table.

The following self-join creates a list of employees and their managers.

SELECT e.emp_fname, m.emp_fname
FROM employee AS e JOIN employee AS m
   ON e.manager_id = m.emp_id

PLAN> e (seq), m (employee)

To compute this result, Adaptive Server Anywhere will first access the employee table sequentially. For each row, it will access the employee table again, but this time using the primary index.

Temporary tables 

To execute some query results, Adaptive Server Anywhere must use a temporary table, or may choose to use one to lower the overall cost of computing the result. When a temporary table will be used for a join strategy, the words TEMPORARY TABLE precede the description of that strategy.

SELECT DISTINCT quantity
FROM sales_order_items

PLAN> TEMPORARY TABLE sales_order_items (seq)

A temporary table is necessary in this case to compute the distinct quantities.

Colons separate join strategies 

The following command contains two query blocks: the outer select statement from the sales_order_items table, and the subquery that selects from the product table.

SELECT *
FROM sales_order AS o
   KEY JOIN sales_order_items AS i
WHERE EXISTS
   (   SELECT *
      FROM product p
      WHERE p.id = 300 )

PLAN> o (seq), i (id_fk): p (product)

Colons separate join strategies. Plans always list the join strategy for the main block first. Join strategies for other query blocks follow. The order of join strategies for these other query blocks may not correspond to the order in your statement nor to the order in which they will be executed.

In this case, the optimizer has decided to first access o, the sales_order table, sequentially and join it to i, the sales_order_items table, using the foreign key index (contained in the primary index of the product table). At some point, rows from p, the product table, will be located using the primary index.

The optimizer can rewrite your query 

When the optimizer discovers a more efficient means of computing your result, the access plan may not appear to follow the structure of your query. Adding a condition to the subquery in the previous command causes the optimizer to choose a different strategy.

SELECT *
FROM sales_order AS o
   KEY JOIN sales_order_items AS i
WHERE EXISTS
   (   SELECT *
      FROM product p
      WHERE p.id = 300
         AND p.id = i.prod_id )

PLAN> p (product), i (ky_prod_id), o (sales_order)

The optimizer rewrites this command as a single query block that consists of single join between three tables.

     For more information about the rules Adaptive Server Anywhere obeys when rewriting your query, see Rewriting sub-queries as exists predicates and Semantic query transformations.