Statement-level output

The first section of showplan output for each query plan presents some statement-level information. There is always a message giving the statement and line number in the batch or stored procedure of the query for which the query plan was generated:

QUERY PLAN FOR STATEMENT N (at line N).

This message may be followed by a series of messages that apply to the statement’s query plan as a whole. A message about abstract plan usage appears next if the query plan was generated using an abstract plan. The message indicates how the abstract plan was forced.

If an explicit abstract plan was given by a plan clause in the SQL statement, the message is:
```
Optimized using the Abstract Plan in the PLAN clause.
```
If an abstract plan has been internally generated (that is, for alter table and reorg commands that are executed in parallel) the message is:
```
Optimized using the forced options (internally generated Abstract Plan).
```
If an abstract plan has been retrieved from sysqueryplans because automatic abstract plan usage is enabled, the message is:
```
Optimized using an Abstract Plan (ID : N).
```
If the query plan is a parallel query plan, the following message shows the number of processes (coordinator plus worker) that are required to execute the query plan.
```
Executed in parallel by coordinating process and N worker processes.
```
If the query plan was optimized using simulated statistics, this message appears next:
```
Optimized using simulated statistics.
```
Adaptive Server uses a scan descriptor for each database object that is accessed during query execution. Each connection (or each worker process for parallel query plans) has 28 scan descriptors by default. If the query plan requires access to more than 28 database objects, auxiliary scan descriptors are allocated from a global pool. If the query plan uses auxiliary scan descriptors, this message is printed, showing the total number required:
```
Auxiliary scan descriptors required: N
```
This message shows the total number of operators appearing in the query plan:
```
N operator(s) under root
```
The next message shows the type of query for the query plan. For query plans, the query type is select, insert, delete, or update:
```
The type of query is SELECT.
```
A final statement-level message is printed at the end of showplan output if Adaptive Server has been configured to enable resource limits. The message displays the optimizer’s total estimated cost of logical and physical I/O:
```
Total estimated I/O cost for statement N (at line M): X.
```

The following query, with showplan output, shows some of these messages:

1> use pubs2

1> set showplan on

1> select stores.stor_name, sales.ord_num
2> from stores, sales, salesdetail
3> where salesdetail.stor_id = sales.stor_id
4> and stores.stor_id = sales.stor_id
5> plan " ( m_join ( i_scan salesdetailind salesdetail)
6> ( m_join ( i_scan salesind sales ) ( sort ( t_scan stores ) ) ) )"


QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using the Abstract Plan in the PLAN clause.

6 operator(s) under root

The type of query is SELECT.

ROOT:EMIT Operator

    |MERGE JOIN Operator (Join Type: Inner Join)
    | Using Worktable3 for internal storage.
    |  Key Count: 1
    |  Key Ordering: ASC
    |
    |   |SCAN Operator
    |   |  FROM TABLE
    |   |  salesdetail
    |   |  Index : salesdetailind
    |   |  Forward Scan.
    |   |  Positioning at index start.
    |   |  Index contains all needed columns. Base table will not be read.
    |   |  Using I/O Size 2 Kbytes for index leaf pages.
    |   |  With LRU Buffer Replacement Strategy for index leaf pages.
    |
    |   |MERGE JOIN Operator (Join Type: Inner Join)
    |   | Using Worktable2 for internal storage.
    |   |  Key Count: 1
    |   |  Key Ordering: ASC
    |   |
    |   |   |SCAN Operator
    |   |   |  FROM TABLE
    |   |   |  sales
    |   |   |  Table Scan.
    |   |   |  Forward Scan.
    |   |   |  Positioning at start of table.
    |   |   |  Using I/O Size 2 Kbytes for data pages.
    |   |   |  With LRU Buffer Replacement Strategy for data pages.
    |   |
    |   |   |SORT Operator
    |   |   | Using Worktable1 for internal storage.
    |   |   |
    |   |   |   |SCAN Operator
    |   |   |   |  FROM TABLE
    |   |   |   |  stores
    |   |   |   |  Table Scan.
    |   |   |   |  Forward Scan.
    |   |   |   |  Positioning at start of table.
    |   |   |   |  Using I/O Size 2 Kbytes for data pages.
    |   |   |   |  With LRU Buffer Replacement Strategy for data pages.

After the statement level output, the query plan is displayed. The showplan output of the query plan consists of two components:

The names of the operators (some provide additional information) to show which operations are being executed in the query plan.
Vertical bars (the “|” symbol) with indentation to show the shape of the query plan operator tree.