Teradata DDL – DELETE, don’t DROP
A straightforward 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 significant downsides to this approach:
- Locks are placed on multiple dictionary tables – in TD14.10 there are nine tables locked including a table level write lock on DBC.ACCESSRIGHTS.
- The EXPLAIN plan contains 22 steps (including parallel steps).
- 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.
- 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!”)
- 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;
.IF ERRORCODE = 0 THEN .GOTO NODATA;
DROP TABLE staging_table;
CREATE TABLE staging_table,…;
.LABEL NODATA;
Rest of loading script…
Compared to the previous logic:
- There are no explicit dictionary table locks.
- The EXPLAIN plan contains five steps.
- No dictionary rows are removed and then added back in.
- It will be faster and more efficient (same result, fewer resources).
Currently, this logic cannot be processed using a simple TPT script (there is now no capability of branching based on error codes), so it has to be two scripts, the first 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