Comparing Update and Stream Operators
Both the Update operator and the Stream operator can be used to update data in the Teradata Database, however:
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 |
Requires a load slot against the database connected to by each Export, Load, or Update operator specified in the APPLY-SELECT statement. |
Does not require an active 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. |