Both the Update operator and the Stream operator can be used to update data in the Teradata Database, however:
- The Update operator locks the target tables that are being updated so that interactive data reads and writes cannot be performed concurrently.
- The Stream operator does not lock the target tables that are being updated so that interactive read and write activities can be performed concurrently.
This feature of the Stream operator enables it to perform update operations during periods of heavy table access by other users. Like the other Teradata PT operators, the Stream operator can use multiple sessions and multiple operator instances to process data from several data sources concurrently.
Unlike the Load and Update operators, Stream operator does not use its own protocol to access Teradata. Rather it uses Teradata SQL protocol.
| Parameter | Update Operator | Stream Operator |
|---|---|---|
| Volume | Performs high-volume updates against a large number of rows. | Works better for low-volume real-time updates. |
| Performance | Performance improves as the volume of updates increases. | Performance improved with multi-statement requests. |
| Lock Granularity | Bulk updates at block level. Must lock all tables, which prevents access until complete. Rows are not available until the load job is complete. |
Does not fully lock target tables during updates. Instead, uses standard SQL locking protocols to lock individual rows as updates are applied, which permits concurrent read and write access to target tables by other users. Rows are immediately available for access once the transaction is complete. |
| Number of Tables | No more than 5. | Up to 127. |
| Timing | Batches transactions and applies them at a higher volume, but usually at a rate that is much slower than real-time. | Loads changes in near real-time. |
| Concurrent Operations | Requires one Teradata Database load slot against the database connected to by each Export, Load, or Update operator specified in the APPLY-SELECT statement. | Does not require a Teradata Database load slot. |
| Instances | Multiple parallel instances improve update performance. | Multiple parallel instances might or might not improve performance. |
| Sequencing | Data is processed in sequence all the time (but not in real-time). | Robust mode must be used if sequencing is needed. |
| DML Statements | Uses actual DML statements. | Uses macros to modify tables rather than actual DML commands. |
| Work Tables | Requires one work table per target table. | Work tables not required. |