In sage x3, we have a function called database optimization which is used to track or create custom index. This function help us for easy reapplication when updating or migrating data set. It is useful to create the indexes in order to achieve a specific optimization (for example in a report or to optimize a complex inquiry). A typical example of this is the addition of an index to a large table to accelerate the creation of data in an inquiry created by the requestor, or the use of a dedicated index in the object setup, to use a specific order in a left list.
Database Optimization function comes under the Setup module and therefore it is folder specific. There is a copy function to allow you to copy custom indexes to other endpoint folders. Make sure you select the proper folder.
Follow the below steps:
Navigate to > Set up > Usage > Data > Database Optimization
- Table: In this column, we will enter the table code of the table in which you want to add custom index.
- Index: In this column, we have to label the index starting with SPE_ then whatever else you want to label it as. It must start with SPE_ or else the index will not add.
- Index Descriptor: The third column is the index descriptor which is the organizing field columns in which you want to use. Here we have to enter the field based on which you need to add index. For the column separator you must use minus (-) or plus (+).
- Active: The Active column is a simple yes/no for if the index is active or not.
- Comments: Lastly the Comments column is just a text field for you to leave yourself comments about the index.
Here we are using ZTEMP table and are adding the “ZITMREF+ZSTOFCY ” as index descriptors and will keep this as active.
Now click on save. Once we have saved we have to click on RUN button to apply the index. This will create an index for the table ZTEMP. Refer the below screen shot.
Note, it will apply all indexes marked as active.
If users are in the system the the below warning message will pop up and the index will not be applied.
If there will be no pop up msg then everything is successful.
We cant able to see the custom index through table dictionary. Custom indexes applied from the Database Optimization function are written directly to SQL. You will have to log into SQL Server to verify that the index was applied. Refer the below screenshot.
It is also possible to remove the custom index. If you want to remove a custom index, change the active column from yes to no and click the run button again to remove the index.
You can verify the same by logging into SQL.
In this way we can add or remove custom index from table by using database optimization function.