Recommended Index Status

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:

Value	Meaning
`NEW`	Automatically 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.

Value	Meaning
`NEW_ACCEPTED`	Indexes will be created with `NEW` and `ACCEPTED` status values only
`ACCEPTED`	Indexes will be created with `ACCEPTED` status value only
`NONE`	No indexes will be created
`DEFAULT`	Index 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');