<< TEMP_CONG_CREATE_TIME | NO_QUALIFIED_ROWS >>

SYSXPLAIN_SCAN_PROPS system table

<< TEMP_CONG_CREATE_TIME | NO_QUALIFIED_ROWS >>

Derby Reference Manual

252

The SYSXPLAIN_SCAN_PROPS table captures information about table/index accesses
which occurred during statements that were executed using RUNTIMESTATISTICS with
XPLAIN style.

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

Each row in this table describes a single table/index scan for a particular result set
used by a particular statement. Rows in this table are typically joined with rows in
SYSXPLAIN_STATEMENTS and SYSXPLAIN_RESULTSETS during analysis:

select st.stmt_text, sp.no_visited_rows
from my_stats.sysxplain_scan_props sp,
my_stats.sysxplain_resultsets rs,
my_stats.sysxplain_statements st
where st.stmt_id = rs.stmt_id and
rs.scan_rs_id = sp.scan_rs_id and
rs.op_identifier = 'TABLESCAN' and
sp.scan_object_name = 'COUNTRIES'

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_SCAN_PROPS system table.

Table 66.

SYSXPLAIN_SCAN_PROPS system table

Column Name

Type

Length

Nullable

Contents

SCAN_RS_ID

CHAR

false

A unique identifer
for this particular
row. Referenced
by the foreign key
SCAN_RS_ID in SYSXPLAIN_RESULTSETS.

SCAN_OBJECT_NAME

VARCHAR

128

true

The name of the object
being scanned. If this
is a scan of a table or
index, the table name
or index name appears
here. If this is a scan
of the internal index
created for a constraint,
the constraint name
appears here. For
complex join queries,
the object being
scanned may be an
intermediate result,
in which case a
description such as
'Temporary HashTable'
appears.

SCAN_OBJECT_TYPE

CHAR

false

A code indicating the
type of object being
scanned. Codes include
'T' for Table, 'I' for
Index, and 'C' for
Constraint.