RE: drop table vs truncate table -- performance

From: <JayMiller_at_TDWaterhouse.com>
Date: Wed, 12 Mar 2003 10:49:50 -0800
Message-ID: <F001.00567E1C.20030312104950@fatcity.com>

Interesting question.

I'll throw out another guess that Oracle has extra checking to do to either retain the first extent or perhaps recreate it (whereas drop just gets rid of all extents)?

Comparing the results of a SQL Trace or Event 10046 on drop and truncate could be interesting.

Jay Miller

-----Original Message-----
Sent: Monday, March 10, 2003 5:21 PM
To: Multiple recipients of list ORACLE-L

Is it because, truncate has to reset the HWM?

GovindanK

• gmei <gmei_at_incyte.com> wrote: > Hi:
  > Oracle 8173 on Sun Solaris 2.8.
  > I am running a program that creates and drops tables
  > repeatedly in a schema.
  > The table dropping part looks like this:
  >
  > drop table ypd.t1 cascade constraints;
  > drop table ypd.t2 cascade constraints;
  > drop table ypd.t3 cascade constraints;
  > drop table ypd.t4 cascade constraints;
  > ....
  >
  > I thought I might be able to use "truncate table ..." and
  > then "drop table
  > ..." to make this process a bit faster. However the tests
  > I did today showed
  > otherwise:
  >
  > SQL> select bytes from dba_segments where
  > segment_name='SEQUENCE';
  >
  > BYTES
  > ----------
  > 1949810688
  >
  > SQL> create table ypd_owner.SEQUENCE1
  > 2 tablespace ypd nologging as select * from
  > mt.SEQUENCE;
  >
  > Table created.
  >
  > Elapsed: 00:04:272.50
  > SQL> drop table ypd_owner.SEQUENCE1;
  >
  > Table dropped.
  >
  > Elapsed: 00:00:05.19
  > SQL> create table ypd_owner.SEQUENCE1
  > 2 tablespace ypd nologging as select * from
  > mt.SEQUENCE;
  >
  > Table created.
  >
  > Elapsed: 00:04:275.85
  > SQL> truncate table ypd_owner.SEQUENCE1;
  >
  > Table truncated.
  >
  > Elapsed: 00:00:06.99
  > SQL> drop table ypd_owner.SEQUENCE1;
  >
  > Table dropped.
  >
  > Elapsed: 00:00:00.70
  >
  > -----------
  > Tablspace ypd is LMT. Similar test on smaller table
  > showed the same result
  > "drop table ..." is faster than "truncate table ...". Any
  > explanation?
  > TIA.
  >
  > Guang
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.net
  > --
  > Author: gmei
  > INET: gmei_at_incyte.com
  >
  > Fat City Network Services -- 858-538-5051
  > http://www.fatcity.com
  > San Diego, California -- Mailing list and web
  > hosting services
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an E-Mail
  > message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information (like
  > subscribing).
  >
  

  ---

Do You Yahoo!?
Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Govindan=20K?=
  INET: gkatteri_at_yahoo.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Mar 12 2003 - 12:49:50 CST