Re: Truncate and insert vs drop and create

I use truncate before doing an import with ignore=y I have two problems I have yet overcome. 1. When re-enabling constraints any primary key indexes that are created will then have the same name as the constraint. Some of my databases have a constraints name such as sys3245134 and the associated index is properly named such as test_pk. After re-enabling the constraint the index will now be called sys3245134.

2. After re-enabling a primary key constraint the newly created index will have an enormous amount of extents. I think it gets the storage clause from the tablespace level?

Any comments?

Thanks,
Chris

In article <3C460CB7.BE754C71_at_exesolutions.com>, "Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote:
>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 23 2002 - 20:31:00 CST