Subject Re: [firebird-support] Large DB after batch import: index rebuild/set stats, backup/restore?
Author Ann W. Harrison
Kjell Rilbe wrote:
> We're preparing a database for an online system and have imported a
> large amount of data. The DB file is currently 42 gigabyte. There are
> two tables with over 110 million records and then some with about 1-2
> million records. Most other tables only have a few hundred or a few
> thousand records. No records are deleted.

If you had asked before you did the load, I would have said,
import with indexes off, then create the indexes.
> Page size is 4096.
> Now, after the batch import finishes, I assume the indexes could use
> with some "conditioning". But what's my best option?
> 1. Just set statistics on all of them?

> 2. Rebuild all indexes with deactivate/reactivate?

Will produce dense index pages - which may be good or bad,
depending on what you do next. If the database continues
to grow significantly across all index ranges, then having
some space in the index reduces future splits. If you've
got most of the data you expect to have, maximize packing
density to reduce the number of index reads.
> 3. Do a backup/restore cycle with gbak?

> 4. Other options?

Don't activate the indexes until the data is stored.
> Would you handle indexes differently depending on selectivity? For
> example, the "largest" table, with over 110 million records, has a
> primary key where key values are incremental (not always +1, but always
> +something). Is it better to leave that index without a rebuild?

The normal index loading does have an optimization when the page
to be split is the last one in the index. Normally, a split puts
half the data in each page. If the split is the last page in the
index, the new page has only the record that didn't fit. That
means that if you load in key order, the index will be created
> Also, what considerations should I make regarding page size? Maybe I
> should bump up the page size?

How deep are your indexes?

Good luck