Skip to main content
Skip table of contents

Index Management

Indexes are used to speed up common database operations such as combining tables or refining results. The Data Virtuality Server enables indexes on materialized tables in analytical storage to augment analytical procedures even further. For more information on how indexes are recommended, what kinds are supported, and other questions, please refer to Index Management and Recommended Indexes.

SYSADMIN.createIndex

This procedure allows you to create indexes on each materialized and custom table. For this, the data source must be set as an analytical storage or have the importer.defaultSchema property set:

SQL
CREATE FOREIGN PROCEDURE createIndex(IN recOptId biginteger, IN columnName string NOT NULL, IN indexType string, IN parentRecOptID biginteger, IN indexKind string, IN tableName string, IN nofail boolean, IN recOptUuid string, IN parentRecOptUuid string, OUT id biginteger NOT NULL RESULT)

Parameters

ParameterDescription
recOptIdSpecifies recoptId, or recOptUuid, or tableName. If recoptID or recOptUuid is specified, the system procedure creates an index for the corresponding recommended optimization
columnNameFully qualified column name of the source table, which shall have an index on its materialized table(s)
indexTypeType of the index to create. JOIN, WHERE or MANUAL are accepted values. Default: MANUAL
parentRecOptIdID of the recommended optimization upon which the materialized table was created
indexKind

Current implementation knows only PostgreSQL and Oracle expression-based indexes, so the actual physical expression indexes are only being created for the mentioned DBMS.
Possible values:

  • SINGLE - single column index (for example: a);
  • MULTIPLE - multiple column index (for example: a, b);
  • EXPRESSION - expression index (for example: a + b)
tableNameSpecifies either recoptId or tableName. If tableName is specified, the index is created on the corresponding table in the specified schema
nofailSpecifies if the stored procedure should fail or not in case of errors. Default: FALSE
recOptUuidSpecifies recoptId, or recOptUuid, or tableName. If recoptID or recOptUuid is specified, the system procedure creates an index for the corresponding recommended optimization
parentRecOptUuidUUID of the recommended optimization upon which the materialized table was created

recOptUuid and parentRecOptUuid parameters in SYSADMIN.createIndex are available since v4.1

Example

SQL
CALL SYSADMIN.createIndex(1, 'test_tables_pg.test_a.a', 'MANUAL', NULL, 'SINGLE') ;
SQL
CREATE TABLE dwh.testIndex (a integer, b integer);;

--Single kind:
CALL "SYSADMIN.createIndex"(
    "columnName" => 'a',
    "tableName" => 'dwh.testIndex'
);;

--Multiple kind:
CALL "SYSADMIN.createIndex"(
    "columnName" => 'a,b',
    "tableName" => 'dwh.testIndex',
    "indexKind"=> 'MULTIPLE'
);;

--Expression kind:
CALL "SYSADMIN.createIndex"(
    "columnName" => 'dwh.testIndex.a + dwh.testIndex.b',
    "tableName" => 'dwh.testIndex',
    "indexKind"=> 'EXPRESSION'
);;

SYSADMIN.dropIndex

SQL
SYSADMIN.dropIndex(IN recOptId biginteger, IN columnName string NOT NULL, IN indexType string, IN dropPhysical boolean, IN recOptUuid string)

Parameters

ParameterDescription
recOptId

ID of the corresponding recommended optimization. If recoptID is specified, the system procedure drops an index for the corresponding recommended optimization matched with columnName and IndexType

columnNameFully qualified column name of the index to be dropped. Case must match the entry in the SYSADMIN.RecommendedIndexes system table 
indexTypeType of the index to create. JOIN, WHERE or MANUAL are accepted values. If the passed parameter is NULL, all the indexes related to the given recommended optimization will be dropped
dropPhysical

If set to TRUE: physical index will be removed from the most recent materialized table (if any);
If set to FALSE: index will not be removed from the physical table.

Default: TRUE

recOptUuid
UUID of the corresponding recommended optimization. The system procedure drops an index for the corresponding recommended optimization matched with columnName and IndexType

The recOptId or recOptUuid must be specified.

An alternative way to drop an index is to use index id or name only as follows.

recOptUuid parameter in SYSADMIN.dropIndex is available since v4.4

SYSADMIN.dropIndexById

SQL
CREATE FOREIGN PROCEDURE dropIndexById(IN indexId biginteger NOT NULL, IN dropPhysical boolean)

SYSADMIN.dropIndexByName

SQL
CREATE FOREIGN PROCEDURE dropIndexByName(IN tableName string NOT NULL, IN indexName string NOT NULL)

Example

SQL
CALL SYSADMIN.dropIndexByName(tableName => 'dwh.testindex', indexName => 'testindex_1454927209895');;
Here, indexName is the name of the index in the DBMS. Index names are usually shown in the SYS.Keys and SYS.KeyColumns tables.

SYSADMIN.importIndex

SQL
CREATE FOREIGN PROCEDURE importIndex(IN recOptId biginteger NOT NULL, IN columnName string NOT NULL, IN indexType string, IN parentRecOptID biginteger, IN indexKind string, IN recOptUuid string, IN parentRecOptUuid string, OUT id biginteger NOT NULL RESULT)

Parameters

ParameterDescription
recOptIdSpecifies recoptId, or recOptUuid, or tableName. If recoptID or recOptUuid is specified, the system procedure creates an index for the corresponding recommended optimization
columnNameFully qualified column name of the source table having an index on its materialized table(s)
indexTypeType of the index to import. JOIN, WHERE or MANUAL are accepted values. Default: MANUAL
dropPhysical

If set to TRUE: physical index will be removed from the most recent materialized table (if any);
If set to FALSE: index will not be removed from the physical table.

Default: TRUE

parentRecOptId

ID of the recommended optimization upon which the materialized table was created

indexKind

Current implementation knows only PostgreSQL and Oracle expression-based indexes, so the actual physical expression indexes are only being created for the mentioned DBMS.
Possible values:

  • SINGLE - single column index (for example: a);
  • MULTIPLE - multiple column index (for example: a, b);
  • EXPRESSION - expression index (for example: a + b)
recOptUuidSpecifies recoptId, or recOptUuid, or tableName. If recoptID or recOptUuid is specified, the system procedure creates an index for the corresponding recommended optimization
parentRecOptUuidUUID of the recommended optimization upon which the materialized table was created

recOptUuid and parentRecOptUuid parameters in SYSADMIN.importIndex are available since v4.1

Example

SQL
CALL SYSADMIN.importIndex(2, 'test_tables_or.test_a.a,test_tables_or.test_a.b', 'MANUAL', null, 'MULTIPLE') ;
 
CALL SYSADMIN.importIndex(1, 'FLOOR(test_tables_pg.test_a.a)', 'MANUAL', null, 'EXPRESSION') ;
JavaScript errors detected

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

If this problem persists, please contact our support.