This discussion is archived
6 Replies Latest reply: Feb 18, 2013 5:44 AM by APC RSS

truncate or drop table.

947771 Newbie
Currently Being Moderated
hi,

I have a sp , it delets records then insert some record in a table when ever it runs.
it's a offline sp.

truncation is better or deleting or droping and creating it again.

please tel me above with respect to GTT(trnasaction level) and normal tabel?

yours siincerely.
  • 1. Re: truncate or drop table.
    jeneesh Guru
    Currently Being Moderated
    944768 wrote:
    hi,

    I have a sp , it delets records then insert some record in a table when ever it runs.
    I would suggest TRUNCATE if the whole data is to be removed..

    And if the data is coming from other tables - you could think of Materialized View also
    it's a offline sp.
    What does that mean?
    truncation is better or deleting or droping and creating it again.
    truncate is better than delete - it will reset the HWM, release the space occupied. But there are differences - ON DELETE triggers will not get executed in case of TRUNCATE, if there are any. TRUNCATE is auto COMMIT as it is a DDL. You cannot directly write TRUNCATE statement in an SP , you need to use dynamic SQL..

    Dropping will drop all your indexes, grants, constraints etc..
    please tel me above with respect to GTT(trnasaction level) and normal tabel?
    GTT is just like a normal table (operationally) except that the data will be visible for the session by which it is operated..
    And GTT data will be stored in TEMP tablespace.. You should not use GTT for very large set of data
  • 2. Re: truncate or drop table.
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    Truncate is alway's better than delete.

    Please visit below link..

    http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands
  • 3. Re: truncate or drop table.
    947771 Newbie
    Currently Being Moderated
    --That means even GTT (transaction level ) will be in good health if 'truncate is used.

    i am using it like 'TRUNCATE TABLE GTTABC'
    is it ok or should one consider some more things in case of noraml table or GTT.

    -- offline means when there is no entry going on.

    yours sincerly
  • 4. Re: truncate or drop table.
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    It's ok. you can use truncate and then use the structure of the table.
  • 5. Re: truncate or drop table.
    APC Oracle ACE
    Currently Being Moderated
    Chanchal Wankhade wrote:
    Truncate is alway's better than delete.
    ... for certain values of "always".

    For instance DELETE might be preferable if the table is owned by a different schema. Or if you want to include the clear-down as part of a transaction. Or if you just want to be able to change your mind about zapping the records. Or if you want to undertake the cleardown in a stored proecdure without using dynamic SQL.

    Cheers, APC
  • 6. Re: truncate or drop table.
    APC Oracle ACE
    Currently Being Moderated
    944768 wrote:
    --That means even GTT (transaction level ) will be in good health if 'truncate is used.
    You shouldn't need to truncate a global temporary table. And you definitely don't need to truncate if it's a transaction level temporary table: a commit or a rollback will suffice to wipe its data.

    If you think you regularly have a need to truncate a global temporary table you probably are not using it in the correct fashion, and probably shouldn't be using a global temporary table at all.

    Cheers, APC

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points