Skip to main content
Skip table of contents

Recommended Index Status

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

The recommended index status reflects the production status of an index and controls whether the index is being created when a corresponding materialized table is created.

It may have one of the following values:

ValueMeaning
NEWAutomatically recommended index not yet reviewed by a user
ACCEPTED Automatically recommended index accepted by a user
REJECTED Automatically recommended index rejected by a user

You can get the status of an index by checking the status field of the SYSADMIN.RecommendedIndexes table. The following procedure may be used to set its value:

SQL
CREATE FOREIGN PROCEDURE setIndexStatus(IN indexId biginteger NOT NULL, IN indexStatus string NOT NULL)

To correctly manage indexes regarding their status, there are index status policy values. When a materialized table is getting actually materialized (i.e., a new stage is being created), these values control which indexes will be actually created for the table instance.

ValueMeaning
NEW_ACCEPTED Indexes will be created with NEW and ACCEPTED status values only
ACCEPTED Indexes will be created with ACCEPTED status value only
NONENo indexes will be created
DEFAULTIndex creation policy is retrieved from the INDEX_CREATION_POLICY global option


Please note that values are case-sensitive.

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

SQL
CREATE FOREIGN PROCEDURE setRecOptAllowIndexCreationByStatus(IN recOptId biginteger NOT NULL, IN allowIndexCreationByStatus string NOT NULL)

Here are some examples with different values:

1. ACCEPTED

SQL
CALL SYSADMIN.setRecOptAllowIndexCreationByStatus(1, 'ACCEPTED') ;

2. NEW_ACCEPTED

SQL
CALL SYSADMIN.setRecOptAllowIndexCreationByStatus(1, 'NEW_ACCEPTED');

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

SQL
SELECT allowIndexCreationByStatus FROM SYSADMIN.RecommendedOptimizations ;

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

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

SQL
SELECT allowIndexCreationByStatus FROM SYSADMIN.ScheduleJobRun ;
 
SELECT allowIndexCreationByStatus FROM SYSADMIN.ScheduleJobs ;

To get the global option, you can use this procedure:

SQL
SELECT * FROM (call SYSADMIN.getDefaultOptionValue('INDEX_CREATION_POLICY')) a

And to set the global option, this procedure:

SQL
CALL SYSADMIN.setDefaultOptionValue('INDEX_CREATION_POLICY', 'ACCEPTED');
JavaScript errors detected

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

If this problem persists, please contact our support.