Schemas:: ACCOUNT_USAGE

TABLE_PRUNING_HISTORY view¶

This Account Usage view can be used to determine the efficiency of pruning for all tables, and to understand how a table’s default (natural) ordering of data affects pruning.

You can compare the number of partitions pruned (PARTITIONS_PRUNED) to 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 `NUM_SCANS`.
PARTITIONS_PRUNED	NUMBER	Number of partitions pruned during the scan operations described in `NUM_SCANS`.
ROWS_SCANNED	NUMBER	Number of rows scanned during the scan operations described in `NUM_SCANS`.
ROWS_PRUNED	NUMBER	Number of rows pruned during the scan operations described in `NUM_SCANS`.

Usage notes¶

Latency for the view may be up to 6 hours.
This view does not include pruning information for hybrid tables.

Examples¶

List the top five tables that had the worst pruning efficiency within the last seven days:

SELECT
    table_id,
    ANY_VALUE(table_name) AS table_name,
    SUM(num_scans) AS total_num_scans,
    SUM(partitions_scanned) AS total_partitions_scanned,
    SUM(partitions_pruned) AS total_partitions_pruned,
    SUM(rows_scanned) AS total_rows_scanned,
    SUM(rows_pruned) AS total_rows_pruned
  FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_PRUNING_HISTORY
  WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  GROUP BY table_id
  ORDER BY
    total_partitions_pruned / GREATEST(total_partitions_scanned + total_partitions_pruned, 1),
    total_partitions_scanned DESC
  LIMIT 5;

Copy

+----------+----------------+-----------------+--------------------------+-------------------------+--------------------+-------------------+
| TABLE_ID | TABLE_NAME     | TOTAL_NUM_SCANS | TOTAL_PARTITIONS_SCANNED | TOTAL_PARTITIONS_PRUNED | TOTAL_ROWS_SCANNED | TOTAL_ROWS_PRUNED |
|----------+----------------+-----------------+--------------------------+-------------------------+--------------------+-------------------|
|   308226 | SENSOR_DATA_TS |              11 |                       21 |                       1 |           52500000 |           2500000 |
|   185364 | MATCH          |              16 |                       14 |                       2 |             240968 |             34424 |
|   209932 | ORDER_HEADER   |               2 |                      300 |                      56 |          421051748 |          75350790 |
|   209922 | K7_T1          |             261 |                      261 |                      52 |              30421 |              3272 |
|   338948 | SENSOR_DATA_TS |               9 |                       15 |                       3 |           38880000 |           8035200 |
+----------+----------------+-----------------+--------------------------+-------------------------+--------------------+-------------------+

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.