# SYSXPLAIN_RESULTSETS system table

The SYSXPLAIN_RESULTSETS table captures information about each result set which is part of a statement that has been executed using RUNTIMESTATISTICS with XPLAIN style.

See "Working with RunTimeStatistics" in Tuning Derby for information on how to configure this.

Most statements have at least one result set associated with them, and some complex statements may have many result sets associated with them. Some statements, for example DDL statements such as CREATE TABLE, have no result sets associated with them.

Each row in this table describes a particular result set used by a particular statement. Rows in this table are typically joined with rows in SYSXPLAIN_STATEMENTS during analysis:

    select st.stmt_text, rs.op_identifier
        from my_stats.sysxplain_statements st
        join my_stats.sysxplain_resultsets rs
            on st.stmt_id = rs.stmt_id

Rows in this table are added automatically when Derby has been configured appropriately. The rows remain in the table until you delete them or drop the table.

The following table shows the contents of the SYSXPLAIN_RESULTSETS system table.

Table 1. SYSXPLAIN_RESULTSETS system table
Column Name | Type | Length | Nullable | Contents
--- | --- | --- | --- | ---
RS_ID | CHAR | 36 | false | A unique identifier for this particular row.
OP_IDENTIFIER | VARCHAR | 32,672 | false | A code indicating what type of result set these statistics are for. Common result set types include TABLESCAN, INDEXSCAN, and PROJECTION.
OP_DETAILS | VARCHAR | 32,672 | true | Additional string information which varies for each different type of result set. Interpreting this information currently requires reading the Derby source code to know what values are being displayed here.
NO_OPENS | INTEGER | 10 | true | Number of times this result set was opened during execution of the containing statement.
NO_INDEX_UPDATES | INTEGER | 10 | true | The number of index updates performed by this result set. This value is NULL for result sets used by queries, but may have a non-zero value for modification statements such as INSERT, UPDATE, or DELETE.
LOCK_MODE | CHAR | 2 | true | A code indicating the locking level that was used for this result set: 'EX' for exclusive table-level locking, 'SH' for share table-level locking, 'IX' for exclusive row-level locking, or 'IS' for share row-level locking.
LOCK_GRANULARITY | CHAR | 1 | true | A code indicating the locking granularity that was used for this result set: 'T' for table-level locking, or 'R' for row-level locking.
PARENT_RS_ID | CHAR | 36 | true | The result sets for a particular statement are arranged in a parent-child tree structure. The output rows from one result set are delivered as the input rows to its parent. This column stores the identifier of the parent result set. For the outermost result set in a particular statement, this column is NULL. Note that sometimes there are multiple result sets with the same parent result set (that is, some nodes have multiple children): for example, a UNION result set will have two child result sets, representing the two sets of rows which are UNIONed together.
EST_ROW_COUNT | DOUBLE | 52 | true | The optimizer's estimate of the total number of rows for this result set.
EST_COST | DOUBLE | 52 | true | The optimizer's estimated cost for this result set. The value indicates the number of milliseconds that the optimizer estimates it will take to process this result set.
AFFECTED_ROWS | INTEGER | 10 | true | This column is non-null only for INSERT, UPDATE, and DELETE result sets. For those result sets, this column holds the number of rows which were inserted, updated, or deleted, respectively.
DEFERRED_ROWS | CHAR | 1 | true | This column is only non-null for INSERT, UPDATE, and DELETE result sets. For those result sets, this column holds 'Y' if the INSERT/UPDATE/DELETE is being performed using deferred change semantics, and holds 'N' otherwise. Deferred change semantics are used when self-referencing is taking place.
INPUT_ROWS | INTEGER | 10 | true | This column is used for SORT, AGGREGATE, and GROUPBY result sets, and indicates the number of rows that were input to the result set, and thus were sorted by the sorter.
SEEN_ROWS | INTEGER | 10 | true | For join and set nodes, this is the number of rows seen by the "left" side of the processing. For aggregate, group, sort, normalize, materialize, and certain other nodes, this is the number of rows seen.
SEEN_ROWS_RIGHT | INTEGER | 10 | true | For join and set nodes, this is the number of rows seen by the "right" side of the processing. For example, in the statement
    select country
      from countries
    union
    select country
      from countries
      where region = 'Africa'
the UNION result set has SEEN_ROWS = 6 and SEEN_ROWS_RIGHT = 19.
FILTERED_ROWS | INTEGER | 10 | true | This column holds the number of rows which were eliminated from the result set during processing.
RETURNED_ROWS | INTEGER | 10 | true | This column holds the number of rows which were returned by the result set to its caller. Generally speaking, the number of returned rows is the number of rows INPUT or SEEN, minus the number of rows FILTERED.
EMPTY_RIGHT_ROWS | INTEGER | 10 | true | This column is used for left outer joins, and, if not null, holds the number of empty rows which had to be constructed because no existing rows met the join criteria.
INDEX_KEY_OPT | CHAR | 1 | true | This column records when the Index Key Optimization is used. The Index Key Optimization is a special optimization which occurs when a query references the MAX or MIN value of a column which happens to have an index, and so the MIN or MAX computation can be performed by fetching the first or last, respectively, entry in the index, as in:
    select max(country_iso_code)
      from countries
SCAN_RS_ID | CHAR | 36 | true | If this resultset is one of the resultset types which performs a scan of a table or index, this column contains the id value which identifies the particular row in SYSXPLAIN_SCAN_PROPS that describes the statistics related to the scan behavior.
SORT_RS_ID | CHAR | 36 | true | If this resultset is one of the resultset types which performs a sort of a table or index, this column contains the id value which identifies the particular row in SYSXPLAIN_SORT_PROPS that describes the statistics related to the sort behavior. The most common situations which involve sorting of the data are when processing the ORDER BY and GROUP BY clauses.
STMT_ID | CHAR | 36 | false | This column will contain the ID value which identifies the particular statement for which this result set was executed. Note that there may be multiple result sets executed for a single statement, so a join between the SYSXPLAIN_STATEMENTS table and the SYSXPLAIN_RESULTSETS table may retrieve multiple rows.
TIMING_ID | CHAR | 36 | true | If statistics timings were not being captured, this column will have a NULL value. If statistics timings were being captured, this column will contain the id value which can be used as a foreign key to join with the SYSXPLAIN_RESULTSET_TIMINGS row which has the timing information for this resultset.

Parent topic: XPLAIN style tables