Example: DELETE and NoPI Tables - Teradata VantageCloud Lake

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 submitted in Teradata session mode as an implicit transaction.

     DELETE FROM new_sales;

The following DELETE statement uses fastpath delete processing when 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 submitted with an END TRANSACTION statement in a multiple-statement request.

     DELETE FROM new_sales
     ;END TRANSACTION;

The following DELETE statement uses fastpath delete processing when submitted with a COMMIT statement in ANSI mode in a multiple-statement request.

     DELETE FROM new_sales
     ;COMMIT;