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." |
|   } ]                                                                     |
| }                                                                         |
+---------------------------------------------------------------------------+