Re: Truncate and insert vs drop and create

And, if I may add a d) to your list ... you won't drop indexes, constraints, triggers, and object privileges.

Daniel Morgan

Paul Brewer wrote:

> 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
> >
> >
Received on Wed Jan 16 2002 - 17:28:55 CST