Re: Truncate and insert vs drop and create

"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 - 01:47:57 CST