Example: DELETE and NoPI Tables - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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;