It was late Friday afternoon, but then it's usually late Friday when these calls come in. The voice on the phone hurriedly said, "It is taking over an hour to delete all the rows from my Teradata table! Does Teradata have a TRUNCATE command?"
It was a consulting friend who was working on a Customer's Teradata Server. This meant that dropping and recreating the table was not an option.
My friend needed the Teradata version of the ORACLE TRUNCATE command. One problem, there isn't a Teradata version of that command.
What's so special about TRUNCATE?
Well, the Oracle TRUNCATE command isn't logged which means there is very little overhead -- it is fast. Wicked fast! The command also resets the high water mark for the table. Oracle will read empty tablespace area, so resetting the high water mark speeds-up queries. The downside is that since there is no logging, it is an unrecoverable action.
Fortunately there is a way to very rapidly delete all the rows from a Teradata table. It isn't as simple as TRUNCATE but it does work well. This method bypasses the Teradata journaling facility -- which makes it non recoverable. The official name for this is the Fast Path DELETE. The trick here is the DELETE command has to occur during a single transaction.
You can invoke the Fast Path Delete via explicit pass-through using ANSI or Teradata mode. Here are some example:
Teradata Mode Fast Path DELETE example:
proc sql;
connect to teradata (server=MyServ database=MyDB MODE=TERADATA user=MyUserid password=mypasswd);
execute (BEGIN TRANSACTION; DELETE mytable ALL; END TRANSACTION; ) by teradata;
quit;
ANSI Mode Fast Path DELETE example:
proc sql;
connect to teradata (server=MyServ database=MyDB user=MyUserid password=mypasswd MODE=ANSI);
execute (DELETE mytable ALL; COMMIT; ) by teradata;
quit;
There is a paper on SAS' web site that covers this topic as well as many others. You can find it here.
