Modify to truncate vs. drop table

Modify to truncate vs. drop table

Post by Roy Han » Fri, 12 Jul 1996 04:00:00




>      One of our developers has just told me that they have a program which
>      does a modify to truncate followed by a drop, of a temprary table.
>      When asked why he didn't just drop it, he said because he was once
>      told that it was faster to modify to truncate, then drop, rather than
>      to just drop.

>      All the DBA's here were slightly sceptical of this claim so we decided
>      to put it to the collected wisdom of the newsgroup. Has anyone else
>      heard this, and what is the justification for it ?

I've never bothered to test it, but DROP always seems to work pretty much
instantly.  If there is a difference it ain't worth worrying about.  I
think your programmer is confusing droping the table with emptying it.  A
MODIFY TO TRUNCATED is MUCH faster than a DELETE FROM...

========================================================================

BKB Engineering Ltd.,                      NAIUA Member-at-Large
11211-76th Avenue,                
Edmonton, Alberta                          http://www.tnc.com/bkb
T6G 0K2                                    Tel: (403)497-2409
Canada                                     FAX: (403)436-6055
========================================================================

 
 
 

Modify to truncate vs. drop table

Post by Dean.Lawrenc » Fri, 12 Jul 1996 04:00:00


     Hi,

     One of our developers has just told me that they have a program which
     does a modify to truncate followed by a drop, of a temprary table.
     When asked why he didn't just drop it, he said because he was once
     told that it was faster to modify to truncate, then drop, rather than
     to just drop.

     All the DBA's here were slightly sceptical of this claim so we decided
     to put it to the collected wisdom of the newsgroup. Has anyone else
     heard this, and what is the justification for it ?

     TIA

     **********************************************************************
     * Name:  Dean Lawrence                                               *

     * Phone: int +44 115 9662297                                         *
     * Fax:   int +44 115 9662091                                         *
     *                                                                    *
     * Address: 398 Coppice Road, Arnold,                                 *
     *          Nottingham, NG5 7HX, United Kingdom                       *
     **********************************************************************

 
 
 

Modify to truncate vs. drop table

Post by Karl Schend » Fri, 12 Jul 1996 04:00:00



Quote:> Subject: Modify to truncate vs. drop table

> ... he said .. he was once
>      told that it was faster to modify to truncate, then drop, rather than
>      to just drop.

>      All the DBA's here were slightly sceptical of this claim so we decided
>      to put it to the collected wisdom of the newsgroup. Has anyone else
>      heard this, and what is the justification for it ?

If you want it to go REALLY fast, chant paragraphs from the Farmer's
Almanac while it's running...

seriously, i never heard of any such thing, and i can't imagine a sensible
implementation that would work like that.  (I don't consider logging
the entire table contents to log a drop sensible, and ingres doesn't
do that.)

--
Karl Schendel            Phone: (412) 963-8844
Telesis Computer Corp      Fax: (412) 963-1373

 
 
 

Modify to truncate vs. drop table

Post by Dean.Lawrenc » Fri, 12 Jul 1996 04:00:00


     Hi,

     One of our developers has just told me that they have a program which
     does a modify to truncate followed by a drop, of a temprary table.
     When asked why he didn't just drop it, he said because he was once
     told that it was faster to modify to truncate, then drop, rather than
     to just drop.

     All the DBA's here were slightly sceptical of this claim so we decided
     to put it to the collected wisdom of the newsgroup. Has anyone else
     heard this, and what is the justification for it ?

     TIA

     **********************************************************************
     * Name:  Dean Lawrence                                               *

     * Phone: int +44 115 9662297                                         *
     * Fax:   int +44 115 9662091                                         *
     *                                                                    *
     * Address: 398 Coppice Road, Arnold,                                 *
     *          Nottingham, NG5 7HX, United Kingdom                       *
     **********************************************************************

 
 
 

Modify to truncate vs. drop table

Post by William Blackwell 01925 40561 » Fri, 12 Jul 1996 04:00:00


It is better and faster to have a permanent 'temporary' table and modify to
truncate than it is to create and drop temporary tables, which is most probably
where the confusion lies. Modifying before drop makes no difference.

If memory serves me right create and drop update system catalogues (iitable,
iicolumns, iifile_info etc) which is where it takes slightly longer, where as
modify to truncate is a quick way of emptying the table that affects the table
itself and nothing else. However I am lead to believe there is an OpenIngres
temporary table that does not use system catalogues and does not write to file
if possible, which will most probably be the quickest form (in terms of create
and drop) of temporary storage.  

As with all Ingres  'this is faster than that' claims there will always be an
exception.

 
 
 

Modify to truncate vs. drop table

Post by Jon Machtyng » Fri, 12 Jul 1996 04:00:00




>> Subject: Modify to truncate vs. drop table

>> ... he said .. he was once
>>      told that it was faster to modify to truncate, then drop, rather than
>>      to just drop.

>>      All the DBA's here were slightly sceptical of this claim so we decided
>>      to put it to the collected wisdom of the newsgroup. Has anyone else
>>      heard this, and what is the justification for it ?

>seriously, i never heard of any such thing, and i can't imagine a sensible
>implementation that would work like that.  (I don't consider logging
>the entire table contents to log a drop sensible, and ingres doesn't
>do that.)

Actually the contents are not logged, althought the modification is.  You
can confirm this using auditdb.  A HEAP page replaces the original table.

BTW, I can't see any reason why modification is quicker.  In effect, you
just give the drop table less table to remove (physically).  A drop will do
the same thing in one step.

Jon

 
 
 

1. Truncate table vs drop table and fragmentation

Greetings, Oracle experts.

I have an Oracle 7.3.4 installation I use for development. We've been doing
some longevity testing on our app, and in between runs I need to clean out
the database.

The easiest way for me to do this is to drop the tables and recreate them.
We have a DBA in the office, however, who tells me I'm an idiot for doing
this since I'll fragment the tablespaces, and that I should truncate the
tables instead. Unfortunately, he is unable to articulate why this would
cause fragmentation.

I haven't seen any performance degradation or encountered any problems with
creating new extents, so I'm a bit skeptical.

Can anyone explain this to me, or point me to the appropriate Oracle
documentation.

Thanks

--Doug

2. SQL 2000 on SBS 4.5

3. Drop Vs (Truncate & Drop)

4. SQL 60 Install

5. Drop table versus truncate and drop index

6. What type licence for internet use of sqlServer, etc

7. Problem with Drop Table and truncate table

8. Barebone Sybperl program to Compare Similar tables. on two different servers.

9. performance question (drop/create vs truncate/insert)

10. Truncate and insert vs drop and create

11. Truncate table vs delete table

12. truncate table vs delete table

13. Alter table Modify Column length, Drop Identity