Table rotation vs table extension

[georgechen]

Hi folks,

I am a newbie in the Table Rotation and extension space, and wondering if I could get some idea from you guys,

Based on Wiki ServiceNow Table Rotation and Extension references

http://wiki.servicenow.com/index.php?title=Table_Rotation#gsc.tab=0

http://wiki.servicenow.com/index.php?title=Table_Extension#gsc.tab=0

I am hoping none of our audit history would be lost when table extension or rotation is implemented, I am having a IN with ServiceNow HI for a heavy query on a date time field,

one suggestion I am offered is to implementing the indexing on the table, and am also advised to implement Rotation / Extension schedules for sys_audit, sys_email, sysevent tables.     These are the recommendations as the loading of slow TASK related items like Change or Incident can be caused due to a large audit table which the request has to traverse through.       So here is my understand

1) The existing audit table has caused significant performance impact on the Task related records

2) The implementation of Task Rotation/Extension would improve the loading on Task related records, but it pays off the auditing on the table.

Any advice if my understanding on this issue would be appreciated.

Kind regards,

George

[sergiu_panaite]

Hi George,

Basic difference between table rotation and extension is that rotation removes data from a table based on a rotation schedule while extension never deletes data, just partitions the table based on a extension schedule.

As example, syslog table is rotated by default which means this table holds only data for a certain period (usually 8 rotations of 7 days each, so 56 days of data). On the other hand, tables like sys_audit, sys_email never get cleaned, but extended. The advantage of extension is that when querying data for a period, we usually scan only the respective shards (sub-tables) rather than entire table. This is much faster, but comes with a disadvantage when we need to get the data from all shards (as we have to do a UNION ALL and that would be somewhat expensive).

For syslog, with default 8 rotations, 7 days each you would have something like:

syslog

syslog0000   --> these are called shards (or subtables if you want)

syslog0001

syslog0002

syslog0003

syslog0004

syslog0005

syslog0006

syslog0007

For sys_audit, an extension with 30 days each would look like:

sys_audit

sys_audit0000

sys_audit0001

...

sys_audit000n --> this will increase every 30 days

In case of sys_audit, if I only need data from a month data fits in one of the shards, then I would only query that shard, rather than all.

Hope this helps, if you have any other questions, let me know.

Regards,

Sergiu

[georgechen]

Thanks Sergiu, for your clear explanantion, based on your response I am creating a Knowledge base article in our organisation for any references, this is hoped to help others who are also interested in this top.

Your resposne is appreciated.

Kind regards,

George