Skip to main content
Skip table of contents

Optimization Management

The Data Virtuality Server has extensive possibilities for optimizing queries and supports three types of optimizations: MAT_TABLE, MAT_JOIN, and MAT_AGGR. For more information, please refer to Optimization Subsystem.

SYSADMIN.enableOptimization

This procedure enables an optimization:

SQL
SYSADMIN.enableOptimization(IN id biginteger, IN enable boolean NOT NULL, IN materializeNow boolean NOT NULL, IN uuid string)

It takes the following parameters:

ParameterDescription
idID of the RecommendedOptimization row
enable

Can take one of the following values:

  • TRUE: materialized data is used
  • FALSE: original data is used
materializeNowIf set to TRUE, the procedure transfers the data to the analytical storage immediately without creating a schedule for the appropriate job
uuidUUID of the recommended optimization
The id or uuid must be specified.

SYSADMIN.cleanUpStaleMatTables

This procedure runs the cleaner to delete stale materialized tables from Analytical Storage:

SQL
SYSADMIN.cleanUpStaleMatTables(IN stagesToKeep integer, IN daysToKeep integer)

It takes the following parameters:

ParameterDescription
stagesToKeepNumber of old stages to keep
daysToKeepNumber of days to keep

Both parameters are optional. If they are omitted, the system will not keep any stages except the most recent one.

SYSADMIN.cleanOrphanMatTableEntities

This procedure deletes all the entries for which recommended optimization does not exist in SYSADMIN.RecommendedOptimizations from the SYSADMIN.MaterializedTable table without physically dropping materialized tables in the Analytical Storage.

SQL
SYSADMIN.cleanOrphanMatTableEntities()

SYSADMIN.copyOver

This procedure copies data from a source table into a target table:

SQL
SYSADMIN.copyOver(IN dwh_table string, IN source_table string, IN cleanup_method string)

It takes the following parameters:

ParameterDescription
dwh_tableTarget table in the data warehouse
source_tableSource table
cleanup_method

Can take one of the following values:

  • DROP: target table is dropped and recreated
  • DELETE: content of the target table is deleted before the copy is created

SYSADMIN.createCopyOverSourceTableJob

This procedure creates a scheduler job to copy data from a source table into a target table.

SQL
SYSADMIN.createCopyOverSourceTableJob(IN sourceTable string NOT NULL, IN targetTable string NOT NULL, IN cleanupMethod string NOT NULL, IN description string, IN uuid string, OUT jobid biginteger NOT NULL RESULT)

It takes the following parameters:

ParameterDescription
sourceTableSource table
targetTableTarget table
cleanupMethod

Can take one of the following values:

  • DROP: target table is dropped and recreated
  • DELETE: content of the target table is deleted before the copy is created
descriptionDescription of the job
uuidCustom UUID of the job to be created

It returns the jobId of the newly created job.

SYSADMIN.createOptimization

This procedure creates an optimization using the given matchDescriptor or SELECT query:

SQL
SYSADMIN.createOptimization(IN matchDescriptorOrSelectQuery string NOT NULL, OUT optimizationID biginteger NOT NULL RESULT, OUT optimizationType string NOT NULL)

Example

SQL
SELECT * FROM table(CALL SYSADMIN.createOptimization('test.test_a')) a

SYSADMIN.createOptimizationQuery

This procedure recreates the SELECT query from a given MAT_AGGR recoptId:

SQL
SYSADMIN.createOptimizationQuery(IN optimizationId biginteger NOT NULL, OUT query string NOT NULL)

SYSADMIN.deleteOptimization

This procedure deletes the following:

  • the optimization itself
  • all associated schedules
  • all relevant tables in analytical storage.

In short, it does a complete cleanup of the optimization.

SQL
SYSADMIN.deleteOptimization(IN id biginteger, IN uuid string)
The id or uuid must be specified.

Examples

1. Deleting all optimizations

SQL
CALL SYSADMIN.deleteOptimization(-1)

2. Deleting just one optimization by id:

SQL
CALL SYSADMIN.deleteOptimization(5)

3. Deleting just one optimization by uuid:

SQL
CALL SYSADMIN.deleteOptimization('ddaa2a0c-c9a4-11ee-9776-00155de68115')

SYSADMIN.importOptimization

This procedure imports an optimization (as in the old system, reusing precisely the same matTablePrefix, etc.). Please note that it can create duplicates.

SQL
SYSADMIN.importOptimization(IN matchDescriptor string NOT NULL, IN matTablePrefix string NOT NULL, IN optType string NOT NULL, IN enabled boolean NOT NULL, IN frequency integer NOT NULL, IN lastUsed timestamp, IN lastMaterialized timestamp, 
IN dwhState string, IN dwhStateComment string, IN lastReplState string, IN lastReplStateComment string, IN sourceState string, IN sourceStateComment string, IN uuid string, OUT optimizationID biginteger NOT NULL RESULT)

Example

SQL
CALL SYSADMIN.importOptimization(matchDescriptor =>'"test_tables.test_a"',matTablePrefix =>'mat_table_0',optType =>'MAT_TABLE',enabled => false,frequency => 1,lastUsed => '2014-04-04 06:05:00.373',lastMaterialized=> '2014-04-04 06:08:16.096',dwhState => 'OK',dwhStateComment=> '',lastReplState => 'OK',lastReplStateComment => '',sourceState => 'OK',sourceStateComment =>'', uuid => '52cd677c-6696-11ee-8c99-0242ac120002') ;;

SYSADMIN.importMatTable

This procedure imports a materialized table. Please note that it can create duplicates.

SQL
SYSADMIN.importMatTable(IN accessState string NOT NULL, IN creationTime timestamp, IN lastModifiedTime timestamp, IN name string NOT NULL, IN recOptId biginteger NOT NULL, IN matTableType string NOT NULL, IN recOptUuid string, OUT id biginteger NOT NULL RESULT)

Example

SQL
CALL SYSADMIN.importMatTable(accessState => 'READY', creationTime => '2014-04-04 07:00:44.007', lastModifiedTime => '2014-04-04 07:10:44.007', name => 'mat_table_1_st0', recOptId => 2, matTableType => 'MAT_TABLE') ;;

SYSADMIN.importRecOptSymbol

This procedure imports recOptSymbols (only for MAT_AGGR recommended optimizations), Please note that it can create duplicates.

SQL
SYSADMIN.importRecOptSymbol(IN aggrFunction string, IN alias string, IN symbol string, IN type string, IN recOptId biginteger, IN recOptUuid string, OUT recOptSymbolID biginteger NOT NULL RESULT)

Example

SQL
CALL SYSADMIN.importRecOptSymbol(aggrFunction => 'COUNT', alias => 'count_adventureworks_custo', symbol => 'COUNT(adventureworks.customeraddress.AddressID)', type => 'AGGR', recOptId => 2) ;;

SYSADMIN.setOptimizationFrequency

This procedure sets a special parameter (frequency) to set or restore the frequency of an exported recOpt. If not specified, all the imported recommended optimizations will have frequency = 0. All frequencies of a newly generated recommended optimizations are set to 0.

SQL
SYSADMIN.setOptimizationFrequency(IN optimizationId biginteger NOT NULL, IN frequency biginteger NOT NULL)

SYSADMIN.setAllowCreateTempTables

deprecated

SYSADMIN.setAllowCreateTempTables deprecated since v4.1

SYSADMIN.refreshOptimizations

This procedure checks all materialized and enabled recommended optimizations. If no materialized tables are found, the procedure will re-materialize these optimizations again in the proper order (according to the view dependency graph):

SQL
SYSADMIN.refreshOptimizations()

See Also

Find Enabled Recommended Optimizations Which Have No Schedule for a short guide on how to check that there are no optimizations not covered by schedules


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.