Subject Re: [firebird-support] Does added column cause all records to be touched or what happens?
Author Ann W. Harrison

> Hi,
> I'm a bit curious. I just added a nullable bigint column without a
> default value and an index on it to a table with about 150 million records.
> It's taking very long to commit, so I'm wondering what the server is
> actually doing. There's no criticism here, it's just something I find
> interesting.

What Firebird does when you add a nullable field is almost nothing. It
creates a new record in the rdb$formats table that describes the new
physical format of the record and a new format number for new records.
Old records stay in the old format until they are updated.

What's taking the time is creating an index on 150,000,000 records
all of which have exactly the same key value. I seriously doubt
that the index will be of any value to you, and now that it's created,
it will have a selectivity of 1.0 - as bad as it gets. When you do
put values in that field, you should at least reset the selectivity.
Though it sounds unkind, given how much work Firebird has put into
creating a worthless index, I suggest you drop it and recreate it
when you've actually got more than one value.