@Simon, Sir I dont want to rearrange the data. I will try to explain more. All my rows have the exact same size. They will not differ in size. My problem is due to the fact that I use 64kB page size. My rows are exactly 8 Bytes + 4096 Bytes.
Now for the purpose of ease in calculation lets assume each row is exactly 4 KB. So one page stores 16 rows. Lets say 10 pages are in use and I have a total of 160 rows. Now I delete Rows 1-4 (total 4 rows) and I insert another 4 rows. What I wanted is that the space freed by the first 4 rows being deleted be used for the 4 new rows. This should be done without any re-arrangement of data (so no vacuum and no internal data rearrangement !). As far as I am aware if a page is marked as free, sqlite will first use the page to store new data. But since my page size is 64 KB, this will not be possible. Hence I am evaluating all options on this to optimize my storage space utilization. On Sat, Feb 8, 2014 at 10:21 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 8 Feb 2014, at 11:24am, Raheel Gupta <raheel...@gmail.com> wrote: > > > I dont want to repack the DB sir. > > When a page becomes free I want to make sure that page is used up first > and > > then new pages are created. > > Just to explain that this would be extremely inefficient because a new row > that you write to a database will not take up the same space as a row you > have deleted. > > While a database is in use it might use perhaps 100 pages for a particular > table. Almost every one of those pages will have a little space free: > anything from 1 byte to most of the page, depending on how much space each > row takes up. When writing a new row to a table, SQLite intelligently > figures out which existing page it can write the row to (or does it ? > someone who has read the source code can tell me I'm wrong and if it > searches for the 'best' page). > > What it won't do is rearrange existing pages so that they are used as much > as possible. That could be done whenever a row is deleted (including when > a row is replaced using UPDATE). But it would require a lot of checking, > processing, reading and writing, and this would slow SQLite down a great > deal for every DELETE and UPDATE operation. As an the top of my head > guess, individual operations could take unpredictable amounts of time since > most efficient packing could require any number of pages to be rewritten. > I don't know of any database system that works like this. > > So that's one thing that might make you want to use VACUUM. Even VACUUM > does not reclaim the maximum amount of space possible. Instead it prefers > to keep the data for a row together and rows in primary index order > together, to increase speeds > > There are also the auto_vacuum and incremental-vacuum PRAGMAs. However > they operate only on the level of pages: they will reap entire unused > pages, but not interfere with the packing of data within a page. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
