Categories:

System Functions (System Information)

SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS

Returns the estimated costs of adding search optimization to a given table.

Syntax

SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('<table_name>')

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.

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 estimated upper boundary for the cost. The actual cost could be lower.

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

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" : "EstimatedUpperBound"                           |
|   }, {                                                                    |
|     "name" : "StorageCosts",                                              |
|     "costs" : {                                                           |
|       "value" : 0.070493,                                                 |
|       "unit" : "TB"                                                       |
|     },                                                                    |
|     "computationMethod" : "EstimatedUpperBound"                           |
|   }, {                                                                    |
|     "name" : "MaintenanceCosts",                                          |
|     "costs" : {                                                           |
|       "value" : 30.296,                                                   |
|       "unit" : "Credits",                                                 |
|       "perTimeUnit" : "MONTH"                                             |
|     },                                                                    |
|     "computationMethod" : "EstimatedUpperBound",                          |
|     "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." |
|   } ]                                                                     |
| }                                                                         |
+---------------------------------------------------------------------------+