16.10 - Delete Task Option - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

The Delete Task option is unique to the Update operator. It deletes rows more quickly than using a single DELETE SQL statement.

When the DeleteTask attribute is set to 'Y', rows are deleted from a single table based on values other than a unique primary index (UPI) equality condition. A Delete Task option uses a single session and a single instance.

The Delete Task option is a good choice when a large percentage of rows in a table need to be deleted, such as deleting all rows with a transaction date prior to a specified date.

The Delete Task option operates very similarly to the standard DELETE statement in the Update operator, with the following differences:

  • Deleting based on non-index columns is normal for the Delete Task option.
  • Deleting based on a primary index, although possible, has certain limitations:
    • An equality test of a UPI value is not permitted.
    • An inequality test of a UPI value is permitted.
    • An equality test of a NUPI value is permitted.
  • A single DELETE statement is used in the APPLY statement.
  • The Delete Task option does not include an acquisition phase because there are no varying input records to apply.
  • The application phase reads each target block and deletes qualifying rows.
  • Altered blocks are written back to disk.

When the Delete Task option is specified, the Update operator functions as a standalone operator, that is, not as the usual consumer operator that reads from a source data stream. The exception is when the Delete Task is invoked by an APPLY statement that includes a WHERE clause, and the source data stream contains only a single row. In this case, the Update operator with the Delete Task option still functions as a consumer operator.

The following rules apply to a Delete Task operation regardless of whether it functions as a standalone or consumer operator:

  • Only one session is connected.
  • Only one instance is specified.
  • Only one DML group is specified.
  • Only one DML statement in the DML group is specified.
  • Only a single DELETE statement is used.
  • Only one target table is specified.
  • The first error table is not used and is ignored.
  • Only one data record is provided if using a WHERE clause.