page135
118
Then reset your warehouse to X-Small:
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
While the presented information is useful for an individual table query, the approach
cannot be used for joins as the partitions total is the sum of all referenced tables.
Furthermore, you cannot programmatically use the screen output to derive information
from multiple tables; what you need is something more sophisticated.
GET_QUERY_OPERATOR_STATS
Utilizing the same
query_id
from the previous example query, let’s examine the
GET_
QUERY_OPERATOR_STATS
output. Note that this feature is at Public Preview status at the
time of writing.
SELECT *
FROM TABLE ( get_query_operator_stats('01ae63a0-0000-8905-0000-000113
1e50ad'));
Within the returned data set, there are two attributes of interest. Within
OPERATOR_
TYPE
you are looking for the row with
TableScan
, and for the same row you use the
OPERATOR_STATISTICS
attribute to derive the
partitions_total
attribute.
{
"io": {
"bytes_scanned": 2048,
"percentage_scanned_from_cache": 0,
"scan_progress": 1
},
"output_rows": 5,
"pruning": {
"partitions_scanned": 1,
"partitions_total": 1
}
}
ChaPTer 4
MiCro-ParTiTionS