Subject Re: [firebird-support] Adding index befor or after filling table?
Author Thomas Steinmaurer
> 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

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