- 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.
Use this Organization Usage view to gain a better understanding of data access patterns during query execution.
You can use this view in combination with the COLUMN_QUERY_PRUNING_HISTORY view. For example, you can identify access to target tables by using the TABLE_QUERY_PRUNING_HISTORY view, then identify frequently used columns on those tables by using the COLUMN_QUERY_PRUNING_HISTORY view.
In particular, these views can help you make a more educated choice for clustering keys.
Each row in this view represents the query 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 queries executed, partitions scanned, partitions pruned, rows scanned, rows pruned, and rows matched.
See also TABLE_PRUNING_HISTORY view and Query Pruning.
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 |
|---|---|---|
| INTERVAL_START_TIME | TIMESTAMP_LTZ | Start of the time range (on the hour mark) during which the queries were executed. |
| INTERVAL_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 | 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. |
| WAREHOUSE_ID | NUMBER | Internal/system-generated identifier for the warehouse that was used to run the queries. |
| WAREHOUSE_NAME | VARCHAR | Name of the warehouse that ran the queries. |
| QUERY_HASH | VARCHAR | The hash value computed based on the canonicalized SQL text. |
| QUERY_PARAMETERIZED_HASH | VARCHAR | The hash value computed based on the parameterized query. |
| NUM_QUERIES | NUMBER | Number of queries executed in this time range with this specific QUERY_HASH value, using this warehouse, accessing this table. |
| AGGREGATE_QUERY_ELAPSED_TIME | NUMBER | Total elapsed time (in milliseconds) for queries defined by NUM_QUERIES. This total includes queueing and other time not associated with compilation and execution. |
| AGGREGATE_QUERY_COMPILATION_TIME | NUMBER | Total compilation time (in milliseconds) for queries defined by NUM_QUERIES. |
| AGGREGATE_QUERY_EXECUTION_TIME | NUMBER | Total execution time (in milliseconds) for queries defined by NUM_QUERIES. |
| PARTITIONS_SCANNED | NUMBER | Number of partitions scanned on this table for queries defined by NUM_QUERIES. |
| PARTITIONS_PRUNED | NUMBER | Number of partitions pruned on this table for queries defined by NUM_QUERIES. These partitions were eliminated during query processing and not scanned, improving the efficiency of the query. |
| ROWS_SCANNED | NUMBER | Number of rows scanned on this table for queries defined by NUM_QUERIES. |
| ROWS_PRUNED | NUMBER | Number of rows pruned on this table for queries defined by NUM_QUERIES. These rows were eliminated during query processing and not scanned, improving the efficiency of the query. |
| ROWS_MATCHED | NUMBER | Number of rows that matched the WHERE clause filters while scanning this table for the queries defined by NUM_QUERIES. |
Usage notes¶
- Latency for the view may be up to 6 hours.
- Data is retained for 1 year.
- This view does not include pruning information for hybrid tables.
- For complex filtering conditions that can’t benefit from a pushdown optimization, rows might not be filtered out during the table scan operation, even if they do not match the filtering condition. Therefore, these rows are counted in the ROWS_MATCHED value.
- 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.