Teradata DDL – DELETE, don’t DROP

By David Wellman

March 31, 2015

Guest Post

Teradata DDL – DELETE, don’t DROP

A very simple start to the ETL processing on Teradata is to load data from outside of Teradata into an empty table, often referred to as a ‘staging’ table. Working with many customer sites I’ve often seen the following logic (usually coded in a Fastload or TPTLoad script):

DROP TABLE staging_table;

CREATE TABLE staging_table,…;

Rest of loading script…

While this works from a functional perspective there are some large downsides to this approach:

  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 consulting with customers or teaching I recommend the following approach:

DELETE FROM staging_table;


DROP TABLE staging_table;

CREATE TABLE staging_table,…;


Rest of loading script…

Compared to the previous logic:

  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. It will be faster and more efficient (same result, fewer resources).
At the moment this logic cannot be processed using a simple TPT script (there is currently no capability of branching based on error codes) so it has to be two scripts, the first one being BTEQ.

This change will not reduce run-times by minutes but is just one part of making your Teradata processing more efficient.

See also:
Cloning of Tables in Teradata

Buy now at Amazon

David Wellman

David Wellman is the Technical Director of Ward Analytics Ltd, a UK company specialising in Teradata performance analysis and management. David has been using the Teradata products for over 20 years and regularly delivers Teradata training courses and performance consulting engagements. David is a Teradata Certified Master at V2R3, V2R5, TD12 and TD14.More details at: http://www.ward-analytics.com

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like