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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
