Results 1 to 9 of 9

Thread: diffrence between delete with commit and truct table

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Aug 2007
    Answers
    1

    diffrence between delete with commit and truct table

    what is difference between delete with commit and truct table?

    Both are same?
    thanks


  2. #2
    Junior Member
    Join Date
    May 2007
    Answers
    5

    Post Re: diffrence between delete with commit and truct table

    yes interms,that u cannot get the previous data,that is delete
    is DML,truncate is DDL,if u give delete without commit u can get the previous data,but with truncate it will delete all the rows from that table and cannot be rollback since it is autocommit.


  3. #3
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: diffrence between delete with commit and truct table

    if u delete and then commit it is true that u can't rollback to get back the data .but with TRUNCATE the only difference is being a DDL it changes the structure of the table. What truncate does it it deletes all the records and releases the momory which does not happen with delete till you commit and all that in a single command .

    another difference is u can't specify any condition for truncate. it truncates the entire table by default.


  4. #4
    Contributing Member
    Join Date
    Apr 2008
    Answers
    31

    Re: diffrence between delete with commit and truct table

    Delete will delete the records one by one where as truncate will delete all the records at a time. Also you can roll back the records if you use the delete statement but not when you use truncate...


  5. #5
    Contributing Member
    Join Date
    Jun 2009
    Answers
    76

    Re: diffrence between delete with commit and truct table

    Quote Originally Posted by viralkumar View Post
    what is difference between delete with commit and truct table?

    Both are same?
    thanks
    Please go thro' all replies and add this into that, delete keeps the log while truncate does not.


  6. #6
    Junior Member
    Join Date
    Jul 2010
    Answers
    2

    Re: diffrence between delete with commit and truct table

    The difference lies first with the efficiency, 'delete with commit' will remove the enteries from your table in a way that each row is deleted individaully while truncate will empty your table in a single turn, which is more efficient. and secondly the table space, delete won't release a table space, while truncate does


  7. #7
    Junior Member
    Join Date
    Jun 2009
    Answers
    18

    Re: diffrence between delete with commit and truct table

    hi,
    truncate is ddl command and delete is dml command .compare to delete truncate is best in performance bcoz when execute a delete command all record in a table is store into rollback tablespace after storing it will delete the data,so it takes time for all this process while truncate will delete directly with storing the data that's the reson when we execute rollback we will get the deleted records.


  8. #8
    Junior Member
    Join Date
    Aug 2010
    Answers
    1

    Re: diffrence between delete with commit and truct table

    Hi... Both does the same deletion of rows :
    truncate executes faster as compared to delete but once you issue truncate you cannot rollback,in case of delete ,table is placed in rollback tablespaces ,even if you delete you can rollback i.e get back the data .


  9. #9
    Junior Member
    Join Date
    Jul 2009
    Answers
    5

    Re: diffrence between delete with commit and truct table

    Delete is DML command. It will remove the records from the table.
    We need to commit the transaction to make the changes permanent to the database.

    Also DELETE will log the details in data dictionary. So if we have not explicitly committed the transaction we could always "ROLLBACK" to get back all the deleted rows.

    TRUNCATE also removes the records from the table.

    Difference is how Oracle implements "TRUNCATE" and "DELETE".
    For "TRUNCATE" Oracle engine will drop the table and recreate it. This is much faster than delete. Only issue is table records once truncated cannot be recovered. The reason is Oracle does not create logs as in the case of "DELETE".


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact