Skip to main content
Skip table of contents

Recommended Index Type

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

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:

ValueMeaning
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');
JavaScript errors detected

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

If this problem persists, please contact our support.