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:

Parameter	Description
`id`	ID of the RecommendedOptimization row
`enable`	Can take one of the following values: `TRUE`: materialized data is used `FALSE`: original data is used
`materializeNow`	If set to `TRUE`, the procedure transfers the data to the analytical storage immediately without creating a schedule for the appropriate job
`uuid`	UUID of the recommended optimization

The id or uuid must be specified.

uuid parameter in SYSADMIN.enableOptimization is available since v4.4

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:

Parameter	Description
`stagesToKeep`	Number of old stages to keep
`daysToKeep`	Number 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:

Parameter Description

dwh_table Target table in the data warehouse

source_table Source 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:

Parameter	Description
`sourceTable`	Source table
`targetTable`	Target 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
`description`	Description of the job
`uuid`	Custom UUID of the job to be created

It returns the jobId of the newly created job.

uuid parameter in SYSADMIN.createCopyOverSourceTableJob is available since v4.1

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.

uuid parameter in SYSADMIN.deleteOptimization is available since v4.4

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)

uuid parameter in SYSADMIN.importOptimization is available since v4.1

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)

recOptUuid parameter in SYSADMIN.importMatTable is available since v4.1

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)

recOptUuid parameter in SYSADMIN.importRecOptSymbol is available since v4.1

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

SYSADMIN.enableOptimization

SYSADMIN.cleanUpStaleMatTables

SYSADMIN.cleanOrphanMatTableEntities

SYSADMIN.copyOver

SYSADMIN.createCopyOverSourceTableJob

SYSADMIN.createOptimization

Example

SYSADMIN.createOptimizationQuery

SYSADMIN.deleteOptimization

Examples

SYSADMIN.importOptimization

Example

SYSADMIN.importMatTable

Example

SYSADMIN.importRecOptSymbol

Example

SYSADMIN.setOptimizationFrequency

SYSADMIN.setAllowCreateTempTables

SYSADMIN.refreshOptimizations

See Also