DROP or DELETE DATA & DROP which is Good ?

Database

DROP or DELETE DATA & DROP which is Good ?

My requirement is to drop the unused tables which will be in size of 10 Gb to 1 TB (it will vary based on load).

what is the best apprach of dropping those tables. 

 

  • Drop Table
  • Delete Data then Drop the Table

Which approch is Good?

 

Please advise.

 

 

 

  • DELETE DROP
  • DELETE Table
  • DROP or DELETE DATA and DROP which is Good
  • DROP Table
4 REPLIES

Re: DROP or DELETE DATA & DROP which is Good ?

Hello ,

This depends on your requirement . Delete is a DML command whereas Drop is a DDL command.  Logging and triggers are done while perofrming a delete and it is a rollback type transaction. Drop being a DDL has no such repercussions and so cannot be a rolled back.

Looking at your requirements :

Option 1 : Drop is definately faster

Option 2 : Delete is slower but a safer option.

 

Cheers !

Re: DROP or DELETE DATA & DROP which is Good ?

I concur with solveraditya's reasoning. 

 

Personally, if I expect to fill that table again anytime soon or need it as a place holder for an ETL, I just use DELETE. In an automated process, I would defintely use DELETE as a step and then DROP as another (time lapse or user approval) to intervene if necessary giving way to the rollback features mentioned.

 

If the absolute end you want is to DROP the table, I wouldn't bother with DELETE and let it go while hot.

N/A

Re: DROP or DELETE DATA & DROP which is Good ?

Unless the DELETE is wrapped in a transaction (or there are Triggers or FKs defined on the table) it will be a FastPath Delete without Transient Journal, so any user approval will be too late. And of course you should be sure if you actually want to delete before you do it.

 

So a single DROP TABLE is always preferred over DELETE/DROP.

Teradata Employee

Re: DROP or DELETE DATA & DROP which is Good ?

Are you going to use again an identical table in the same database in the future?

Is this going to happen for many tables?

If it is the case I'd go for the delete and keep the table in place rather than drop and create again in the future.