page136
119
You might restate your
GET_QUERY_OPERATOR_STATS
SQL statement to extract only
the
partitions_total
attribute as follows:
SELECT operator_statistics:pruning:partitions_total
FROM TABLE ( get_query_operator_stats('01ae63a0-0000-8905-0000-000113
1e50ad'));
You will not see the
partitions_total
attribute if results are derived from the
cache. The
OPERATOR_TYPE
will show
QUERY_RESULT_REUSE
.
This approach cannot be used for joins as the partitions total if the sum of all
referenced tables and the
OPERATOR_STATISTICS
output differs. I will leave this for your
further investigation.
You can find more information on
GET_QUERY_OPERATOR_STATS
at
https://docs.
snowflake.com/en/sql-reference/functions/get_query_operator_stats
.
system$clustering_depth and system$clustering_information
The remaining option for determining micro-partition counts introduces a
new system call that you will become very familiar with in the next chapter:
system$clustering_depth
.
i deliberately omit a full explanation of
system$clustering_depth
here and
restrict usage to identifying the micro-partition count only.
You can assume your target table has not been clustered; I discuss cluster keys in the
next chapter.
To prove my assumption, let’s omit the column information.
SELECT system$clustering_depth ( 'LINEITEM_BASELINE' );
You should see this error message: “000005 (XX000): Invalid clustering keys or table
LINEITEM_BASELINE is not clustered.”
You can identify the number of micro-partitions within your target by adding a
second parameter containing a single table attribute, as shown next:
SELECT system$clustering_depth ( 'LINEITEM_BASELINE', '(L_COMMENT)' );
ChaPTer 4
MiCro-ParTiTionS