15.00 - Overhead Costs of Referential Integrity - Teradata Database

Teradata Database Design

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

Overhead Costs of Referential Integrity

Overhead costs includes building the reference index subtables and inserting, updating, and deleting rows in the referencing and referenced tables. Overhead for inserting, updating, and deleting rows in the referencing table is similar to that of USI subtable row handling.

Teradata Database redistributes a row for each reference to the AMP containing the USI or reference index subtable entry. Processing differs after that, and most of the additional cost is in message handling.

When implementing tables with referential integrity, consider the following factors.

  • Most importantly, the performance impact to update operations, which is frequently slowed when a referential integrity constraint must be enforced.
  • INSERT performance slows for table because any referential integrity constraints defined for the table must be enforced.
  • The cost of extra disk space for table maintenance resulting from referential integrity constraints.
  • The cost of extra disk space for reference index subtables versus savings on program maintenance and increased data integrity.
  • The cost of DML integrity validity checking in applications versus the cost of not checking.