- Schema:
For guidance on query performance when using organization-wide usage views, see Performance (Organization Usage).
TABLE_
Important
This view is only available in the organization account. For more information, see Premium views in the organization account.
Organization Usage performance
When you query a specific view in the SNOWFLAKE.ORGANIZATION_USAGE schema, follow the organization-wide guidance in
Performance (Organization Usage): bound every scan on history views, list
columns explicitly, and use the time filter column table plus worked SQL and anti-patterns there.
This Organization 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).
Each row in this view represents the pruning history for a specific table within a given time interval. The data is aggregated by time interval and includes information about the number of scans, partitions scanned, partitions pruned, rows scanned, and rows 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.
See also TABLE_QUERY_PRUNING_HISTORY view and COLUMN_QUERY_PRUNING_HISTORY view.
Columns¶
Organization-level columns
| Column Name | Data Type | Description |
|---|---|---|
| ORGANIZATION_NAME | VARCHAR | Name of the organization. |
| ACCOUNT_LOCATOR | VARCHAR | System-generated identifier for the account. |
| ACCOUNT_NAME | VARCHAR | User-defined identifier for the account. |
Additional 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 and completed. |
| END_TIME | TIMESTAMP_LTZ | End of the time range (on the hour mark) during which the queries were executed and completed. |
| TABLE_ID | NUMBER | Internal/system-generated identifier for the table that was queried. |
| TABLE_NAME | VARCHAR | 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 | VARCHAR | 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 | VARCHAR | 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 for the queries described in NUM_SCANS. These partitions were eliminated during query processing, improving the efficiency of the query. |
| ROWS_SCANNED | NUMBER | Number of rows scanned during the scan operations described in NUM_SCANS. |
| ROWS_PRUNED | NUMBER | Number of rows pruned for the queries described in NUM_SCANS. These rows were eliminated during query processing, improving the efficiency of the query. |
Usage notes¶
- Latency for the view may be up to 8 hours.
- This view does not include pruning information for hybrid tables.
- This view retains data for the 1,000 longest-running table scans per query. Only extremely complex queries exceed this number of scans so data is rarely omitted.