Truncate or not Truncate SQL

5 pts.
Tags:
PL/SQL
SQL
What are the pros/cons of truncate a table export/import to just drop table?
ASKED: April 11, 2008  3:37 PM
UPDATED: April 14, 2008  4:01 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Truncate deletes all your data (w/o using rollback/undo) retaining the definition of the table (and the storage it used, if desired). Dropping the table deletes the data (w/o using rollback/undo) and removes the definition from the data dictionary and de-allocates the storage that was assigned to the table.

Which one you want to use depends on what you are trying to accomplish. If you are reusing the table, truncate clears the table and the REUSE STORAGE clause retains the storage assigned to the table and it runs faster than a drop table.

If it is a one-time use table, drop the table. If you are going to reuse the table truncate is faster and you don’t have to recreate the table everytime. Depending on your application, you may want to look at global temporary tables to do this same thing. They get cleaned up when your session ends automatically.

-Dave

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    Another reason to truncate rather than drop the table is that if you drop the table, you lose the permissions, indexes, constraints, etc. If you have those requirements, you would need to set that back up each time you create the table.
    1,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following