インデックスは、テーブルの結合や結果の絞り込みなど、一般的なデータベース操作を高速化するために使用されます。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 |
---|---|
| Specifies |
| Fully qualified column name of the source table, which shall have an index on its materialized table(s) |
| Type of the index to create. |
| ID of the recommended optimization upon which the materialized table was created |
| Current implementation knows only PostgreSQL and Oracle expression-based indexes, so the actual physical expression indexes are only being created for the mentioned DBMS.
|
| Specifies either |
| Specifies if the stored procedure should fail or not in case of errors. Default: |
| Specifies |
| 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 |
---|---|
| 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 |
| Fully qualified column name of the index to be dropped. Case must match the entry in the |
| Type of the index to create. |
| If set to Default: |
| 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.Keys
とSYS.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 |
---|---|
| Specifies |
| Fully qualified column name of the source table having an index on its materialized table(s) |
| Type of the index to import. |
| If set to Default: |
| ID of the recommended optimization upon which the materialized table was created |
| Current implementation knows only PostgreSQL and Oracle expression-based indexes, so the actual physical expression indexes are only being created for the mentioned DBMS.
|
| Specifies |
| 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'
) ;