Archives of the TeradataForum

Message Posted: Tue, 22 Jul 2003 @ 20:34:18 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This is a belated response to an email from last month.

-------------------------------

-------------------------------

The difference between standard RI and batch RI is the approach used by the database to perform the referential integrity check. In standard RI, whether you are doing row-at-time updates or set-processing insert/selects, each child row will be separately matched to a row in the parent table, one row at a time. A separate select against the parent table is performed for each child row. Depending on your demographics, parent rows may be selected more than once.

With batch RI, all of the rows within a single statement (even if this is just one row) will be spooled and sorted, and will have their references checked in a single operation, as a join to the parent table. Depending on the number of rows in the insert/select, batch RI could be considerably faster, compared to checking each parent-child relationship individually.

In both cases, an insert/select with RI will rollback if there is a single violation detected.

If you plan to do row-at-time updates, there will be very little difference between the approaches. But if you plan to load primarily using insert/selects, batch RI should be a better choice. As will smaller, rather than larger batches, if there is much risk of violations.

Thanks, --Carrie Ballinger

	<Prev	Next>		<<First	<Prev	Next>	Last>>