Categories:

System Functions (System Information)

SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS

Returns the estimated costs of adding search optimization to a given table and configuring specific columns for search optimization.

Important

The values returned by the SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS function are best-effort estimates based on sampling a partial dataset in the table. The estimated costs can vary significantly (up to 50% or, in rare cases, by several times) from the actual realized costs.

Syntax

SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('<table_name>' [ , <search_method_with_target> ])

Arguments

table_name

Table for which you want to estimate the search optimization costs.

If the table name is not fully-qualified (in the form of db_name.schema_name.table_name or schema_name.table_name), the function looks for the table in the current schema for the session.

Note that the entire name must be enclosed in single quotes.

search_method_with_target

Specifies a search method and target for a column configuration.

Output

The function returns a JSON object with the properties described below:

Property

Description

tableName

Name of the table.

searchOptimizationEnabled

true if the search optimization property is set for the table; false otherwise.

costPositions

Array of objects that describe the predicted costs of adding search optimization to the table.

Each object in the costPositions array represents a different type of cost estimate:

...
"costPositions" : [
  {
    "name" : "BuildCosts",
    ...
  }, {
    "name" : "StorageCosts",
    ...
  }, {
    "name" : "Benefit",
    ...
  }, {
    "name" : "MaintenanceCosts",
    ...
  }
]
...

The name property identifies the type of cost represented by the object. name can be one of the following:

name of object in costPositions

Description

BuildCosts

This object describes the predicted costs of building the search access path for the table. If search optimization has already been added to the table, this object contains no cost information.

StorageCosts

This object describes the predicted amount of storage space (in TB) needed for the search access path for the table. . If search optimization has already been added to the table, this object shows the current amount of space used by the search access path.

Benefit

This object does not contain any cost information at this time.

MaintenanceCosts

This object describes the predicted costs of maintaining the search access path for the table. If this table has been created recently, this object does not contain any cost information.

Each object in the costPositions array can have the following properties:

Property

Description

name

Name that identifies the type of cost information represented by this object.

costs

Object that describes the predicted costs in terms of the following properties:

value

Amount of the predicted cost.

unit

Unit of measure for the cost (e.g., “Credits” for compute costs, “TB” for storage costs, etc.).

perTimeUnit

For maintenance costs, the unit of time that the estimated cost covers (for example, "MONTH" for the cost per month).

computationMethod

Method used to estimate the costs, if multiple methods are available.

comment

Additional information about the estimated cost.

Usage Notes

  • For the build cost, this function returns an approximation based on building search access paths for a sample of the data in the specified table.

  • For the maintenance cost, this function bases the estimates on recent changes made to the table (the changes to bytes over time).

  • In order to call the function, you must have a warehouse in use. If no warehouse is currently in use, the function reports the following error:

    No active warehouse selected in the current session.
    Select an active warehouse with the 'use warehouse' command.
    

    To execute this function, you can use an X-Small warehouse. The warehouse size has no effect on the speed and performance of this function.

  • Because the function uses a warehouse, you are billed for warehouse usage for this function.

  • The function can take somewhere in the range of 20 seconds to 10 minutes to complete.

    As noted above, using a larger warehouse size does not result in the faster execution of this function.

Examples

The following example shows the estimated costs of adding search optimization to a table:

select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('TABLE_WITHOUT_SEARCH_OPT');

+---------------------------------------------------------------------------+
| SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('TABLE_WITHOUT_SEARCH_OPT')     |
|---------------------------------------------------------------------------|
| {                                                                         |
|   "tableName" : "TABLE_WITHOUT_SEARCH_OPT",                               |
|   "searchOptimizationEnabled" : false,                                    |
|   "costPositions" : [ {                                                   |
|     "name" : "BuildCosts",                                                |
|     "costs" : {                                                           |
|       "value" : 11.279,                                                   |
|       "unit" : "Credits"                                                  |
|     },                                                                    |
|     "computationMethod" : "Estimated",                                    |
|     "comment" : "estimated via sampling"                                  |
|   }, {                                                                    |
|     "name" : "StorageCosts",                                              |
|     "costs" : {                                                           |
|       "value" : 0.070493,                                                 |
|       "unit" : "TB"                                                       |
|     },                                                                    |
|     "computationMethod" : "Estimated",                                    |
|     "comment" : "estimated via sampling"                                  |
|   }, {                                                                    |
|     "name" : "MaintenanceCosts",                                          |
|     "costs" : {                                                           |
|       "value" : 30.296,                                                   |
|       "unit" : "Credits",                                                 |
|       "perTimeUnit" : "MONTH"                                             |
|     },                                                                    |
|     "computationMethod" : "Estimated",                                    |
|     "comment" : "Estimated from historic change rate over last ~11 days." |
|   } ]                                                                     |
| }                                                                         |
+---------------------------------------------------------------------------+

The following example shows the output of this function for a table that already has search optimization:

select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('TABLE_WITH_SEARCH_OPT');
+---------------------------------------------------------------------------+
| SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('TABLE_WITH_SEARCH_OPT')        |
|---------------------------------------------------------------------------|
| {                                                                         |
|   "tableName" : "TABLE_WITH_SEARCH_OPT",                                  |
|   "searchOptimizationEnabled" : true,                                     |
|   "costPositions" : [ {                                                   |
|     "name" : "BuildCosts",                                                |
|     "computationMethod" : "NotAvailable",                                 |
|     "comment" : "Search optimization is already enabled."                 |
|   }, {                                                                    |
|     "name" : "StorageCosts",                                              |
|     "costs" : {                                                           |
|       "value" : 0.052048,                                                 |
|       "unit" : "TB"                                                       |
|     },                                                                    |
|     "computationMethod" : "Measured"                                      |
|   }, {                                                                    |
|     "name" : "Benefit",                                                   |
|     "computationMethod" : "NotAvailable",                                 |
|     "comment" : "Currently not supported."                                |
|   }, {                                                                    |
|     "name" : "MaintenanceCosts",                                          |
|     "costs" : {                                                           |
|       "value" : 30.248,                                                   |
|       "unit" : "Credits",                                                 |
|       "perTimeUnit" : "MONTH"                                             |
|     },                                                                    |
|     "computationMethod" : "EstimatedUpperBound",                          |
|     "comment" : "Estimated from historic change rate over last ~11 days." |
|   } ]                                                                     |
| }                                                                         |
+---------------------------------------------------------------------------+