Teradata DDL – DELETE, don’t DROP
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:
- 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.
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).
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
