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.