15.00 - Overhead Costs of Standard Referential Integrity - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Overhead Costs of Standard Referential Integrity

By implementing standard referential integrity, you incur certain overhead costs that can have a negative effect on performance. The following table lists the various referential integrity overhead operations that affect performance.

 

Operation

Description

Insert, update, or delete rows in a referencing table.

Overhead is similar to that for USI subtable row handling.

A redistributed spool for each reference is dispatched to the AMP containing the subtable entry.

BYNET traffic incurs the majority of the cost of this operation.

Insert a row into a referencing table

A referential integrity check is made against the Reference Index subtable.

  • If the referenced column is in the Reference Index subtable, the count in the Reference Index subtable is incremented.
  • If the referenced column is not in the Reference Index subtable, Teradata Database first checks the Reference Index subtable to verify that the referenced column exists.
  • If it does, an entry with a count of 1 is added to the Reference Index subtable.

    Delete a row from a referencing table

    A referential integrity check is made against the Reference Index subtable and its count for the referenced field is decremented.

    When the count decrements to 0, then the subtable entry for the Referenced field is deleted.

    Update a referenced field in a referencing table

    Overhead is similar to that for changing the value of a USI column.

    A referential integrity check is made against the Reference Index subtable. Both the inserting-a-row and deleting-a-row operations execute on the Reference Index subtable, decrementing the count of the old Referenced column value and incrementing the count of the new Reference column value.

    Delete a row from a referenced table

    The Reference Index subtable is checked to verify that the corresponding Referenced column does not exist. When nonexistence is confirmed, the row is deleted from the Referenced table.

    No BYNET traffic is involved because the Referenced column is the same value in the Referenced table and the Reference Index subtable.