Optimization Management

You are looking at an older version of the documentation. The latest version is found here.

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:

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

It takes the following parameters:

SYSADMIN.cleanUpStaleMatTables

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

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

It takes the following parameters:

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.

SYSADMIN.cleanOrphanMatTableEntities()

SYSADMIN.copyOver

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

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

It takes the following parameters:

SYSADMIN.createCopyOverSourceTableJob

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

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

It takes the following parameters:

It returns the jobId of the newly created job.

SYSADMIN.createOptimization

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

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

Example

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

SYSADMIN.createOptimizationQuery

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

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.

SYSADMIN.deleteOptimization(IN id biginteger NOT NULL)

Examples

1. Deleting all optimizations

CALL SYSADMIN.deleteOptimization(-1)

2. Deleting just one optimization:

CALL SYSADMIN.deleteOptimization(5)

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.

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, OUT optimizationID biginteger NOT NULL RESULT)

Example

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 =>'') ;;

SYSADMIN.importMatTable

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

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, OUT id biginteger NOT NULL RESULT)

Example

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.

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

Example

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.

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

SYSADMIN.setAllowCreateTempTables

This procedure defines if creating temporary tables is allowed or not:

SYSADMIN.setAllowCreateTempTables(IN role_name string NOT NULL, IN allow boolean NOT NULL)

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):

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