Sample Query Plan Reports

The following sections describe sample query plans that you might want to display when analyzing the performance of queries.

Single-Table Query

Figure 53 shows the SET EXPLAIN output for a simple query.

QUERY:
------
SELECT fname, lname, company FROM customer

Estimated Cost: 2
Estimated # of Rows Returned: 28

  1) virginia.customer: SEQUENTIAL SCAN

Figure 54 shows the SET EXPLAIN output for a complex query on the customer table.

QUERY:
------
SELECT fname, lname, company FROM customer
WHERE company MATCHES 'Sport*' AND
    customer_num BETWEEN 110 AND 115
ORDER BY lname

Estimated Cost: 1
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

  1) virginia.customer: INDEX PATH

        Filters: virginia.customer.company MATCHES 'Sport*'

    (1) Index Keys: customer_num   (Serial, fragments: ALL)
        Lower Index Filter: virginia.customer.customer_num >= 110
        Upper Index Filter: virginia.customer.customer_num <= 115

The following output lines in Figure 54 show the scope of the index scan for the second query:

• Lower Index Filter: virginia.customer.customer_num >= 110

  Start the index scan with the index key value of 110.

• Upper Index Filter: virginia.customer.customer_num <= 115

  Stop the index scan with the index key value of 115.

Multitable Query

Figure 55 shows the SET EXPLAIN output for a multiple-table query.

QUERY:
------
SELECT C.customer_num, O.order_num, SUM (I.total_price)
FROM customer C, orders O, items I
WHERE C.customer_num = O.customer_num
AND O.order_num = I.order_num
GROUP BY C.customer_num, O.order_num

Estimated Cost: 78
Estimated # of Rows Returned: 1
Temporary Files Required For: Group By

  1) virginia.o: SEQUENTIAL SCAN

  2) virginia.c: INDEX PATH

    (1) Index Keys: customer_num   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: 
          virginia.c.customer_num = virginia.o.customer_num
NESTED LOOP JOIN

  3) virginia.i: INDEX PATH

    (1) Index Keys: order_num   (Serial, fragments: ALL)
        Lower Index Filter: virginia.o.order_num = virginia.i.order_num
NESTED LOOP JOIN

The SET EXPLAIN output lists the order in which the database server accesses the tables and the access plan to read each table. The plan in Figure 55 indicates that the database server is to perform the following actions:

1. The database server is to read the orders table first.

   Because no filter exists on the orders table, the database server must read all rows. Reading the table in physical order is the least expensive approach.

2. For each row of orders, the database server is to search for matching rows in the customer table.

   The search uses the index on customer_num. The notation Key-Only means that only the index need be read for the customer table because only the c.customer_num column is used in the join and the output, and the column is an index key.

3. For each row of orders that has a matching customer_num, the database server is to search for a match in the items table using the index on order_num.

Key-First Scan

A key-first scan is an index scan that uses keys other than those listed as lower and upper index filters. Figure 56 shows a sample query using a key-first scan.

create index idx1 on tab1(c1, c2);

select * from tab1 where (c1 > 0) and ( (c2 = 1) or (c2 = 2))
Estimated Cost: 4
Estimated # of Rows Returned: 1
 
1) pubs.tab1: INDEX PATH
 
    Filters: (pubs.tab1.c2 = 1 OR pubs.tab1.c2 = 2)
 
    (1) Index Keys: c1 c2   (Key-First)  (Serial, fragments: ALL)
        Lower Index Filter: pubs.tab1.c1 > 0

Even though in this example the database server must eventually read the row data to return the query results, it attempts to reduce the number of possible rows by applying additional key filters first. The database server uses the index to apply the additional filter, c2 = 1 OR c2 = 2, before it reads the row data.

Query Plans for Subqueries

The optimizer can change a subquery to a join automatically if the join provides a lower cost. For example, Figure 57 sample output of the SET EXPLAIN ON statement shows that the optimizer changes the table in the subquery to be the inner table in a join.

QUERY:
------
SELECT company, fname, lname, phone
FROM customer c
WHERE EXISTS(
   SELECT customer_num FROM cust_calls u
      WHERE c.customer_num = u.customer_num)

Estimated Cost: 6
Estimated # of Rows Returned: 7

  1) virginia.c: SEQUENTIAL SCAN

  2) virginia.u: INDEX PATH  (First Row)

    (1) Index Keys: customer_num call_dtime   (Key-Only)  
                                              (Serial, fragments: ALL)
        Lower Index Filter: virginia.c.customer_num = virginia.u.customer_num
NESTED LOOP JOIN  (Semi Join)

For more information on the SET EXPLAIN ON statement, see Query Plan Report.

When the optimizer changes a subquery to a join, it can use several variations of the access plan and the join plan:

• First-row scan

  A first-row scan is a variation of a table scan. When the database server finds one match, the table scan halts.

• Skip-duplicate-index scan

  The skip-duplicate-index scan is a variation of an index scan. The database server does not scan duplicates.

• Semi join

  The semi join is a variation of a nested-loop join. The database server halts the inner-table scan when the first match is found. For more information on a semi join, see Nested-Loop Join.

Query Plans for Collection-Derived Tables

A collection-derived table is a special method that the database server uses to process a query on a collection. To use a collection-derived table, a query must contain the TABLE keyword in the FROM clause of an SQL statement. For more information about how to use collection-derived tables in an SQL statement, see the IBM Informix: Guide to SQL Syntax.

Although the database does not actually create a table for the collection, it processes the data as if it were a table. Collection-derived tables allow developers to use fewer cursors and host variables to access a collection, in some cases.

These SQL statements create a collection column called children:

CREATE ROW TYPE person(name CHAR(255), id INT);
CREATE TABLE parents(name CHAR(255),
id INT,
children LIST(person NOT NULL));

The following query creates a collection-derived table for the children column and treats the elements of this collection as rows in a table:

SELECT name, id
FROM TABLE((SELECT children
FROM parents
WHERE parents.id = 1001)) c_table(name, id);

To complete this query, the database server performs the following steps:

1. Scans the parent table to find the row where parents.id = 1001

   This operation is listed as a SEQUENTIAL SCAN in the SET EXPLAIN output that Figure 58 shows.

2. Reads the value of the collection column called children.
3. Scans the single collection and returns the value of name and id to the application.

   This operation is listed as a COLLECTION SCAN in the SET EXPLAIN output that Figure 58 shows.

QUERY:
------
select name, id
from table((select children
from parents
where parents.id = 1001))
c_table(name, id)

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) lsuto.c_table: COLLECTION SCAN
    Subquery:
    ---------
    Estimated Cost: 1
    Estimated # of Rows Returned: 1

      1) lsuto.parents: SEQUENTIAL SCAN

            Filters: lsuto.parents.id = 1001