17.05 - Example: DELETE and NoPI Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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;