DWHPro Insights

Teradata DDL Best Practice: Use DELETE Instead of DROP for Efficiency and Resource Management

By Roland Wenzlofsky ·

Teradata DDL Best Practice: Use DELETE Instead of DROP for Efficiency and Resource Management

The dictionary-lock and EXPLAIN-step counts below are historical TD14.10 observations, not fixed current counts. The safe fallback is to stop and diagnose an unexpected DELETE error, never to drop the table indiscriminately.

Teradata DDL – DELETE, don’t DROP

To initiate the ETL process on Teradata, a simple approach involves importing data from external sources into an empty table commonly known as a ‘staging’ table. In my experience working with numerous clients, I have encountered a common practice of implementing this logic through a Fastload or TPTLoad script.

DROP TABLE staging_table;
CREATE TABLE staging_table,…;

Rest of loading script…

Although functional, this approach has notable drawbacks.

  1. Locks are placed on multiple dictionary tables – in TD14.10 there are nine tables locked including a table level write lock on DBC.ACCESSRIGHTS.
  2. The EXPLAIN plan contains 22 steps (including parallel steps).
  3. For the proverbial 99.99% of times, this logic is executed the DROP TABLE removes some dictionary rows which are then added back to the CREATE TABLE. Apart from date/time values nearly all of the data values in the new rows are identical to the old rows.
  4. It also helps to negate a lot of the work which is done to housekeep the DBC.AccessRights table (see my article titled “Redundant AccessRights – More Housekeeping!”)
  5. It is slower and uses more resources (CPU and IO) than necessary.

When the existing table structure is correct, DELETE can empty it while preserving grants and indexes. Stop on a DELETE error and diagnose it; a missing table can be handled through a separate explicit creation path, but other errors must not trigger DROP.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
DELETE FROM staging_table;
.IF ERRORCODE <> 0 THEN .QUIT 8
-- Continue with the load only after the DELETE succeeds.

Rest of loading script…

In contrast to the previous reasoning:

  1. There are no explicit dictionary table locks.
  2. The EXPLAIN plan contains five steps.
  3. No dictionary rows are removed and then added back in.
  4. DELETE and DROP/CREATE do not have identical semantics. DELETE preserves identity state, grants and indexes; in a fixture the next identity value was 2 after deleting the row with identity 1. Choose the required behavior before comparing cost.

Currently, this reasoning cannot be executed through a basic TPT script since no branching feature exists according to error codes. Consequently, two scripts must be involved, with the initial being BTEQ.

This modification won’t diminish runtime by minutes, yet is a singular piece of the puzzle in optimizing your Teradata processing.

Also, check out:
Table Cloning in Teradata

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam