17.00 - 17.05 - Guidelines for Deleting Rows - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

The following table provides some guidelines you can use to make the best choice of utilities when deleting rows. Keep in mind that you may find a better way through experimenting.

Task First Choice Second Choice
Delete a small number of rows from any table TPump BTEQ
Delete a large number of rows from a large table MultiLoad BTEQ
Perform DELETE operations on a large number of rows on multiple tables MultiLoad TPump
Delete all rows from a table BTEQ

In this case, Teradata marks the table header with an indicator to say that the table is empty. Assuming there are no locks on the table when this delete is requested, the delete will complete in a second or two.

 
Delete some rows from a table BTEQ
Space permitting, it can also be an option to create an empty table, insert into this table the rows that you wish to retain and drop the original table and then rename the new table to the name of the original table. MultiLoad deletes are good for deleting large volumes of data, but not all data, from a table.

MultiLoad reads the data block by block. It will “touch” each block consumed by the table and remove rows as appropriate, and replace the block.

This can be very efficient and is faster than an SQL delete in many cases because there is no rollback logging. However, MultiLoad must complete or else the table will remain in an unstable state. For MultiLoad to work best, drop SIs, run the MultiLoad delete, and then replace the indexes. This is quicker and ensures that the work table is small.

You cannot delete rows from a table using the SQL MERGE statement.