> Den 2011-08-10 12:52 skrev Thomas Steinmaurer såhär:
>> > We're going to publish data from a large database into a smaller one.
>> > Primary keys and othe rindexed values will probably be inserted in
>> > random order.
>> >
>> > Is it more efficient to add indexes/pk:s after the insert than before?
>>
>> Yes.
>
> Thanks. To separate DDL and DML in my code I'd still like to create
> indexes where I create the tables. So, excepting PK:s, I may want to
> just deactivate indexes while pumping the data and the reactivate them.

You can't deactivate the underlaying index of a primary key constraint.

> Will this have performance as if I deferred index creation to after
> pumping the data?

Activating an index rebuilds the index.

> I think I can live with the performance hit of having the PK index
> active during data pump. The PK:s are bigints. Is this a bad idea? I
> think there will be 5-10 million inserts in total across all tables, the
> largest three tables containing about 1 million records each.

It would be good if you re-calculate the selectivity after the import
for those indices, which have been active during the import. I have a
stored procedure in all my databases which execute a SET STATISTIC on
all indices.


--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!