Re: Truncate and insert vs drop and create

It was drop, rebuild and RELOAD vs. truncate and RELOAD.

-Kevin

"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message news:3C46E14A.45F01B4E_at_exesolutions.com...
> 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 Fri Jan 18 2002 - 09:22:15 CST