Usage Notes - Teradata Database

Locks and Concurrency

A DELETE statement sets a WRITE lock for the table, partitions, or row. For a nonconcurrent load isolated delete operation on a load isolated table, the delete operation sets an EXCLUSIVE lock.

For a SELECT subquery, the lock depends on the isolation level for the session, the setting of the AccessLockForUncomRead DBS Control field, and whether the subquery is embedded within a SELECT or DELETE statement.

DELETE Processing Time

Processing time can vary between different syntaxes used to perform the identical delete operation. Use the EXPLAIN request modifier to determine which syntax form produces optimal processing time.

Unconstrained Fastpath Delete Processing

To ensure that fastpath delete processing is enabled for unconstrained deletes performed using unconditional DELETE or DELETE ... ALL statements, there must not be any restrictions or conditions on the deletions and the statements must be positioned properly.

Restrictions on Fastpath Deletions

Fastpath Positioning Requirements

Position Within the Transaction

The speed of processing of an unconstrained delete depends on its position within the transaction.

When submitted as the last request of a transaction, other than for an implicit transaction, the DELETE or DELETE ... ALL statement must be the last statement that references the target table in the request that contains an END TRANSACTION or COMMIT. When a request is submitted as an implicit transaction, the DELETE or DELETE ... ALL statement must be the last statement that references the target table in the request.

Proper Positioning For the Session Mode and Transaction Type

Because fastpath delete processing defers the delete until a transaction ends and because it allows rollback and delete processing to be almost instantaneous, make sure that the DELETE or DELETE ... ALL statements are positioned properly based on the active session mode and the transaction type.

DELETE FROM table_name ALL
;COMMIT;

BEGIN TRANSACTION;
DELETE FROM table_name ALL
;END TRANSACTION;

Fastpath Delete and Join Indexes

Fastpath Delete for Multiple DELETE Statements

Teradata Database uses fastpath delete processing for multiple DELETE ALL statements in an implicit transaction or in the last request of a transaction.

Constrained (Slow Path) DELETE Processing

Processing time for a constrained delete can be shortened by specifying a primary index value, a USI value, or a highly selective NUSI value as the conditional expression of the WHERE clause.

Duplicate Rows and DELETE Processing

Duplicate rows in a MULTISET table cannot be distinguished. When a WHERE condition identifies duplicate rows, all duplicate rows are deleted.

General Rules for Using DELETE

The following rules apply to using DELETE.

• All correlation names must be specified in the FROM clause.
• The activity count in the success response for a DELETE statement reflects the total number of rows deleted.
• A DELETE statement that references objects in multiple databases should use fully-qualified names. Name resolution problems may occur if referenced databases contain tables or views with identical names and these objects are not fully qualified. Name resolution problems can occur even if the identically named objects are not explicitly referenced.
• You cannot delete a row from a base table that causes an insert into a join index that has row partitioning such that a partitioning expression for that index row does not result in a value between 1 and the number of partitions defined for that level. Otherwise, Teradata Database aborts the request (ANSI mode) or the transaction (Teradata mode).
• You cannot delete a row from a base table that causes an update of an index row in a join index with row partitioning such that a partitioning expression for that index row after the update does not result in a value between 1 and the number of partitions defined for that level. Otherwise, Teradata Database aborts the request (ANSI mode) or the transaction (Teradata mode).

Deleting Rows Using Views

To delete table rows using a view through which the table is accessed, refer to the following conditions.

• You must have the DELETE privilege on the view. Also, the immediate owner of the view (that is, the database in which the view resides) must have the DELETE privilege on the underlying object (view or base table) whose rows are to be deleted, and the SELECT privilege on all tables that are specified in the WHERE clause.
• Each column of the view must correspond to a column in the underlying table. That is, none of the columns in the view can be derived using an expression.
• The data type definitions for an index column should match in both the view definition and in the base table to which the view refers.

  While it is true that you can generally convert the type of a view column (for example, from VARCHAR to CHARACTER), if that converted column is a component of an index, then that index is not used to delete rows from the base table because the data type of the recast column no longer matches the data type definition for that column in the index.

  The resulting all-AMP, all-row behavior of the delete circumvents the performance advantages for which the index was designed.

• Any two view columns cannot reference the same table column.
• The view cannot include a column that contains a range constraint.
• The expression used to define a view cannot have a data type specified for any column in the view.

Subqueries in a DELETE Statement

DELETE statement predicates can include subqueries that reference the delete target table, as well as other tables. The following DELETE statement is an example:

     DELETE FROM publisher
     WHERE 0 = (SELECT COUNT(*)
                FROM book
                WHERE book.pub_num=publisher.pub_num);

Two publishers have books in the library and two publishers do not.

The subquery executes once for each row of the outer reference, the publisher table. Because two publishers have no books in the library, the two rows that represent those publishers are deleted from the publisher table.

To modify this DELETE to use a noncorrelated subquery, change the subquery code to include all tables it references in its FROM clause.

     DELETE FROM publisher
     WHERE 0 = (SELECT COUNT(*)
                FROM book, publisher
                WHERE book.pub_num=publisher.pub_num);

When coded this way, the subquery predicate has a local defining reference, so the DELETE statement does not contain a correlated subquery. The count, determined once, is nonzero, so no rows are deleted.

Rules for Using Scalar Subqueries in a DELETE Statement

You can specify a scalar subquery in the WHERE clause of a DELETE statement in the same way you can specify one for a SELECT statement.

You can also specify a DELETE statement with a scalar subquery in the body of a trigger. However, Teradata Database processes any noncorrelated scalar subquery you specify in the WHERE clause of a DELETE statement in a row trigger as a single-column single-row spool instead of as a parameterized value.

Rules for Using Correlated Subqueries in a DELETE Statement

The following rules apply to correlated subqueries used in a DELETE statement:

• A DELETE statement requires that if joined tables are specified, all tables referenced in the DELETE statement must be specified in the FROM clause, including the deleted table.

  A table_name must be added to specify the deleted table name in this case.

• All correlation names must be defined in the FROM clause.

  Correlation names are also referred to as range variables and aliases.

• If a correlation name is defined for the deleted table name in the FROM clause, then that correlation name, not the original table name, must be used as the table_name that follows the DELETE keyword.
• If an inner query column specification references an outer FROM clause table, then the column reference must be fully qualified.
• The table_name preceding the FROM clause is optional if no joined tables are specified for deletion.

Also see Correlated Subqueries.

DELETE Statement is in the Last Request

If a DELETE statement is in the last request of a transaction and the following conditions are met, the delete is optimized to delete entire row partitions:

• The ANSI mode and Teradata mode conditions for fastpath delete processing are satisfied, with the exception that a deferred partition DELETE can be followed by another statement that references the same target table.
• The target table is not defined with referential integrity.

This avoids transient journaling of each row deleted from those row partitions.

Deferred Deletion Applies Only to Range Terms

Deferred deletions do not occur for partitioning levels that are not defined using a RANGE_N function. To qualify for deferred deletion, the test value of a RANGE_N partitioning expression must be a simple column reference for a partitioning level.

Deferred Deletion is Supported for LIKE Terms

Deferred deletion is also supported for LIKE terms defined in the form partitioning_column LIKE ‘abc%’.

Deferred Deletion and Join Indexes

Teradata Database uses deferred partition deletion on row-partitioned base tables and row-partitioned join indexes for the following scenarios:

• Row-partitioned tables.
• Row-partitioned join indexes

  Deletion and Row-Partitioned Join Indexes

If you attempt to delete a row from a base table that causes an insert into a row-partitioned join index or an update of an index row in a row-partitioned join index such that:

• any of the partitioning expressions for that join index row evaluate to null, or
• the partitioning expression is an expression that is not CASE_N or RANGE_N, it's result is not between 1 and 65535 for the row

the system aborts the request (ANSI mode) or transaction (Teradata mode). It does not perform the insert or update and returns an error or failure, respectively.

Deleting a row from a base table does not always cause the deletion of a join index on that base table. For example, you can specify a WHERE clause in the CREATE JOIN INDEX statement to create a sparse join index for which only those rows that meet the condition of the WHERE clause are inserted into the index, or, for the case of a row in the join index being updated in such a way that it no longer meets the conditions of the WHERE clause after the update, cause that row to be deleted from the index.

The process for this activity is as follows:

1. The system checks the WHERE clause condition for its truth value after the update to the row.

   Condition	Description
   FALSE	the system deletes the row from the sparse join index.
   TRUE	the system retains the row in the sparse join index and proceeds to stage b.

2. The system evaluates the new result of the partitioning expression for the updated row.

   Partitioning Expression	Description
   Evaluates to null, or Expression is not CASE_N or RANGE_N	Result is not between 1 and 65535 for the row. The system aborts the request (ANSI mode) or transaction (Teradata mode). It does not update the base table or the sparse join index, and returns an error or failure, respectively.
   Evaluates to a value, and Expression is not CASE_N or RANGE_N	Result is between 1 and 65535 for the row. The system stores the row in the appropriate partition, which might be different from the partition in which it was stored, and continues processing requests.

Expression evaluation errors, such as divide by zero, can occur during the evaluation of a partitioning expression. The system response to such an error varies depending on the session mode in effect.

Define your partitioning expressions to ensure that expression errors do not prevent the insertion of valid rows.

Collation and Row Deletion

Collation can affect the deletion of rows from tables defined with a character partitioning expression.

• If the collation for a table is either MULTINATIONAL or CHARSET_COLL and the definition for the collation has changed since the table was created, Teradata Database aborts any request that attempts to delete a row from the table and returns an error to the requestor.
• If a noncompressed join index with a character partitioning expression defined with either an MULTINATIONAL or CHARSET_COLL collation sequence is defined on a table and the definition for the collation has changed since the join index was created, Teradata Database aborts any request that attempts to delete a row from the table and returns an error to the requestor whether the operation would have deleted rows from the join index or not.
• The session mode and collation at the time the table was created need not match the current session mode and collation for the delete operation to succeed.

  Partitioning Expressions and Unicode

If a partitioning expression for a table or noncompressed join index involves Unicode character expressions or literals and the system has been backed down to a release that has Unicode code points that do not match the code points that were in effect when the table or join index was defined, Teradata Database aborts any attempts to delete rows from the table and returns an error to the requestor.

Column Partitioned Tables and DELETE

For column partitions with ROW format, rows are physically deleted and the space is reclaimed.

DELETE for NoPI Tables

DELETE is supported for NoPI tables.

For more information about NoPI tables, see Database Design .

Queue Tables and DELETE

The best practice is to avoid using the DELETE statement on a queue table because the operation requires a full table scan to rebuild the internal queue table cache. You should reserve this statement for exception handling.

A DELETE statement cannot be specified in a multistatement request that contains a SELECT and CONSUME request for the same queue table.

For details on queue tables and the queue table internal cache, see SQL Data Definition Language .

DELETE Support for External UDT Expressions

External UDT expressions are valid specifications for DELETE processing in the same way as any other valid expression. A UDT expression is any expression that returns a UDT value. See Example: UDT Support For DELETE Statements for valid examples of using a UDT expression in a DELETE operation.

DELETE Support for SQL UDT Expressions

You can specify an SQL UDT as part of a WHERE clause condition in a DELETE statement if the SQL UDT returns a value expression. See Example: Using an SQL UDF in a DELETE Request WHERE Condition.

Embedded SQL and Stored Procedure Error Condition Handling

If DELETE is specified with a WHERE clause and the specified search condition fails because it selects no rows, the value ‘02000’ is assigned to SQLSTATE, +100 is assigned to SQLCODE, and no rows are deleted.

A fastpath delete operation may not occur on load isolated tables due to presence of logically deleted rows.

DELETE Support for Load Isolated Tables

A nonconcurrent load isolated delete operation on a load isolated table or join index physically deletes the qualified rows.

A concurrent load isolated delete operation on a load isolated table or join index logically deletes the qualified rows. The rows are marked as deleted and the space is not reclaimed until you issue an ALTER TABLE statement with the RELEASE DELETED ROWS option. See SQL Data Definition Language Syntax and Examples.