Table flattening

  • Release version: Yokohama
  • Updated January 30, 2025
  • 3 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of Table flattening

    Table flattening in ServiceNow stores a hierarchy of related tables as a single table in a relational database, enhancing query performance by reducing the need for complex joins. This capability is controlled through different extension models that determine how table hierarchies are physically stored and accessed.

    Show full answer Show less

    Extension Models

    ServiceNow provides three extension models for storing table hierarchies:

    • Table per class: Stores each class in its own physical table, with the parent table storing copies of all descendant records. This model requires joining multiple tables for queries, which can degrade performance as the hierarchy size grows. This is the default model for most tables and system tables.
    • Table per hierarchy: Stores an entire hierarchy in a single flat physical table, with a class name column identifying the record type. This eliminates the need for table joins and improves search performance. It is used for the Task table hierarchy on MySQL databases. Oracle support requires contacting Technical Support.
    • Table per partition: Similar to table per hierarchy but divides the flat logical table into multiple physical partitions. Each partition optimizes database resource usage and supports large datasets efficiently. It uses both class name and class path columns to constrain queries, further improving performance. This model is used for the Base Configuration Item (cmdb) hierarchy on MySQL databases. Oracle support requires contacting Technical Support.

    Key Outcomes

    • Improved query performance by minimizing or eliminating table joins, especially for large table hierarchies.
    • Efficient use of database resources through partitioning for large datasets.
    • Clear identification of record types within flattened tables via class name and class path columns.
    • Default use of Table per class for most tables ensures compatibility and consistency, with specific hierarchies benefiting from flattening models based on database type and table structure.

    Practical Implications for ServiceNow Customers

    • Understanding which extension model your tables use helps optimize database queries and system performance.
    • Table flattening models can impact how you query and manage data, especially in large or complex table hierarchies such as Task or CMDB.
    • For Oracle databases, enabling Table per hierarchy or Table per partition requires assistance from ServiceNow Technical Support.
    • You can determine the extension model of any table to better tailor your performance tuning and data operations.

    Table flattening stores a hierarchy of related tables as one table in a relational database.

    Extension models

    The system offers these extension models to store a table hierarchy on a relational database.

    Table 1. Available extension models
    Extension model Flattens tables?
    Table per class No
    Table per hierarchy Yes
    Table per partition Yes

    Table per class

    The Table per class extension model stores each table of the hierarchy in its own physical table on the relational database. Each physical table uses the table prefix of the source table each stores a different class of records. An example of the Table per class extension model is the Asset [alm_asset] table, and its child tables: Hardware [alm_hardware], Consumable [alm_consumable], Facility [alm_facility], and Software License [alm_license]. The parent table of the hierarchy, Asset, stores a copy of every record in its descendant tables.

    To find records in the Table per class extension model, the system queries records from multiple tables and joins the results. For example, when searching for hardware in a related facility, the system must join results from the Hardware, Facility, and Asset tables.

    Table joins cause a performance bottleneck on relational databases. The more classes a query includes, the worse the query performance. Therefore any query for records from the top of the table hierarchy has the worst performance because it requires joining all descendant tables.

    The system uses the Table per class extension model by default when creating tables. Most system tables also use the Table per class extension model as there is no performance benefit from flattening them.

    Table per hierarchy

    The Table per hierarchy extension model stores an entire table hierarchy in a single flat physical table on the relational database. The physical table is named after the parent table of the hierarchy, such as Task. The physical table contains all records of the table hierarchy and assigns a class name column value to each descendant table of the hierarchy. The system uses the name of the source table as the class name value. For example, Task records can have class names such as Change, Incident, or Problem.

    To find records in a table hierarchy, the system queries the physical table and uses the class name column to constrain the results. Since such queries do not require joining results from multiple tables, the system provides better search performance.

    The system uses the Table per hierarchy extension model for the Task table hierarchy on MySQL databases. Other tables use the Table per class extension model because there is no performance benefit to flattening them. To use Table per hierarchy on an Oracle database, contact Technical Support.

    Table per partition

    The Table per partition extension model stores an entire table hierarchy in a single flat logical table on the relational database. Each logical table can have multiple physical storage tables called partitions supporting it. Each partition optimizes the database resources available to a physical table such as the column count, index count, and row size. The system adds a partition whenever the logical table needs additional relational database resources.

    Each logical table is named after the parent table of the hierarchy, and each supporting physical partition consists of the logical name plus a partition name. For example, the Base Configuration Item [cmdb] table starts as a logical table with no partitions. Suppose your hardware configuration items consume enough database resources that the system creates a partition called cmdb$par1 to store them. Later, computer configuration items could consume enough database resources to warrant the system creating a second partition called cmdb$par2 to store these records.

    Within each logical table, the system assigns a class name column value to each descendant table of the hierarchy. For example, within the Base Configuration Item logical table there are records with class names for Application, Computer, and IP Router. The system also assigns a two-digit class path value to each descendant table of the hierarchy. The class path is based on the table location in the hierarchy. For example, the parent class Hardware might have a class path such as /!!/!D and the child class Computer might have a class path such as /!!/!D/!!.

    To find records in the Table per partition extension model, the system queries the logical table and its partitions and uses the class path column to constrain the results. Since these queries do not require joining results from multiple tables, the system provides better search performance. In addition, the class path reduces the total number of records to search, which further improves search performance.

    The system uses the Table per partition extension model for the Base Configuration Item [cmdb] table hierarchy on MySQL databases. To use Table per partition on an Oracle database, contact Technical Support.