- Schema:
For guidance on query performance when using organization-wide usage views, see Performance (Organization Usage).
SEARCH_
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.
The SEARCH_OPTIMIZATION_HISTORY view in the ORGANIZATION_USAGE schema is used for querying maintenance history for indexes maintained by the search optimization service. The information returned by the view includes the index, the base table, and the credits consumed each time a maintenance operation occurred.
Note
The INDEX_ID, INDEX_NAME, INDEX_TYPE, BASE_TABLE_ID, and BASE_TABLE_NAME columns replace TABLE_ID and TABLE_NAME when
SEARCH_OPTIMIZATION_HISTORY views: New columns and column changes (Pending) is enabled. If you have scripts that query TABLE_ID or TABLE_NAME, update them to use
the new column names.
Columns¶
| Column Name | Data Type | Description |
|---|---|---|
| ORGANIZATION_NAME | VARCHAR | Name of the organization where the usage took place. |
| ACCOUNT_NAME | VARCHAR | Name of the account where the usage took place. |
| ACCOUNT_LOCATOR | VARCHAR | Name of the account locator. |
| REGION | VARCHAR | Name of the region where the account is located. |
| USAGE_DATE | DATE | Date (in the UTC time zone) of this usage record. |
| CREDITS_USED | NUMBER | Number of credits billed for search optimization service index maintenance during the USAGE_DATE. |
| INDEX_ID | NUMBER | Internal/system-generated identifier for the index. Replaces TABLE_ID. |
| INDEX_NAME | VARCHAR | Name of the index. Replaces TABLE_NAME. For a search optimization index, this is a system-generated alias of the form SEARCH OPTIMIZATION ON: <base_table_id>. For a search optimization secondary index, this is the customer-defined index name. |
| INDEX_TYPE | VARCHAR | Type of index maintained by the search optimization service. Possible values are Search Optimization Index and Secondary Index. Secondary Index is not a hybrid table secondary index. |
| BASE_TABLE_ID | NUMBER | Internal/system-generated identifier for the base table. |
| BASE_TABLE_NAME | VARCHAR | Name of the base table. |
| SCHEMA_ID | NUMBER | Internal/system-generated identifier for the schema that contains the base table. |
| SCHEMA_NAME | VARCHAR | Name of the schema that contains the base table. |
| DATABASE_ID | NUMBER | Internal/system-generated identifier for the database that contains the base table. |
| DATABASE_NAME | VARCHAR | Name of the database that contains the base table. |
Usage notes¶
- Latency for the view may be up to 24 hours (1 day).
- Use
INDEX_TYPEto distinguish search optimization index maintenance (Search Optimization Index) from search optimization secondary index maintenance (Secondary Index). These values are not hybrid table secondary indexes. INDEX_IDidentifies the specific index.INDEX_NAMEis a system-generated alias for a search optimization index (SEARCH OPTIMIZATION ON: <base_table_id>), or the customer-defined name for a search optimization secondary index.BASE_TABLE_IDandBASE_TABLE_NAMEidentify the table that the index is defined on.