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.