# Statement execution plan

RUNTIMESTATISTICS also provides information about the statement execution plan. The statement execution plan shows how long each node took to evaluate, how many rows were retrieved, whether an index was used, and so on. If an index was used, it shows the start and stop positions for the matching index scan. Looking at the plan can help you determine whether to add an index or to rewrite the query.

A statement execution plan is composed of a tree of result set nodes. A result set node represents the evaluation of one portion of the statement; it returns rows to a calling (or parent) node and can receive rows from a child node. A node can have one or more children. Starting from the top, if a node has children, it requests rows from the children. Usually only the execution plans of DML statements (queries, inserts, updates, and deletes, not dictionary object creation) are composed of more than one node.

For example, consider the following query:

    SELECT * FROM Countries

This simple query involves one node only-reading all the data out of the Countries table. It involves a single node with no children. This result set node is called a Table Scan ResultSet. RUNTIMESTATISTICS text for this node looks something like this:

    Statement Name:
            null
    Statement Text:
            select * from countries
    Parse Time: 20
    Bind Time: 10
    Optimize Time: 50
    Generate Time: 20
    Compile Time: 100
    Execute Time: 10
    Begin Compilation Timestamp : 2005-05-25 09:16:21.24
    End Compilation Timestamp : 2005-05-25 09:16:21.34
    Begin Execution Timestamp : 2005-05-25 09:16:21.35
    End Execution Timestamp : 2005-05-25 09:16:21.4
    Statement Execution Plan Text:
    Table Scan ResultSet for COUNTRIES at read committed isolation
    level using instntaneous share row
    locking chosen by the optimizer
    Number of opens = 1
    Rows seen = 114
    Rows filtered = 0
    Fetch Size = 16
            constructor time (milliseconds) = 0
            open time (milliseconds) = 0
            next time (milliseconds) = 10
            close time (milliseconds) = 0
            next time in milliseconds/row = 0

    scan information:
            Bit set of columns fetched=All
            Number of columns fetched=3
            Number of pages visited=3
            Number of rows qualified=114
            Number of rows visited=114
            Scan type=heap
            start position:
    null    stop position:
    null    qualifiers:
    None
            optimizer estimated row count:          119.00
            optimizer estimated cost:           69.35

Consider this second, more complex query:

    SELECT Country
    FROM Countries
    WHERE Region = 'Central America'

When executed, this query involves two nodes- one to retrieve qualifying rows (the restriction is done at this node) and one to project the requested columns. So, at bottom, there is a TableScanResultSet for scanning the table. The qualifier (Region = 'Central America') is evaluated in this node. These data are passed up to the parent node, called a Project-Restrict ResultSet, in which the rows are projected-only the country column is needed (the first column in the table). RUNTIMESTATISTICS text for these two nodes looks something like this:

    Statement Name:
            null
    Statement Text:
            SELECT Country FROM Countries WHERE Region = 'Central America'
    Parse Time: 10
    Bind Time: 0
    Optimize Time: 370
    Generate Time: 10
    Compile Time: 390
    Execute Time: 0
    Begin Compilation Timestamp : 2005-05-25 09:20:41.274
    End Compilation Timestamp : 2005-05-25 09:20:41.664
    Begin Execution Timestamp : 2005-05-25 09:20:41.674
    End Execution Timestamp : 2005-05-25 09:20:41.674
    Statement Execution Plan Text:
    Project-Restrict ResultSet (2):
    Number of opens = 1
    Rows seen = 6
    Rows filtered = 0
    restriction = false
    projection = true
            constructor time (milliseconds) = 0
            open time (milliseconds) = 0
            next time (milliseconds) = 0
            close time (milliseconds) = 0
            restriction time (milliseconds) = 0
            projection time (milliseconds) = 0
            optimizer estimated row count:           11.90
            optimizer estimated cost:           69.35

    Source result set:
            Table Scan ResultSet for COUNTRIES at read committed isolation level
    using instantaneous share row locking chosen by the optimizer
            Number of opens = 1
            Rows seen = 6
            Rows filtered = 0
            Fetch Size = 16
                    constructor time (milliseconds) = 0
                    open time (milliseconds) = 10
                    next time (milliseconds) = 0
                    close time (milliseconds) = 0
                    next time in milliseconds/row = 0

            scan information:
                    Bit set of columns fetched={0, 2}
                    Number of columns fetched=2
                    Number of pages visited=3
                    Number of rows qualified=6
                    Number of rows visited=114
                    Scan type=heap
                    start position:
    null            stop position:
    null            qualifiers:
    Column[0][0] Id: 2
    Operator: =
    Ordered nulls: false
    Unknown return value: false
    Negate comparison result: false

                    optimizer estimated row count:           11.90
                    optimizer estimated cost:           69.35

Other, more complex queries such as joins and unions have other types of result set nodes.

For inserts, updates, and deletes, rows flow out of the top, where they are inserted, updated, or deleted. For selects (queries), rows flow out of the top into a result set that is returned to the user.

The Java DB Reference Manual shows the many possible ResultSet nodes that might appear in an execution plan.

In addition, read DML statements and performance, for more information about some of the ways in which Derby executes statements.

Parent topic: Analyzing the information
Related concepts
Statistics timing
Optimizer estimates
Optimizer overrides
Understanding XPLAIN style database tables