On Tue, Sep 10, 2019 at 4:32 PM R Smith <ryansmit...@gmail.com> wrote:
> > So "fat" tables, with large rows, and lost of inserts/updates, is > basically > > the worse case > > scenario for such WITHOUT ROWID tables. It works, no issue there, and as > > typical of > > SQLite is often fast enough for most DB sizes, but it *can* matter. Just > be > > aware of it. > > That is interesting - could you elaborate on how exactly lots of inserts > would be worse in WITHOUT_ROWID tables than in normal tables?* > WITHOUT ROWID tables have a "real" natural key, which as such is much more likely to have a random distribution, resulting in splicing new rows all over the place. While regular tables typically have an auto-incrementing ROWID, acting as the B-Tree key, which means new rows are mostly inserted "at the end", in the last page, yielding fewer non-leaf page rewrites I'm guessing. You're probably right to challenge what I wrote. It's mostly intuition, not hard-facts, so I could well be completely off-base. I may also have read the above when I looked into Oracle IOTs (Index Organized Tables), which are similar I think (again, perhaps I'm wrong). I guess one would need to run experiments with a shim VFS to track IO to verify my claims above :). For now, just take it with a grain of salt or just plain assume it was talking out of my a..! --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
