3

I've been recently assigned on a project using Teradata. I've been told to strictly use DROP+CREATE instead of DELETE ALL, because the latter "leaves some space allocated someway". This is counter-intuitive to me, and I think it's probably wrong. I searched the web for a comparison between the two methods, but I found nothing. This only reinforces my belief that DELETE ALL doesn't suffer from the issue above. However, if this is the case, I must prove it (both practically and theoretically).

So, my question is: is there a difference in space allocation between the two methods? If not, is there an official document (user guide, technical specification, whatever else) that proves it?

Thank you!

agdev84
  • 151
  • 1
  • 5
  • Is it possible the space that is being reported in DBC.DiskSpace is the table header? That space would exist if you did a DROP and CREATE without populating the new table. – Rob Paller Jan 08 '15 at 13:30

3 Answers3

3

There's a discussion here: http://teradataforum.com/teradata/20120403_105705.htm about the very same subject (although it does not really answer the "leaves some space allocated someway" part). They actually recommend DELETE ALL but for other (performance) reasons:

I'll quote just in case the link goes dead:

"Delete all" will be quicker, although being practical there often isn't a lot of difference in the performance of them.

However, especially for a process that is run regularly (say a daily batch process) then I recommend the "delete all" approach. This will do less work as it only removes the data and leaves the definition in place. Remember that if you remove the definition then this requires accessing multiple dictionary tables, and of course you then have to access those same tables (typically) when you re-create the object.

Apart from the performance aspect, the downside of the drop/create approach is that every time you create an object Teradata inserts "default rows" into the AccessRights table, even if subsequent access to the object is controlled via Role security and/or database level security. As you may well know the AccessRights table can easily get large and very skewed. In my experience many sites have a process which cleans this table on a regular basis, removing redundant rows. If your (typically batch) processes regularly drop/create objects, then you're simply adding rows into the table which have previously been removed by a clean process, and which will be removed in the future by the same process. This all sounds like a complete waste of time to me.

Community
  • 1
  • 1
Jcl
  • 27,696
  • 5
  • 61
  • 92
2

Your impression is correct, you didn't find any reference to "DELETE leaves some space allocated" in any place, because it's simply wrong :-)

DELETE ALL is similar to a TRUNCATE in other DBMSes and in most cases use fastpath processing:

dnoeth
  • 59,503
  • 4
  • 39
  • 56
0

First of all, you cannot do DROP/CREATE in one transaction in Teradata (in Oracle there are other problems with everyday DDL) so when ETL processes become complicated you might end up with the dependence where more important business processes depend on less important (like you might see the customers table empty just because the interests rates were not refreshed or you have an exceeding varchar value in just one minor column)

My opinion: Use transactions and modular programming. In Teradata this means avoiding DDL where possible and using DELETE/UPDATE/MERGE/INSERT instead of DROP/CREATE.

We have a slightly different situation in Postgres where DDL statements are transactional.

Eugene Lycenok
  • 603
  • 6
  • 14