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


> I recall that if indexed values are inserted in increasing order, the
> index maintenance is rather efficient and perhaps even more efficient
> than adding the index afterwards, but if values appear in random order...?

Index creation is slower on random values like on GUID columns compared
to primary key column using an incremental integer.

> Also, if I add the index afterwards, will its stats be up to date
> automatically, or should I issue set statistics commands for each one
> after creation? (I assume it's done automatically).

No. The selectivity is up-to-date after creating the index.

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!