SYSXPLAIN_SORT_PROPS system table

<< SCAN_QUALIFIERS | NO_OUTPUT_ROWS >>

Derby Reference Manual

255

Column Name

Type

Length

Nullable

Contents

HASH_KEY_COLUMN_NUMBERS

VARCHAR

32,672

true

For hash joins, this
column contains
information about which
column is being used to
hash the rows that are
joined.

HASH_TABLE_SIZE

INTEGER

true

For hash joins, this
column contains
information about the
size of the hash table
that will be used to hold
the rows being joined.
This hash table is an
intermediate result,
and will be discarded
at the end of the
query. If the hash table
cannot fit in memory,
it will automatically
spill over to disk.
Since the spillover
to disk can have
significant performance
implications, this value
can provide a clue that
the hash table was
unexpectedly too large
to fit in memory.

SYSXPLAIN_SORT_PROPS system table

The SYSXPLAIN_SORT_PROPS table captures information about row sorting actions
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.

Rows in this table are typically joined with rows in SYSXPLAIN_STATEMENTS and
SYSXPLAIN_RESULTSETS during analysis.

select s.stmt_text, rs.op_identifier,
srt.no_input_rows, srt.no_output_rows
from my_stats.sysxplain_sort_props srt,
my_stats.sysxplain_resultsets rs,
my_stats.sysxplain_statements s
where rs.stmt_id = s.stmt_id and rs.sort_rs_id = srt.sort_rs_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_SORT_PROPS system table.

Table 67.

SYSXPLAIN_SORT_PROPS system table