Update operations have two phases:
Acquisition Phase – Data from the input stream is transmitted to the AMPs, and access locks are placed on the target tables, limiting table access to read-only. The acquisition phase is complete when all data rows are on the appropriate AMPs where their changes will be applied.
Records are sorted by the hash value of the primary index value. This sorting order becomes the sequence in which they are applied to a target table. Sorted records are placed in temporary work tables that require permanent space for the duration of the job.
One work table per target table is created by the Teradata Database. Because the acquisition phase involves writing only to work tables, target tables are left available for user access.When the job has multiple work tables and uses the Extended MultiLoad Protocol, only the first work table is created. The Update operator inserts records into the first work table during the acquisition phase.
- Application Phase – Sorted input records are applied to data blocks of target tables using the appropriate DML commands (insert, update, delete). Each target block is read once into memory, and all changes are applied at that time. The access lock on target tables is upgraded to a write lock, so tables are not available for user access until the phase is complete. At the end of this phase, the work tables are dropped during a subsequent clean-up phase.