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.

[su_shadow][su_panel]
  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.
[/su_panel][/su_shadow]

When advising clients or instructing, I suggest the subsequent procedure:

DELETE FROM staging_table;
.IF ERRORCODE = 0 THEN .GOTO NODATA;
DROP TABLE staging_table;
CREATE TABLE staging_table,…;
.LABEL NODATA;

Rest of loading script…

In contrast to the previous reasoning:

[su_shadow][su_panel]
  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).
[/su_panel][/su_shadow]

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

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

>