インデックスは、テーブルの結合や結果の絞り込みなど、一般的なデータベース操作を高速化するために使用されます。Data VirtualTUALity Serverは、分析ストレージのMaterialized Tableにインデックスを作成し、分析手順をさらに強化します。インデックスがどのように推奨されているか、どのような種類がサポートされているか、その他の質問については、Index Management and Recommended Indexesを参照してください。

SYSADMIN.createIndex

このプロシージャにより、各マテリアライズドテーブルおよびカスタムテーブルにインデックスを作成できます。このためには、データソースが分析ストレージとして設定されているか、importer.defaultSchemaプロパティが設定されている必要があります:

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

Parameter

Description

recOptId

Specifies recoptId, or recOptUuid, or tableName. If recoptID or recOptUuid 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

recOptUuid

Specifies recoptId, or recOptUuid, or tableName. If recoptID or recOptUuid is specified, the system procedure creates an index for the corresponding recommended optimization

parentRecOptUuid

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

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

Example

CALL SYSADMIN.createIndex(1, 'test_tables_pg.test_a.a', 'MANUAL', NULL, 'SINGLE') ;
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

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

Parameters

Parameter

Description

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

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

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.

インデックスを削除する別の方法は、以下のようにインデックスIDまたは名前のみを使用することです。

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

SYSADMIN.dropIndexById

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

SYSADMIN.dropIndexByName

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

Example

CALL SYSADMIN.dropIndexByName(tableName => 'dwh.testindex', indexName => 'testindex_1454927209895');;
ここで、indexNameは DBMS におけるインデックスの名前です。インデックス名は通常、SYS.KeysSYS.KeyColumnsテーブルに表示されます。

SYSADMIN.importIndex

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

Parameter

Description

recOptId

Specifies recoptId, or recOptUuid, or tableName. If recoptID or recOptUuid 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)

recOptUuid

Specifies recoptId, or recOptUuid, or tableName. If recoptID or recOptUuid is specified, the system procedure creates an index for the corresponding recommended optimization

parentRecOptUuid

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

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

Example

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