All Forums Database
cool_guy 1 post Joined 10/11
04 Oct 2011
Query to Delete all the records in a table in Teradata

Hi All,

What is the query to delete all the records in a table in Teradata ?

Is this query correct:

Delete * from <DB_Name.Table_Name>;

Pls reply.

Thanks!

 

Ckraenzle 2 posts Joined 08/10
04 Oct 2011

DELETE FROM <dbname>.<tablename>;

Adeel Chaudhry 773 posts Joined 04/08
11 Oct 2011

Hi,

 

In case the table is huge, you can also use DROP TABLE and CREATE TABLE commands.

 

HTH!

 

Regards, MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

ramesh_td 9 posts Joined 01/14
15 Jun 2014

Whatever mentioned by Ckraenzle is correct.  You can simple use the below Synxtax to delete complete records from a teradata table.

delete from dbname.tablename;

 

ramesh_td 9 posts Joined 01/14
15 Jun 2014

Whenever we delete huge number of records from a table, say I am trying to delete 50 millon records in a 1 billion records table, usually it takes so long to complete the task. What I know was the delete statement tries to delete all the records at one shot.
Is it a way to improve the performance of deleting records from a table? one of my friend was telling me that we can delete the records by putting the #of records in loop, which is say putting the delete statement in a loop such a way that each time it will delete 2 million records and the total records will be deleted in 25 loops.
Has someone approached this way?

dnoeth 4628 posts Joined 11/04
16 Jun 2014

Don't delete in batches.
If the delete is slow it's probably because it needs a Full Table Scan or there's secondary indey maintenance. If you split it into 25 deletes wil require FTSs and SI maintenance 25 times.
Do you have any SIs (or Trigger/FKs)?
On big tables you should try to delete data based on the partitioning schema.

Dieter

ramesh_td 9 posts Joined 01/14
19 Jun 2014

Dieter,
Thanks for your reply. I don't have any idea regarding deleting data based on partitioning schema. can you give me a sample example?
ramesh
 
 

dnoeth 4628 posts Joined 11/04
20 Jun 2014

Big tables are usually partitioned by date, e.g

PARTITION BY RANGE_N (trans_date BETWEEN DATE '2005-01-01' AND DATE '2020-12-31' 
EACH INTERVAL '1' DAY

When you

DELETE FROM tab 
WHERE trans_date BETWEEN DATE '20010-01-01' AND DATE '2010-02-15'

there's no Full Table Scan, but a very efficient partition access. Plus this might be a FastPath Delete without Transient Journal.  

Dieter

goldminer 118 posts Joined 05/09
23 Jun 2014

Just a word or caution regarding the option of dropping the table and then re-creating...
 
When you re-create you need to also recreate the table and column comments.  More importantly past statistics analysis will also be lost unless you capture those stats and re-apply after table creation.  I used dieters process to create a macro called grab_stats that captures existing collect stats statements on a table to be re-applied in scenarios just like this... thanks again Deiter!
 
Joe

calldhavalpatel 1 post Joined 06/14
27 Jun 2014

delete * from database.tablename;

VandeBergB 182 posts Joined 09/06
28 Jun 2014

the delete syntax permeating this thread is spot on, but if you've got a large table and need to truncate the entire table, add the "all" keyword to the end...
delete from dbname.tablename all;
Running the delete statement with the "all" keyword allows you to avoid transaction logging of each row's deletion.  The "all" keyword wipes the cyliner headers and marks the tables as empty..a.k.a fast path delete.
 

Some drink from the fountain of knowledge, others just gargle.

dnoeth 4628 posts Joined 11/04
28 Jun 2014

Nope, there's no difference when you add ALL, this is just an optional keyword.
You get a FastPath Delete when there's no Trigger/Foreign Key and it's known to the optimizer that the delete is commited, i.e. there must be a "END TRANSACTION" step in Explain.

Dieter

You must sign in to leave a comment.