Re: Truncate and insert vs drop and create

The question was never one of delete vs truncate. It was drop and rebuild the table vs truncate. Both are DDL; not DML. Daniel Morgan

Slarti wrote:

> "Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message news:<3c45fc92$1_1_at_mk-nntp-1.news.uk.worldonline.com>...
> > I agree; on performance probably not much difference.
> >
> > I'd go for the truncate, on these grounds:
> >
> > a) You won't invalidate any objects upon which this new table may depend
> >
> > b) If structure of y has changed, you'll get an error, thus you will be
> > made aware of any app changes which need to be made.
> >
> > c) Fewer changes in the metadata ("data dictionary" in the SYS schema)
> >
> > If performace is an issue, you can of course use the unrecoverable clause in
> > the create table, or the append hint in the insert after truncate...
> >
> > By the way, thanks for not cross-posting!
> >
> > Good luck...
> >
> > Paul
> >
> >
> >
> >
> > "Philip Morrow" <cracker_at_mymorrow.com> wrote in message
> > news:6Yl18.11711$_w.1271779_at_typhoon.tampabay.rr.com...
> > > Which is the better way to empty a table and reload data.
> > >
> > > truncate, then use insert into x select ..... from y
> > >
> > > or is it better to
> > >
> > > drop table, then create table x as select ... from y???
> > >
> > > I'm just wondering which is faster. I've tried both when putting about
> > > 24000 records into a table and I couldn't see much of a difference.
> > >
> > > Thanks
> > > Phil
> > >
> > >
>
> I can not agree to your "...on performance probably not much
> difference" completely. This may be while operating on less data this
> case, but if you try mass data deletion, you'll get into some
> performance trouble; in this case TRUNCATE could help. While deleting
> tables, Oracle will do Redo-Log-Entries and you'll have to do an
> explicit COMMIT; using TRUNCATE won't create any entries in Redo-Logs,
> therefore there is no way back. Realize this!
>
> That's the difference where performance comes from and i would like to
> add a d) to your list, Paul ;-)
>
> But doing this way does not mean "better", it's simply another aspect.
Received on Thu Jan 17 2002 - 08:35:55 CST