Information around table schema level changes



This KB revolves around schema level changes that can result in table alters. The focus here will mainly be TASK and CMDB tables as they are the largest tables in the system. A table alter causes schema changes in the database. The execution time of an alter will depend on various factors like the number of impacted records, database constraints etc.

It may not always be simple to estimate whether an action will cause any schema level changes in the database or not but one of the ways it can be found is by looking at the sys_schema_change table. The changes are recorded in this table. There are many actions that can trigger table alters and can result in situations like temporary performance degradation for some forms or reports, some fields not being visible on the form for a while and so on. The following section encompasses a comprehensive list of such actions.



Changes that can/may result in a table alter
#Adding, removing or modifying an index of a table directly from the database (This operation can only be performed by support)
#Modifying the datatype of a column in a table
#Adding new column/columns to a table
#Removing an existing column from a table
#Drop a table from a the database
#Checking the 'unique' checkbox from the dictionary record of a field
To confirm that an 'Alter' happened, the sys_schema_change table should be checked. For example, a new column called 'u_kind' was added to the Task table in the out of box instance. The screenshot below shows the record which was created in the sys_schema_change as a result of this action. This also indicates that an ALTER was performed on the task table.
Changes that will not result in a table alter
#Adding a dictionary attribute
#Changing the label of a field from the dictionary
#Checking or unchecking the 'active' or 'read-only' check boxes from a dictionary entry
#Adding an index from the UI of an instance