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.
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.