DELETE - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.00
Language
English (United States)
Last Update
2018-09-28
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

DELETE

Purpose  

There are four forms of the DELETE statement.

  • Basic - removes one or more rows from a table
  • Join Condition - removes rows from a table when the WHERE condition directly references columns in tables other than the one from which rows are to be deleted; that is, if the WHERE condition includes a subquery or references a derived table.
  • Use this form when the request specifies a condition that directly references more than one table.

  • Positioned - is invoked from an embedded SQL application. For details, see “DELETE (Positioned Form)” in SQL Stored Procedures and Embedded SQL.
  • Temporal. For information about syntax compatible with temporal tables, see ANSI Temporal Table Support and Temporal Table Support.
  • Syntax - Basic Form

    Syntax - Join Condition Form

    where:

     

    Syntax Element …

    Specifies …

    delete_table_name

    the table or queue table from which the DELETE statement is to remove rows.

    The delete_table_name specification is optional when you specify a FROM clause. If you do not specify a delete_table_name, then the system deletes only the first table specified in the FROM clause.

    If you specify a correlation name in the FROM clause, then you must specify that correlation name in place of delete_table_name.

    table_name

    the name of the table, queue table, or view on delete_table_name from which the DELETE operation is to remove rows.

    FROM table_name

    the name of a derived table, joined table, or view in the subquery referenced by the predicate of the WHERE clause.

    If a row from delete_table is joined with a row from another table in the FROM clause, and the specified WHERE condition for the request evaluates to TRUE for that joined row, then the row in delete_table is deleted; else it is not.

    See “Rules for Using Scalar Subqueries in a DELETE Statement” on page 353 for the rules for using a scalar subquery for a derived table.

    If you do not specify a FROM clause, then you cannot use correlation names (compare “Example 8: Join Condition DELETE With FROM Clause and Correlation Name” on page 359 and “Example 9: Join Condition DELETE With No FROM Clause” on page 359).

    You should also specify the names of all outer tables, including the table from which rows are to be deleted.

    AS correlation_name

    an optional table alias name.

    You must specify a correlation name for each table specified in a self-join.

    ANSI calls table aliases correlation names. They are also referred to as range variables.

    condition

    a predicate to filter the list of rows to be deleted.

    The expression operands can be either constants or references to fields in the specified table or other tables. The predicate also can specify a scalar subquery. See “Scalar Subqueries” on page 142 and “Rules for Using Scalar Subqueries in a DELETE Statement” on page 353 for details.

    ALL

    that all rows in the table are to be deleted.

    This is the default and is used when a WHERE condition is not specified.

    The ALL option is a non-ANSI Teradata extension.

    ANSI Compliance

    The basic form of DELETE is ANSI SQL:2011-compliant with extensions.

    The join condition form of DELETE is a Teradata extension to the ANSI SQL:2011 standard.

    Required Privileges

    You must have the DELETE privilege on the table.

    If the DELETE statement specifies WHERE conditions, you must also have the SELECT privilege on all tables and views through which they are referenced.

    Use caution when granting the privilege to delete data through a view. Data in fields that might not be visible to the user is also deleted when a row is deleted through a view.

    Recommendation

    The basic form of DELETE is ANSI-compliant and you should use it for all delete statements that reference either a single table or reference tables other than the one deleted from only by subqueries in the condition.

    Locks and Concurrency

    A DELETE statement sets a WRITE lock for the subject table or row hash.

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

     

    IF the transaction isolation level is …

    AND the DBS Control AccessLockForUncomRead flag is set …

    THEN the default locking severity for outer SELECT and ordinary SELECT subquery operations is …

    AND the default locking severity for SELECT operations embedded within an UPDATE request is …

    SERIALIZABLE

    FALSE

    READ

    READ

    TRUE

    READ

    READ UNCOMMITTED

     

    FALSE

    READ

    TRUE

    ACCESS

    For More Information

  • See “SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL” in SQL Data Definition Language.
  • See SQL Request and Transaction Processing.
  • See Utilities.
  • 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 Deletions

    To enable fastpath delete processing:

  • The DELETE or DELETE ... ALL statement must not contain a WHERE clause.
  • None of the rows of the target table specified in the statement for deletion can have any row-level security constraints.
  • Positioning Requirements

    To enable fastpath delete processing, statements used to perform unconstrained deletes must be positioned properly. Proper positioning is determined by:

  • The position of the statement within the transaction.
  • The active session mode of the transaction.
  • The type of transaction (ANSI, Implicit, Explicit).
  • 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.

     

    Session mode 

    Transaction Type

    Positioning Requirements

    ANSI

    ANSI

    The DELETE … ALL or unconditional DELETE must be in the same multistatement request that contains a COMMIT.

    For example,

    DELETE FROM table_name ALL
    ;COMMIT;

    Teradata

    Implicit

    (such as a multistatement macro or BTEQ request)

    The DELETE … ALL or unconditional DELETE must be the last statement that references the target table in the request.

    Explicit

    The DELETE … ALL or unconditional DELETE must be in the same multistatement request that contains the END TRANSACTION that terminates the currently open transaction.

    For example,

    BEGIN TRANSACTION;
    DELETE FROM table_name ALL
    ;END TRANSACTION;

    This is not valid for embedded SQL applications because the DELETE and END TRANSACTION statements must fall into the same request, and embedded SQL does not support multistatement requests.

    Fastpath Delete and Join Indexes

    Teradata Database uses fastpath delete processing when the target table has a simple or aggregate join index that is one of the following:

  • Single‑table join index.
  • Multitable join index defined with an inner join.
  • Multitable join index where the table being deleted is the outer table of an outer join.
  • This includes the following statements:

  • A DELETE ALL or unconditional DELETE statement on a table that has a simple or aggregate join index. Teradata Database uses fastpath delete processing for the base table and its join index.
  • A conditional DELETE statement on a table that has a simple or aggregate join index. Teradata Database uses fastpath delete processing only for the join index if the DELETE condition covers the entire index.
  • 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

    The processing time for a constrained delete (DELETE...WHERE) can increase when:

  • The DELETE does not meet the conditions for a fastpath delete processing. See “Unconstrained Fastpath Delete Processing” on page 349.
  • The FALLBACK option is specified for the table, because the rows in the secondary copy of the table must also be deleted.
  • Secondary indexes are defined for the table, because the secondary index subtable also must be updated to account for the rows deleted.
  • 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 might occur if referenced databases contain tables or views with identical names and these objects are not fully qualified. Name resolution problems can even occur if the identically named objects are not themselves referenced.
  • If you attempt to 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, Teradata Database aborts the request (ANSI mode) or the transaction (Teradata mode).
  • If you attempt to 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, 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” on page 134.

    DELETE and Row-Partitioned Tables

    The following rules apply to deleting rows from a row-partitioned table.

    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 PPI base tables and PPI join indexes for the following scenarios:

  • PPI tables that are defined with PPI or non-PPI join indices.
  • PPI join indices that are defined on PPI or non-PPI tables.
  • 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.

     

    IF the condition evaluates to …

    THEN …

    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.

     

    IF the partitioning expression …

    THEN …

  • evaluates to null, or
  • is an expression that is not CASE_N or RANGE_N
  • the 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
  • is an expression that is not CASE_N or RANGE_N
  • the 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.

     

    In this session mode …

    Expression evaluation errors roll back this work unit …

    ANSI

    request that contains the aborted request.

    Teradata

    transaction that contains the aborted request.

    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.

    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 NoPI and Column‑Partitioned Tables

    DELETE is supported for NoPI and column‑partitioned tables.

    For tables and rows deleted from a column-partitioned join index, rows are logically deleted. The rows are marked as deleted and the space is not reclaimed until an unconstrained DELETE is done or a row partition delete occurs for the partition that contains the logically deleted row.

    DELETE has the following rules and restrictions for NoPI and column‑partitioned tables:

  • A constrained DELETE request must use a full-table scan on a NoPI or column‑partitioned table unless the table has a secondary, join, or hash index and the Optimizer selects an access path that uses the index.
  • Note: Column-partition elimination and row-partition elimination can cause this to be less than a full-table scan.

  • To be eligible for fastpath delete processing, a DELETE ALL or unconstrained DELETE request on a NoPI or column‑partitioned table must follow the rules described in “Positioning Requirements” on page 349.
  • Note: Deferred deletions for entire row partitions that are deleted reclaims space for the deleted row, including any logically deleted rows, in that row partition.

    For more information about NoPI and column‑partitioned tables, see Database Design.

    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 11: UDT Support For DELETE Statements” on page 359 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 13: Using an SQL UDF in a DELETE Request WHERE Condition” on page 360.

    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.

    Example  

    This example deletes all rows with a department number of 500 from the employee table:

         DELETE FROM employee 
         WHERE deptno=500;

    Example  

    This example deletes all rows from the employee table (the ALL keyword may be omitted):

         DELETE FROM employee ALL; 

    Example  

    This example deletes the row for employee 10011 from the employee table:

         DELETE FROM employee 
         WHERE empno=10011; 

    Example : Join Condition DELETE

    This example deletes rows from the employee table for employees who work in NYC. This operation joins the employee and department tables in the WHERE clause predicate:

         DELETE FROM employee
         WHERE employee.deptno = department.deptno 
         AND department.location = 'NYC';

    Example : Self-Join Condition DELETE

    This example deletes rows from the employee table for managers who have less work experience than their employees. This delete performs a self‑join on the employee table:

         DELETE FROM employee AS managers
         WHERE managers.deptno = employee.deptno 
         AND   managers.jobtitle IN ('Manager', 'Vice Pres') 
         AND   employee.yrsexp > managers.yrsexp;

    Example  

    The following macro structures the SELECT and DELETE statements as a single multistatement request:

         CREATE MACRO res_use
               (from_date (DATE,       DEFAULT DATE), 
                to_date   (DATE,       DEFAULT DATE), 
                from_time (INTEGER,    DEFAULT 0), 
                to_time   (INTEGER,    DEFAULT 999999), 
                proc      (VARCHAR(4), DEFAULT 'P'), 
                secs      (SMALLINT,   DEFAULT 600) )
            AS (SELECT
                 the_date (TITLE 'Resource//TheDate'), 
                 the_time (TITLE 'Utilized//TheTime'), 
                 proc, 
                 AVERAGE(hits) (TITLE 'Avg//Hits'), 
                 MAXIMUM(cpu)  (TITLE 'Max//CPU'), 
                 AVERAGE(cpu)  (TITLE 'Avg//CPU'), 
                 MAXIMUM(disk) (TITLE 'Max//Disk'), 
                 AVERAGE(disk) (TITLE 'Avg//Disk'), 
                 MAXIMUM(host) (TITLE 'Max//Host'), 
                 AVERAGE(host) (TITLE 'Avg//Host'), 
                 MAXIMUM(chan) (TITLE 'Max//Chan'), 
                 AVERAGE(chan) (TITLE 'Avg//Chan')
                 FROM DBC.res_use_view 
                 GROUP BY the_date, the_time, proc 
                 WHERE the_date BETWEEN :from_date AND :to_date 
                 AND   the_time BETWEEN :from_time AND :to_time 
                 AND   proc CONTAINS :proc 
                 AND   secs EQ :secs 
                 ORDER BY proc, the_date, the_time 
     
               ;DELETE FROM res_use_view ALL;); 

    If the preceding macro is executed in Teradata session mode, but not within the boundaries of an explicit transaction, fastpath delete processing is used for the DELETE statement. See “Unconstrained Fastpath Delete Processing” on page 349.

    In ANSI session mode, the system uses fastpath delete processing for the DELETE statement when a COMMIT is included in the macro or when a COMMIT is specified at the end of the request line, and immediately following the execution of the macro, within the request.

    For example, the last line of the preceding macro would read as follows:

         DELETE FROM res_use_view ALL ; 
         COMMIT;) ; 

    The system uses slow path processing for the DELETE statement when no COMMIT is stated in the macro itself or within the request that executes the macro.

    Example  

    The DELETE … ALL statement in the following BTEQ request invokes fastpath delete processing. The statements are combined into a multistatement request and are processed as an implicit transaction in Teradata session mode. Note that DELETE is the last statement in the request:

         SELECT * 
         FROM DBC.log_on_off_v 
         WHERE log_date = DATE 
         AND   user_name = 'Administrator'
        ;SELECT log_time, user_name, event, account_name
         FROM DBC.log_on_off_v 
         WHERE log_date = DATE 
         AND   user_name NOT IN ('Administrator', 'SecAdmin', 'Oper')
        ;SELECT log_time, event, logical_host_id, pe_no 
         FROM DBC.log_on_off_v 
         WHERE log_date = DATE 
         AND user_name = 'Oper'
        ;DELETE FROM DBC.log_on_off_v ALL ;

    In ANSI session mode, the COMMIT statement must follow the DELETE request, so the last line of the above example would read:

         DELETE FROM DBC.log_on_off_v ALL ; 
         COMMIT;

    Example : Join Condition DELETE With FROM Clause and Correlation Name

    The following example deletes employees from the employee table using a join on the employee and department tables.

         DELETE employee 
         FROM department AS d, employee
         WHERE employee.dept_no = d.dept_no 
         AND   salary_pool < 50000; 

    Example : Join Condition DELETE With No FROM Clause

    The following example uses different syntax to perform the same action as “Example 8: Join Condition DELETE With FROM Clause and Correlation Name” on page 359. Because this DELETE statement does not specify a FROM clause, you cannot use correlation names for the tables.

         DELETE employee 
         WHERE employee.dept_no = department.dept_no 
         AND salary_pool < 50000;

    Example : Join Condition DELETE With Derived Table Subquery

    The following example uses a subquery to perform the same action as “Example 8: Join Condition DELETE With FROM Clause and Correlation Name” on page 359 and “Example 9: Join Condition DELETE With No FROM Clause” on page 359.

         DELETE FROM employee
         WHERE dept_no IN (SELECT dept_no
                           FROM department
                           WHERE salary_pool < 50000); 

    Example : UDT Support For DELETE Statements

    The following examples show valid use of UDT expressions in a DELETE statement:

         DELETE FROM test_table 
         WHERE circle_udt < NEW circle('1,1,9');
     
         DELETE FROM test_table1, test_table2 
         WHERE test_table1.mycirc = test_table2.mycirc;

    Example : DELETE and NoPI Tables

    The following examples are based on this NoPI table definition:

         CREATE TABLE new_sales, 
         FALLBACK (
           item_nbr   INTEGER NOT NULL,
           sale_date  DATE FORMAT 'MM/DD/YYYY' NOT NULL,
           item_count INTEGER)
         NO PRIMARY INDEX;

    The following DELETE statement requires a full-table scan because new_sales has neither a primary nor a secondary index.

         DELETE FROM new_sales 
         WHERE item_nbr = 100;

    The following DELETE statement uses fastpath delete processing if it is submitted in Teradata session mode as an implicit transaction.

         DELETE FROM new_sales;

    The following DELETE statement uses fastpath delete processing when it is submitted in Teradata session mode as a single request.

         BEGIN TRANSACTION 
         ;DELETE FROM new_sales 
         ;END TRANSACTION;

    Assume the following single-statement request is submitted in Teradata session mode.

         BEGIN TRANSACTION;

    Then the following DELETE statement uses fastpath delete processing when it is submitted with an END TRANSACTION statement in a multistatement request.

         DELETE FROM new_sales
         ;END TRANSACTION;

    The following DELETE statement uses fastpath delete processing when it is submitted with a COMMIT statement in ANSI mode in a multistatement request.

         DELETE FROM new_sales 
         ;COMMIT;

    Example : Using an SQL UDF in a DELETE Request WHERE Condition

    You can specify an SQL UDF in a WHERE clause search condition if it returns a value expression.

         DELETE FROM t1 
         WHERE a1 = test.value_expression(b1, c1);

    Example : Deleting a Row From a Table With Row-Level Security Protection

    In this example, assume that the user submitting the request has the privileges required to delete a row from inventory.

    The following DELETE statement deletes a row from a table named inventory.

         DELETE FROM inventory
         WHERE col_1 = 12126;

    Example : Application of Row-Level Security DELETE and SELECT Constraints When User Lacks Required Privileges (DELETE)

    This example shows how the DELETE and SELECT constraints are applied when a user without the required OVERRIDE privileges attempts to execute a DELETE statement on a table that has the row-level security DELETE and SELECT constraints.

    An EXPLAIN request modifier is used to show the steps involved in the execution of the request and the outcome of the application of the constraints.

    Table Definition

    The statement used to create the table in this example is:

         CREATE TABLE rls_tbl(
           col1 INT,
           col2 INT,
           classification_levels   CONSTRAINT,
           classification_categories CONSTRAINT);

    User’s Session Constraint Values

    The user’s session constraint values are:

         Constraint1Name LEVELS
         Constraint1Value 2
         Constraint3Name CATEGORIES
         Constraint3Value '90000000'xb

    EXPLAIN Request Modifier with a DELETE Statement

    This EXPLAIN request modifier shows the steps performed to execute the DELETE statement and the outcome of the application of the DELETE and SELECT constraints.

           EXPLAIN DELETE FROM rls_tbl WHERE col1=2; 

    EXPLAIN Text

    The system returns this EXPLAIN text.

     *** Help information returned. 10 rows.
     *** Total elapsed time was 1 second.
     
    Explanation
    ----------------------------------------------------------------------
      1) First, we do a single-AMP DELETE from RS.rls_tbl by way of the
         primary index "RS.rls_tbl.col1 = 2" with a residual condition of (
         "((SYSLIB.DELETELEVEL (RS.rls_tbl.levels ))= 'T') AND
         (((SYSLIB.SELECTLEVEL (2, RS.rls_tbl.levels ))= 'T') AND
         (((SYSLIB.DELETECATEGORIES (RS.rls_tbl.categories ))= 'T') AND
         ((SYSLIB.SELECTCATEGORIES ('90000000'XB, RS.rls_tbl.categories ))=
         'T')))").  The size is estimated with no confidence to be 2 rows.
         The estimated time for this step is 0.01 seconds.
      -> No rows are returned to the user as the result of statement 1.
         The total estimated time is 0.01 seconds.

    Related Topics

  • “DELETE (Positioned Form)” in SQL Stored Procedures and Embedded SQL
  • “DELETE (Temporal Form)” in Temporal Table Support
  • “DELETE (ANSI System-Time Form)” in ANSI Temporal Table Support
  • “DELETE (ANSI Valid-Time Form)” in ANSI Temporal Table Support
  • “DELETE (ANSI Bitemporal Table Form)” in ANSI Temporal Table Support