Table rotation

Table rotation preserves instance performance and averts risk associated with querying growing data sets by using the External Communication Channel (ECC) Queue and the sys_created_on field to separate data sets into multiple tables based on date.

Note: Deployment of this plugin should be executed in partnership with a ServiceNow representative.
Functionality
The administrator specifies the time parameter (duration) of the process and the number of tables (rotations) within. After the rotation writes the last table in a rotation, the rotation overwrites the first table in the rotation. Please contact ServiceNow Technical Support before applying table rotation to a custom table.
Examples:
  • The query Records created between 2015/12/10 08:49 and 2015/12/09 07/34 where topic=SystemCommand is translated to a SQL query on a single table, because the clause on sys_created_on targets a single shard.
  • The query Records updated between 2015/12/10 08:49 and 2015/12/09 07/34 where topic=SystemCommand, or without a date range, needs to target all shards and therefore is translated as a union query on all shards.
Advantages
  • Allows deletion of old data without affecting current data (for example, to drop or truncate a table).
  • Ensures tables only grow to a reasonable size.
  • Reduces working set of data when date is known for query.
Disadvantages
Queries that do not use the table rotation date (for example, by using the sys_created_on field), force an inefficient union query to query time ranges that span multiple tables and can be extremely slow if the number of sub-tables is large.

To improve performance, it is recommended that the query includes a window of created dates.

You can use table rotation for sequentially-written tables or for insert-only tables. You cannot use table rotation for sys_import tables or tables that extend the Task [task] table.