Index Management

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

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

Parameters

Parameter	Description
`recOptId`	Specifies either `recoptId` or `tableName`. If `recoptID` is specified, the system procedure creates an index for the corresponding recommended optimization
`columnName`	Fully qualified column name of the source table, which shall have an index on its materialized table(s)
`indexType`	Type of the index to create. `JOIN`, `WHERE` or `MANUAL` are accepted values. Default: `MANUAL`
`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)
`tableName`	Specifies either `recoptId` or `tableName`. If `tableName` is specified, the index is created on the corresponding table in the specified schema
`nofail`	Specifies if the stored procedure should fail or not in case of errors. Default: `FALSE`

Example of usage

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 NOT NULL, IN columnName string NOT NULL, IN indexType string, IN dropPhysical boolean)

Parameters

Parameter	Description
`recOptId`	Specifies either `recoptId` or `tableName`. If `recoptID` is specified, the system procedure creates an index for the corresponding recommended optimization
`columnName`	Fully qualified column name of the index to be dropped. Case must match the entry in the `SYSADMIN.RecommendedIndexes` system table
`indexType`	Type 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`

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

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

Parameters

Parameter	Description
`recOptId`	Specifies either `recoptId` or `tableName`. If `recoptID` is specified, the system procedure creates an index for the corresponding recommended optimization
`columnName`	Fully qualified column name of the source table having an index on its materialized table(s)
`indexType`	Type 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)

Examples

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