We are considering using rowids as a unique and stable ID for all records. I
know that in order to do this, we need to prevent recycling by using "primary
key autoincrement".
The concern is that over time, as records are added and removed, there will
appear large gaps in the rowids of a table. As I understand it, sqlite uses
bit-arrays internally for selections, and those bit-arrays represent a list of
rowids. If this is the case, then large gaps in rowids, will cause bit-arrays
to expand, and potentially slow down selections.
1. Do I understand this correctly?
2. If necessary, I can manually recycle rowids to fill in these gaps,
and maintain a separate UID that will never be re-used. For the sake of
selection performance, is this a better solution?
3. Assuming a record isn't removed, can I rely on a rowid to remain
stable? Or can it ever be changed by some event, for example a Vacuum? Do rowid
gaps ever get "compressed"?
Thanks
dw
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
