diff between trucate drop and delete
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: diff between trucate drop and delete

  1. #1
    Join Date
    Jan 2001
    Posts
    60
    Hi

    When i want to delete rows from a table which has million records then which is the best to do it .
    It has indexes ,clusters and so on and it's parent table too .

    I want to know what will be the real scenario ,if i use and which is faster and advantages over other
    html code is off

  2. #2
    Join Date
    Jan 2001
    Posts
    28
    from my experience, truncate table is always the best option because
    1. Delete uses the rollback segment and you will need a real big rollback seg if we are talking deleting a million plus records.
    2. Deleting a Million records consumes a lot of time.

    Truncate on the other hand happens fast and rollback segment is not eaten up. But once done, data is lost, you cannot recover unless you have a tape backup or something.
    If you have a partitioned tablespace, you can truncate a single partition which is even the more helpful.
    - Mayur.

  3. #3
    Join Date
    Jul 2000
    Posts
    296
    If you drop the table, indexes, constraints, grants will also dropped and has to be recreated. The dependent objects (e.g packages) become invalid and has to be recompiled.

    If you truncate a table, all rows wil be deleted, also indexes will be truncated. By default the segment will be reduced to one extent, unused space is freed. You don't lose indexes, constraints and grants. Truncate cannot be rolled back.

    Delete removes the rows from a table. Unused space is not freed, the segment will not be reduced. Delete can be rolled back. With many rows, it is slower then truncate and you need a large rollback segment.

    Because truncate is DDL you need dynamic SQL to use it in PL/SQL, in 8.0 and 8i this has become easy.
    In 8.0:
    DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE emp');
    In 8i:
    EXECUTE IMMEDIATE 'TRUNCATE TABLE emp';

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width