Altering indexes on Campaign’s native tables
Learn about altering indexes on Campaign’s native tables and creating a clustered index.
Description description
Environment
- Campaign Classic
- Campaign
- Campaign Classic v7
Issue/Symptoms
Are you allowed to alter indexes on Campaign’s native tables?
Can you create a clustered index?
Can the clustered index be added from the schema definition?
Resolution resolution
Yes, Campaign allows you to create non-clustered indexes from backend. The only difference is that such indexes will not be available at schema level to make modifications to.
Since this non-clustered index will be maintained by your DBA, the defragmentation of it will have to be handled outside the Campaign system.
You can create a clustered index and it will not hinder the Campaign execution. In fact, it will enhance the performance. Campaign schema primary keys translate to a unique index at the database level. Since a clustered index is needed on the SQL Server table for enhanced performance, you can create it directly from the database.
However, there is a catch to using a clustered index.
- Campaign makes use of sequences to generate the primary key field value.
- Since sequence is a field of Integer (int32) type it cycles between 0 and the highest value.
- Clustered index columns decide the physical ordering of the table and any new inserts can lead to physical shifting of pages.
- When the sequence reaches its maximum value, it recycles to either its negative extreme or to 0, depending upon the configuration done for the sequence. Upon recycling, you will be generating records with a PK value lower than the last record. This leads to the insertion of records on existing pages of the table, leading to multiple shifts.
- Campaign performs bulk inserts/updates, and hence, it can lead to adverse effects.
No, as Campaign only allows the creation of the unique index, the clustered index must be created from the backend.