This table captures information about row sorting actions which occurred during statements that were executed using RUNTIMESTATISTICS with XPLAIN style (see the RUNTIMESTATISTICS section in the Derby Reference Manual for more 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. See "Working with RunTimeStatistics" in the Tuning Derby for additional information.
| Column Name | Type | Length | Nullability | Contents |
|---|---|---|---|---|
| SORT_RS_ID | CHAR | 36 | false | A unique identifier for this row. Matches the corresponding value of SORT_RS_ID in the my_stats.SYSXPLAIN_RESULTSETS row for the result set which required this sort to be performed. |
| SORT_TYPE | CHAR | 2 | true | A code indicating the type of sort that was performed. The code values include 'IN' for an internal sort, and 'EX' for an external sort. I think that an internal sort is one which was entirely performed in-memory and did not overflow to any temporary files, while an external sort used one or more external files. |
| NO_INPUT_ROWS | INTEGER | true | Number of rows which were provided to the sorter. | |
| NO_OUTPUT_ROWS | INTEGER | true | Number of rows which were returned by the sorter. Note that this may be fewer rows than were input, for example when the sorter is performing GROUP BY processing or is eliminating duplicates. | |
| NO_MERGE_RUNS | INTEGER | true | Number of merge runs which were provided. This value will be NULL for an internal sort, but for an external sort it indicates how many times the intermedate sort files were merged together. External sorts are far more expensive than internal sorts, and each additional merge run that an external sort must perform adds considerably more to the overhead of the sort. | |
| MERGE_RUN_DETAILS | VARCHAR | 256 | true | Additional information about the size of the merge runs. This value will be NULL for an internal sort. |
| ELIMINATE_DUPLICATES | CHAR | 1 | true | A code indicating whether or not this sort eliminated duplicates from the input: (Y)es or (N)o. This column only applies for a sort which was NOT performing GROUP BY aggregation; for GROUP BY sorts this column is always NULL. See the DISTINCT_AGGREGATE column for the corresponding information for aggregating sorts. |
| IN_SORT_ORDER | CHAR | 1 | true | A code indicating whether or not the rows which were input to the sorter were already in sort order, which can happen if the rows were retrieved by using an index, or if an earlier phase of processing had already sorted the data. The code is 'Y' for Yes if the rows are already in sorted order, and 'N' for No otherwise. |
| DISTINCT_AGGREGATE | CHAR | 1 | true | A code indicating whether the aggregation process was computing distinct aggregates or not. |