Campaign Classic V7 - Are we allowed to alter indexes on Campaign’s native tables?

Description

  1. Are we allowed alter indexes on Campaign’s native tables?
    2. Can we create a Clustered Index?
  2. Can the Clustered index be added from schema definition

Resolution

  1. Yes, Campaign allows you to create non-clustered indexes from backend. The only difference will be 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 then have to be handled outside the Campaign system.

  1. We 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 database level. Since Clustered index are needed on SQL Serve table for enhanced performance, we can create it directly from database.

However, there is a catch to using a clustered index.

- Campaign makes use of sequences to generate the primary key field value.

  • Sequence since is a field of Integer (int32) type it cycles between 0 and the highest value.

  • Clustered index columns decide the physical ordering of 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 sequence. Upon, recycling, we will be generating records with PK value lower than the last record. This will lead to insertion of records on existing pages of table, leading to multiple shift.

  • Campaign performs bulk inserts/updates and hence it can lead to adverse effects.

  1. No, as Campaign only allows creation of unique index. Clustered index must be created from backend.

On this page