Table Structures FAQ - Support and Troubleshooting

Table Structures FAQ Issue The following KB provides important information around tables structures within the ServiceNOW platform. Understanding table structures is a must when implementing on an instance and this KB will address the questions that arise for situations that require troubleshooting along with information to consider when working directly with tables on the the platform.

FAQs What table structures are used on the ServiceNOW platform?

ServiceNOW employs 3 types of table structures Table Per Hierarchy(TPH), Table Per Class(TPC), and Table Per Partition(TPP)

TPC - This is the normal table structure that is used for all general DBs. In a parent child table structure all direct descendants will inherit the fields/columns from their parent table. A record for a child class will have a record in the child table as well as a record in each of its parent tables, with each record containing data field the fields specific to its class along with the sys_id field which is common to all the tables in hierarchy.

TPH - This table model was introduced in the Dublin release and applies only to the table hierarchy of the task table.there will be one physical db table that stores all classes in a given hierarchy. Currently, this is used on task table. This means, there is one physical database table, "task", and all child classes of task are store on single physical task table.

TPP - This table model was introduced in Jakarta and only applies to tables within the CMDB hierarchy. Similar to task, in the TPH model the CMDB table is also a single flattened table. The Difference however is that TPP allows for multiple partition tables to be created. The partitioning is simply for allowing more columns to be created without running into DB limitations (64KB static rows size and 64 max limit index). It is important to note that TPP table queries will look different from task queries.

Can the Extension Model be chosen?

No. The extension model is set by the platform when a table is created. If a new standalone table is created, or extended from a standalone table then the tables will use the TPC extension model.

If a new table is added that extends the a table in the task hierarchy then typically it will be TPH. There are some circumstances where it can be created as a TPC table creating a hybrid TPH/TPC hierarchy, the most common of which is the overall size of the task table at the time a new table is added to the hierarchy.

If a new table is added to the CMDB hierarchy by extending a table in the hierarchy then that table will use the extension model specified for the base table in the hierarchy. That typically will be TPP unless the hierarchy was not converted to TPP when upgraded to Jakarta, in which case it will be TPC.

The extension model is only set on the hierarchy's base table, and if empty is TPC by default.

Why was TPH introduced?

TPH was featured in Dublin and was introduced to help mitigate performance around the task table.

What are the benefits of TPH?

With one physical task table we eliminate the inner joins that are needed when querying descendant tables of task. This results in better performance for client users.

What are the downfalls of TPH?

With TPH we essentially have one super table that is made up of many additional tables. The likelihood of hitting DB limitations such as the 64Kb static row size, 1000 column hard limit, and 64 max index limit (128 indexes are possible depending on the underlying DB version being used) is much more probable with this table model. Additionally, when a physical storage column is created on the table an ALTER is initiated and since the task table is a large super table admins who are making schema level changes (adding new columns, adding indexes, shortening or increasing field lengths) see long alters since all data in the live physical table is copied to a temporary table. The copy process can take a long time depending on how large the task table is.

What is task flattening?

Task flattening is the process where we change task family hierarchy from table-per-class to table-per-hierarchy (multiple physical tables into one large super table).

What is Hybridization?

This is the process of reversing task flattening for a hierarchy of a direct child of the task table (such as incident or problem) by creating and populating TPC tables for each table in the hierarchy. Only a hierarchy which is still TPH can be hybridized, TPC tables cannot. Once a hierarchy has been hybridized it can't be reversed in the future.

This process is considered when the task table is close to the DB limitations of 64Kb static rowsize or 1000 column limit and its not possible to either drop obsolete columns from the table structure or alter the data model design to avoid adding excessive numbers of columns to the task table, such has introducing standalone related tables.

Analysis is required to determine which hierarchy is the best candidate for hybridization, as the hierarchy must have fields that are only used within the chosen hierarchy so that when the hierarchy is hybridized those fields can be dropped from the task table, thereby reducing the static rowsize and total number of columns. All tables in the chosen hierarchy are converted into TPC tables.

Once implemented joins will be needed between the hybridized table and the task table when retrieving data from the table and any insert, update or delete will generally need to execute against multiple tables. Therefore there will be some impact to hybridizing however there may be no alternative if no unnecessary columns cannot be eliminated.

What is storage alias?

A storage alias record holds information for both TPC and TPH tables that identifies how/where logical/physical elements are mapped in the backend database. TPH elements exists as logical elements (don't physically exist on the physical task table) and may map to a physical column on task with a column name of a_str_3. TPC table elements will map as normal where the application element name is the same as the physical column name.

ex: TPC : u_outage on a custom table u_test will show up in the DB as u_outage

TPH: u_test field on task that is flattened from the UI will logically show as u_test but then map physically to a field on task called a_str_1.

With TPH or TPP the physical field can potentiall longer than the logical field's max_length.

Another reason a storage alias is necessary is the platform allows the use of long table and element names which exceed the limits of the the underlying databases. As a result if a table name or element name is greater than 30 characters long the name is shortened and the resulting alias is used in place of the name in the underlying physical table. The platform then maps between the long names and the aliases when executing any SQL against the database.

What is glomming?

The concept of glomming applies to TPH tables and it allows multiple logical elements that do not have any direct relationship (sibling and cousin elements) to be mapped to a single physical storage column in the task table. Glomming helps to mitigate DB limitations such as 64 KB static row size, 100 column hard limit, 64 max index on a table. There is glomming with TPP for CMDB however, there is a slight difference in that TPP requires storage aliases for every table that inherits a field from the base table where the field was added, while with TPC the storage alias is only specified for the table where a field is added.

Why does the time it take to create a column on a table inconsistent?

When a new dictionary entry is created one of two things will occur.

We will attempt to glom the new element to an existing physical column (only applicable to TPH and TPP extension models) If there is no physical column to glom to we will then ALTER the task table and create a new physical column. If a TABLE ALTER is warranted the time it takes to create the column on the physical task table can take time if the task table is large in size. This is because a temporary table is created in which we need to take a copy of all the raw data on the task table to copy over the the temporary table. This particular process cannot be seen from the UI and occurs on the backend DB. Since task is by design a super table with millions of records the transaction to process the full creation of the new field can take longer.