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.
- 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 advising or instructing clients, I recommend the following 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:
- 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 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
Related Services
⚡ Need Help Optimizing Your Data Platform?
We cut data platform costs by 30–60% without hardware changes. 25+ years of hands-on tuning experience.
Explore Our Services →📋 Considering a Move From Teradata?
Get a personalized migration roadmap in 2 minutes. We have migrated billions of rows from Teradata to Snowflake, Databricks, and more.
Free Migration Assessment →