On 2014/02/09 12:06, Raheel Gupta wrote:
Hi,
Sir, I have only one auto increment primary key.
Since the new rows will always have a higher number will the pages
which have some free space by deleting rows with lower numbered keys never
be reused ? e.g. If row with ROWID "1" was deleted and freed, will it not
be used to store the NEW row which will be assigned ROWID 10001 ?
Yes. That is the point of AutoIncrement, every new Key will always be higher than any previous key ever used, and always exactly one
higher than the highest ever previously used key. As such, it cannot be re-used within pages that are half filled from deletion
(except maybe the last page), and I believe pages that go completely empty may be re-used without the need to vacuum etc. (need
someone to confirm this).
I think if you have 2 columns, one with Autoinc Integer and another one with rowid alias as primary key (but not auto-incremented)
you can get page re-using... but it will still be subject to many factors and never really mimic a full re-using system as you
really want.
You could of course make the keys yourself, no need to leave it up to the DB to autoincrement them, which might be a cheap solution
to the problem, but I would still much more favour separate file(s) with byte data alongside an SQLite indexing DB - it would be the
least work with highest guarantee of working flawlessly.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
