>>>>> Simon Slavin <slav...@bigfraud.org> writes:
>>>>> On 31 Dec 2011, at 4:56pm, Ivan Shmakov wrote:

 >> The integers could take up to 32 bits long, but I deem them likely
 >> to “cluster”, like, e. g.: 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 101, 101,
 >> 102, 102, 102, 103, 103, 768, 768, etc.  My guess is that such
 >> sequences should be quite compressible, but the point is that
 >> there'll only be a few such numbers per row, and millions of such
 >> rows in the table.

 > Thing is, an integer like 103 takes two bytes to store,

        Those were given as examples only.  The real values are going to
        use up to 28 bits (roughly, 4 octets) or so.

 > and one of those bytes is the byte that indicates what kind of value
 > is being stored.  So it's really only taking one byte per value.

        Is it?  I mean, if I know for sure that I'd only ever need, say,
        eight 32-bit integers per row, and pack them into a single BLOB
        instead of having them stored as proper INTEGER's, won't I be
        able to save a few bytes per row?  (For a performance penalty
        when accessing these fields.  Also, I'd have to forget about
        indexes on these, other than a UNIQUE one on the whole
        BLOB-coded tuple, I guess.)

        (With a few functions to unpack these values back out of BLOB's,
        I'd even be able to CREATE a suitable VIEW.  Or may be a VIRTUAL
        TABLE.)

 > And you can't improve on this by compressing individual values, only
 > by compressing at the table level.  And if you're compressing at
 > table level, making any changes is rather slow.

        For now, I'm interested mostly in INSERT's.  (And SELECT's, of
        course.)

 >> [snip]is there a way to determine the filesystem space occupied by a
 >> particular table, or index, in SQLite?  It now seems to me that the
 >> problem is different to what I've initially presumed.

 > Not inside SQLite's API functions.  You can do it by reading the file
 > format and multiplying the page size by how many pages are used.

        ACK, thanks.  Do I understand it correctly that the
        sqlite-analyzer (suggested elsewhere in this thread) does this
        very thing?

        TIA.

-- 
FSF associate member #7257

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to