- Schemas:
TABLE_PRUNING_HISTORY View¶
This Account Usage view can be used to determine the efficacy of pruning for all tables. You can use this view to assess the efficiency of pruning and understand how a table’s default (natural) ordering of data affects pruning.
To determine the efficacy of pruning, you can compare the number of partitions pruned (PARTITIONS_PRUNED
) against the
total number of partitions scanned and pruned (PARTITIONS_SCANNED + PARTITIONS_PRUNED
).
You can also use this view to compare the effects on pruning before and after enabling Automatic Clustering and search optimization for a table.
Columns¶
Column Name |
Data Type |
Description |
---|---|---|
START_TIME |
TIMESTAMP_LTZ |
Start of the time range (on the hour mark) during which the queries were executed. |
END_TIME |
TIMESTAMP_LTZ |
End of the time range (on the hour mark) during which the queries were executed. |
TABLE_ID |
NUMBER |
Internal/system-generated identifier for the table that was queried. |
TABLE_NAME |
TEXT |
Name of the table that was queried. |
SCHEMA_ID |
NUMBER |
Internal/system-generated identifier for the schema that contains the table that was queried. |
SCHEMA_NAME |
TEXT |
Name of the schema that contains the table that was queried. |
DATABASE_ID |
NUMBER |
Internal/system-generated identifier for the database that contains the table that was queried. |
DATABASE_NAME |
TEXT |
Name of the database that contains the table that was queried. |
NUM_SCANS |
NUMBER |
Number of scan operations from all queries (including SELECT statements and DML statements) on the table during the START_TIME and END_TIME window. Note that a given query might result in multiple scan operations on the same table. |
PARTITIONS_SCANNED |
NUMBER |
Number of partitions scanned during the scan operations described in |
PARTITIONS_PRUNED |
NUMBER |
Number of partitions pruned during the scan operations described in |
ROWS_SCANNED |
NUMBER |
Number of rows scanned during the scan operations described in |
ROWS_PRUNED |
NUMBER |
Number of rows pruned during the scan operations described in |
Usage Notes¶
Latency for the view may be up to 6 hours.
Examples¶
List the tables that have the worst pruning efficacy within the last 24 hours:
SELECT * FROM snowflake.account_usage.table_pruning_history
WHERE start_time > DATEADD(hour, -24, CURRENT_TIMESTAMP())
ORDER BY partitions_pruned / GREATEST(partitions_scanned + partitions_pruned, 1), partitions_scanned DESC;
The example above uses GREATEST to avoid dividing by zero when the sum of the number of partitions scanned and the number of partitions pruned is zero.