ALLOW_INDEX_RECOMMENDATION & ALLOW_INDEX_CREATION

ALLOW_INDEX_RECOMMENDATION

To have the control over how recommended indexes are created when a query is processed you can set ALLOW_INDEX_RECOMMENDATION. It can have the following possible values:

Value	Description
`ALL`	All recommended/physical indexes are automatically created; default
`WHERE`	Only recommended/physical indexes with type `WHERE` are created
`JOIN`	Only recommended/physical indexes with type `JOIN` are created
`NONE`	No indexes are created

ALLOW_INDEX_CREATION

To control how physical indexes for the corresponding materialized tables when an optimization is enabled and materialized are created you can set ALLOW_INDEX_CREATION. It can have the following possible values:

Value	Description
`ALL`	All recommended/physical indexes are automatically created; default
`WHERE`	Only recommended/physical indexes with type `WHERE` are created
`JOIN`	Only recommended/physical indexes with type `JOIN` are created
`MANUAL`	Only recommended indexes manually generated using the `createIndex()` system procedure will be created
`NONE`	No indexes are created

Combining Values

Combination of values for the ALLOW_INDEX_CREATION option can be provided in a CSV format. For example, "WHERE, JOIN" or "JOIN, MANUAL" are accepted values. ALL and NONE values cannot be combined with any other value (e.g. "ALL, JOIN" is not a valid value).

Manual Index Creation

As for the MANUAL indexes, they can be created using the stored procedures SYSADMIN.createIndex() or SYSADMIN.importIndex(). Here is an example:

CODE

CALL SYSADMIN.createIndex(3, '', 'test_tables.test_b.c', 'MANUAL', null)
CALL SYSADMIN.importIndex(3, '', 'test_tables.test_b.c', 'MANUAL', null)

The main difference between the create and import procedures is that the first one creates a recommended index and also a physical index in the materialized table; the second one only creates a recommended index.