Recommended Index Type

Physical indexes are created for the corresponding materialized tables when an optimization is enabled and materialized. The recommended index type reflects the production type of an index and controls how the index is created.

To correctly manage indexes according to their types, there are index type policy values. When a materialized table is being actually materialized (i.e., a new stage is being created), these values control which indexes will be actually created for the table instance. The possible values are listed in this table:

Value	Meaning
`ALL`	All indexes are automatically created
`WHERE`	Only indexes with type `WHERE` are created
`JOIN`	Only indexes with type `JOIN` are created
`MANUAL`	Only manually generated indexes are created
`NONE`	No indexes are created
`JOIN_MANUAL`	Only indexes with `JOIN` and `MANUAL` types are created
`JOIN_WHERE`	Only indexes with `JOIN` and `WHERE` types are created
`MANUAL_WHERE`	Only indexes with `MANUAL` and `WHERE` types are created
`JOIN_MANUAL_WHERE`	Indexes with `JOIN`, `WHERE`, and `MANUAL` types are created

Please note that values are case-sensitive.

You may assign an individual policy value to a recommended optimization using the following procedure:

SQL

CREATE FOREIGN PROCEDURE setRecOptAllowIndexCreationByType(IN recOptId biginteger NOT NULL, IN allowIndexCreationByType string NOT NULL)

To find out the currently set value, you can check the RecommendedOptimizations.allowIndexCreationByType field:

SQL

SELECT allowIndexCreationByType FROM SYSADMIN.RecommendedOptimizations ;

When an optimization job is created using createOptimizationJob(), the indexCreationByType parameter can be set to define the individual policy value for the job. The default value is the value of the corresponding optimization's allowIndexCreationByType field.

To find out the currently set value, you can check the ScheduleJobs.indexCreationByType field or ScheduleJobRun.indexCreationByType for the job run record:

SQL

SELECT allowIndexCreationByType FROM SYSADMIN.ScheduleJobRun ;
 
SELECT allowIndexCreationByType FROM SYSADMIN.ScheduleJobs ;

Here are several examples with different values being set:

1. WHERE

SQL

CALL SYSADMIN.setRecOptAllowIndexCreationByType(1, 'WHERE');

2. JOIN

SQL

CALL SYSADMIN.setRecOptAllowIndexCreationByType(1, 'JOIN');

3. JOIN, MANUAL, WHERE

SQL

CALL SYSADMIN.setRecOptAllowIndexCreationByType(1, 'JOIN_MANUAL_WHERE');