On Thu, Oct 29, 2015 at 7:32 PM, Wade, William <bill.wade at dnvgl.com> wrote:
> From https://www.sqlite.org/fileformat2.html, if a row does not fit into > a single block, its overflow is stored in a linked list. The implication > (to me) is that if a particular row requires twenty blocks of storage, and > I want to read a one-byte field that is in the twentieth block, all twenty > blocks will be read. > > Clearly it would be possible to do better even without switching to a > column-major layout (instead use a record-internal b-tree mapping > column-number-for-this-record to page number of the overflow block) but I > don't see any documented evidence that sqlite does that. For some > applications it would certainly be nice. > But that would require a format change, which the SQLite team is not willing to make IMHO. > It would also be nice to get something closer to random access into large > blobs (and the same technique works there). > I've discussed blobs a few times already :). Especially the fact you can't have transactional and incremental updates of blobs (i.e. the transaction "copies" only the blob pages actually modified). This would require a blob-index, and again that's a format change. There is a vacuum mode each tracks a bit more info about pages I vaguely remember, with special pages, that helps a little, but I don't recall the details. --DD
