SYSXPLAIN_RESULTSETS system table

This table captures information about each result set which is part of a statement that has been executed using RUNTIMESTATISTICS with XPLAIN style (see the RUNTIMESTATISTICS section in the Java DB Reference Manual for more 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.

Tuning Java DB contains general information about how to use XPLAIN style and the XPLAIN database tables.

Column Name Type Length Nullability Contents
RS_ID CHAR 36 false A unique identifier for this particular row.
OP_IDENTIFIER VARCHAR 128 false A code indicating what type of result set these statistics are for. Common result set types include: TABLESCAN, INDEXSCAN, PROJECTION, etc. Should I try to list all the result set types here?
OP_DETAILS VARCHAR 256 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   true Number of times this result set was opened during execution of the containing statement.
NO_INDEX_UPDATES INTEGER   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, as follows: 'EX'=Exclusive table-level locking, 'SH'=Share table-level locking, 'IX'=Exclusive row-level locking, 'IS'=Share row-level locking.
LOCK_GRANULARITY CHAR 1 true A code indicating the locking granularity that was used for this result set, as follows: 'T'=Table-level locking, 'R'=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   true The optimizer's estimate of the total number of rows for this result set.
EST_COST DOUBLE   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   true This column is only non-null 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 The 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. I think that deferred change semantics are used when there is self-referencing going on, and we must avoid the "Halloween" problem of processing the rows multiple times.
INPUT_ROWS INTEGER   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   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   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 where region = 'Central America' 
        union 
        select country from countries where region = 'Africa'
, the UNION result set has SEEN_ROWS = 6 and SEEN_ROWS_RIGHT=19.
FILTERED_ROWS INTEGER   true This column holds the number of rows which were eliminated from the result set during processing.
RETURNED_ROWS INTEGER   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   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.