| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: [Q]What is going on?
Truncate will delete the data and release tablespace on extents, but will it release the tablespace allocated to the INITIAL extent ? If the table only has one large initial extent, drop may be the only way to recover the table- space blocks for subsequent use.
It does not matter if you are going to drop the table.
Generally, one does not like to drop tables in an active system as there are synonyms and grants and views that are reference the tables.
Truncate remove the data and release the table space but does not remove the grants, etc. It does not use rollback so is faster than delete which does.
Mike Krolewski
Allan Webster wrote:
> Truncate does not use rollback (& can therefore not be recovered from),
> so is much quicker.
>
> There is nothing to be gained by deleting before truncating, (except
> perhaps a longer tea -break), & if you are going to drop the table
> anyway, why bother doing either?
>
> Rgds
> Allan
>
> > ----------
> > From: andreas.prusch_at_sintec.de[SMTP:andreas.prusch_at_sintec.de]
> > Posted At: Tuesday, May 26, 1998 3:19 PM
> > Posted To: server
> > Conversation: [Q]What is going on?
> > Subject: Re: [Q]What is going on?
> >
> > We have the same experience in our environment. I think that
> > the block clean out is responsable for the long time. But i don't know
> > exactly what it is. My interpretation:
> > All the blocks touched by the delete are dirty because the database
> > has
> > made transaction entries in the blocks. Now on truncate the database
> > writes the blocks away and then truncate the table. Ok, thats my
> > interpretation.
> >
> > I'm looking for answers, too.
> >
> > Andreas Prusch
> >
> > In article <6kcun0$qug$1_at_gte2.gte.net>,
> > sender wrote:
> > >
> > > Have you ever had the following experience? (if not, please try it).
> > > Process A (do the following, step by step,in sqlplus):
> > > 1) create table T_1 as select * from Large_table;
> > > -- Large_table contains about 120000 rows,
> > > -- each rows is about 1000 bytes.
> > > 2) optional (commit)
> > > 3) truncate table T_1;
> > > 4) drop table T_1;
> > > 5) commit;
> > > All things should be finished within a normal (acceptable) response
> > > time.
> > >
> > > Process B (do the following, step by step, in sqlplus):
> > > 1) create table T_1 as select * from Large_table;
> > > -- Large_table contains about 120000 rows,
> > > -- each rows is about 1000 bytes.
> > > 2) optional (commit)
> > > 3) set transaction use rollback segment Big_rollback_seg;
> > > 4) delete from T_1;
> > > 5) optional (commit or rollback)
> > > 6) truncate table T_1;
> > > 7) drop table T_1;
> > > 8) commit;
> > > At STEP 6), it will take a significant longer time to finish.
> > > In addition, other processes also take a hit with response time.
> > > For example, in Process C,
> > > drop table T_2 -- (where T_2 contains only one row)
> > > where you are doing truncate table T_1 in Process B.
> > >
> > > Please let me know whether it is true in your environment.
> > > More importantly, concrete explanation is appreciated. Thanks.
> > >
> >
> >
> > -----== Posted via Deja News, The Leader in Internet Discussion
> > ==-----
> > http://www.dejanews.com/ Now offering spam-free web-based
> > newsreading
> >
---------------End of Original Message----------------- ======================================
Work: (619)505-2868
Pager: (619)494-5539
E-mail: jim_pickett_at_cubic.com
06/16/1998 07:09:20
======================================
Received on Tue Jun 16 1998 - 10:09:19 CDT
 |
 |