User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 24. Monitoring and Improving Performance       

Search strategies for queries from more than one table

This section uses sample queries to illustrate how the server selects an optimal processing route for each query. If you execute each of the commands in this section in Interactive SQL, the Statistics window display shows you the execution plan chosen to process each query.

Using a key join 

The following simple query uses a key join to search more than one table:

SELECT customer.company_name, sales_order.id
FROM sales_order
KEY JOIN customer

The Statistics window displays the following:

Estimated 711 rows in query (I/O estimate 2)

PLAN> customer(seq), sales_order(ky_so_customer)

When this query is executed, the Interactive SQL Statistics window display indicates that Adaptive Server Anywhere first examines each row in the customer table, then finds the corresponding sales order numbers in the sales_order table using the ky_so_customer foreign key joining the sales_order and customer tables.

The order that the tables are listed in the Statistics window is the order that the tables are accessed by the database.

Adding a WHERE clause 

If you modify the query by adding a WHERE clause, as follows, the search is carried out in a different order:

SELECT customer.company_name, sales_order.id
FROM sales_order
KEY JOIN customer
WHERE sales_order.id = 2583

The Statistics windows displays the following plan:

PLAN> sales_order(sales_order), customer(customer)

Now, Adaptive Server Anywhere looks in the sales_order table first, using the primary key index. Then, for each sales order numbered 2583 (there is only one), it looks up the company_name in the customer table using the customer table primary key to identify the row. The primary key can be used here because the row in the sales_order table is linked to the rows of the customer table by the customer id number, which is the primary key of the customer table.

The tables are examined in a different order depending on the query. The Adaptive Server Anywhere built-in query optimizer estimates the cost of different possible execution plans, and chooses the plan with the least estimated cost.

For some more complicated examples, try the following commands which each join four tables. The Interactive SQL statistics window shows that each query is processed in a different order.

Example 1 

  To list the customers and the sales reps they have dealt with.

1. Type the following:

   SELECT customer.lname, employee.emp_lname
   FROM customer
      KEY JOIN sales_order
      KEY JOIN sales_order_items
      KEY JOIN employee

   lname	emp_lname
   Colburn	Chin
   Smith	Chin
   Sinnot	Chin
   Piper	Chin
   Phipps	Chin

The plan for this query is as follows:

PLAN> employee (seq), sales_order (ky_so_employee_id),
customer (customer), sales_order_items (id_fk)

Example 2 

The following command restricts the results to list all sales reps that the customer named Piper has dealt with:

SELECT customer.lname, employee.emp_lname
FROM customer
   KEY JOIN sales_order
   KEY JOIN sales_order_items
   KEY JOIN employee
WHERE customer.lname = 'Piper'

The plan for this query is as follows:

PLAN> customer (ix_cust_name), sales_order (ky_so_customer),
employee (employee), sales_order_items (id_fk)

Example 3 

The third example shows all customers who have dealt with a sales representative who has the same name that they have:

SELECT customer.lname, employee.emp_lname
FROM customer
   KEY JOIN sales_order
   KEY JOIN sales_order_items
   KEY JOIN employee
WHERE customer.lname = employee.emp_lname

The plan for this query is as follows:

PLAN> employee (seq), customer (ix_cust_name),
sales_order (ky_so_employee_id), sales_order_items (id_fk)

     For information on how the optimizer selects a strategy for each search, see How the optimizer works.