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.
See "Working with RunTimeStatistics" in the Tuning Java DB for additional information.
| 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. |