On 9 Feb 2014, at 10:45am, RSmith <rsm...@rsweb.co.za> wrote: > On 2014/02/09 12:06, Raheel Gupta wrote: >> 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).
You are correct, depending on this PRAGMA: <http://www.sqlite.org/pragma.html#pragma_auto_vacuum> auto_vacuum = NONE A page which has all its data deleted is added to the 'free pages' list and eventually reused. auto_vacuum = FULL A page which has all its data deleted is replaced by the last page of the file. The file is then truncated to release the space of the last page for use in other files. auto_vacuum = INCREMENTAL A page which has all its data deleted is replaced by the last used page of the file. When you issue "PRAGMA incremental_vacuum(N)" the file is truncated to release unused pages at the end for use in other files. As in previous discussion, all this is about reclaiming space at the page level: releasing entire pages of space. It has nothing to do with reclaiming space within a page. And also as in previous discussion, the fastest of these is "auto_vacuum = NONE". Copying one page to another, releasing filespace and claiming it back again are slow and require much reading and writing. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
