[Q]What is going on?

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
>
Received on Tue Jun 02 1998 - 11:38:51 CDT