Kjell Rilbe wrote:
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.
Does it have to update all records to add a null value to the new column or if not, how is it handled instead?
I assume the new index is built completely. If not, how is the index handled instead?
Anything else going on in there?
Ann W. Harrison answers:
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.
Kjell Rilbe later continues:
Yes, I realized that once it had started executing - that the index should have been created a bit later in the process.
...and after having filled the new column with data and rebuilt the index, it now has selectivity 0.000022. :-) The update took 12 hours, probably because silly me forgot to deactivate the index before running the update... Live and learn!