ASA SQL User's Guide
  Query Optimization and Execution
    Reading access plans

Text plans

There are two types of text plan: short and long. To choose a plan type in Interactive SQL, open the Options dialog from the Tools menu, and click the Plan tab. To use SQL functions to access the plan, see Accessing the Plan with SQL functions.

Colons separate join strategies 

The following command contains two query blocks: the outer select statement from the sales_order and sales_order_items tables, 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 )

i<seq> JNL o<sales_order> : p<seq>

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 execute.

Short text plan 

The short plan is useful when you want to compare plans quickly. It provides the least amount of information of all the access plan formats, but it provides it on a single line.

In the following example, the plan starts with the word SORT because the ORDER BY clause causes the entire result set to be sorted. The customer table is accessed by its primary key index, also called customer. An index scan is used to satisfy the search condition because the column customer.id is a primary key. The abbreviation JNL indicates that the optimizer is using a nested loops join to process the query. Finally, the sales_order table is accessed using the foreign key index ky_so_customer to find matching rows in the customer table.

For more information about code words used in the plan, see Abbreviations used in the plan.

Long text plan 

The long plan provides a little more information than the short plan, and provides information in a way that is easy to print and view without scrolling down.

In the long plan output for the same query, the first line is Plan [ I/O Estimate: 1 ]. The words Plan or Sub-plan indicate the start of a query block (in this case, there is only one). The I/O estimates how many I/O are required for the query (in this case, one). Again, the plan indicates that the results are sorted, and that a nested loops join is the join algorithm to be used. On the same line as the algorithm, there is either the word TRUE or the search condition and selectivity estimate for the algorithm (in this case, there is none). The WHERE condition is represented on the line starting with FILTER, followed by the search condition, selectivity estimate for the search condition, and source of the selectivity estimate.

For more information about code words used in the plan, see Abbreviations used in the plan.