Skip to main content
Skip table of contents

ALLOW_INDEX_RECOMMENDATION & ALLOW_INDEX_CREATION

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

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:

ValueDescription
ALLAll recommended/physical indexes are automatically created; default
WHEREOnly recommended/physical indexes with type WHERE are created
JOINOnly recommended/physical indexes with type JOIN are created
NONENo 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:

ValueDescription
ALLAll recommended/physical indexes are automatically created; default
WHEREOnly recommended/physical indexes with type WHERE are created
JOINOnly recommended/physical indexes with type JOIN are created
MANUALOnly recommended indexes manually generated using the createIndex() system procedure will be created
NONENo 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.

See also

System Procedures for more information on procedures mentioned in this page

JavaScript errors detected

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

If this problem persists, please contact our support.