SEARCH_OPTIMIZATION_HISTORY¶

Syntax¶

SEARCH_OPTIMIZATION_HISTORY(
      [ DATE_RANGE_START => <constant_expr> ]
      [ , DATE_RANGE_END => <constant_expr> ]
      [ , TABLE_NAME => '<string>' ] )

Arguments¶

All the arguments are optional.

DATE_RANGE_START => constant_expr , . DATE_RANGE_END => constant_expr

The date/time range for which to display the history. For example, if you specify that the start date is 2019-04-03 and the end date is 2019-04-05, then you get data for April 3, April 4, and April 5. (The endpoints are included.)

• If neither a start date nor an end date is specified, the default is the last 12 hours.

• If an end date is not specified, but a start date is specified, then CURRENT_DATE at midnight is used as the end of the range.

• If a start date is not specified, but an end date is specified, then the range starts 12 hours prior to the start of DATE_RANGE_END.

TABLE_NAME => string

The table name. If specified, only shows the history for the specified table. The name can include the schema name and the database name.

If a name is not specified, then the results include the data for each table that has search optimization for which maintenance occurred within the specified time range.

Usage notes¶

• Returns results only for the ACCOUNTADMIN role or any role that has been explicitly granted the MONITOR USAGE global privilege.

  Note

  A role with the MONITOR USAGE privilege can view per-object credit usage, but not object names. The role must also be granted SELECT on an object in order for the object’s name to be returned by this function. If the role does not have sufficient privileges to see the object name, the object name might be displayed with a substitute name such as “unknown_#”, where “#” represents one or more digits.

• When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name must be fully-qualified. For more details, see Snowflake Information Schema.

• The history is displayed in increments of 1 hour.

Output¶

The function returns the following columns:

Examples¶

Retrieve the history for a one-hour range for your account:

select *
  from table(information_schema.search_optimization_history(
    date_range_start=>'2019-05-22 19:00:00.000',
    date_range_end=>'2019-05-22 20:00:00.000'));

Copy

Here is sample output:

+-------------------------------+-------------------------------+--------------+----------------------------------+
| START_TIME                    | END_TIME                      | CREDITS_USED | TABLE_NAME                       |
|-------------------------------+-------------------------------+--------------+----------------------------------|
| 2019-05-22 19:00:00.000 -0700 | 2019-05-22 20:00:00.000 -0700 |  0.223276651 | TEST_DB.TEST_SCHEMA.TEST_TABLE_1 |
+-------------------------------+-------------------------------+--------------+----------------------------------+

Copy

Retrieve the history for the last 12 hours for your account:

select *
  from table(information_schema.search_optimization_history(
    date_range_start=>dateadd(H, -12, current_timestamp)));

Copy

Retrieve the history for the past week for a specified table:

select *
  from table(information_schema.search_optimization_history(
    date_range_start=>dateadd(D, -7, current_date),
    date_range_end=>current_date,
    table_name=>'mydb.myschema.my_table')
    );

Copy

Retrieve the maintenance history for the past week for all tables in your account:

select *
  from table(information_schema.search_optimization_history(
    date_range_start=>dateadd(D, -7, current_date),
    date_range_end=>current_date)
    );

Copy