BTrees as per concept are aware of sorted load vs random load and will adjust their node splitting algorithm accordingly (e.g. 90/10 split for ordered and 50/50 for random load). The rationale being that an ordered load tends to indicate that new data is unlikely or added at the end, whereas a random load suggests that additional data will be added with in-between keys.
So it really depends on the order of adding records more than the presence or absence of a rowid. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Dominique Devienne Gesendet: Dienstag, 10. September 2019 16:50 An: SQLite mailing list <[email protected]> Betreff: [EXTERNAL] Re: [sqlite] insert: how to force application to provide value for int primary key? On Tue, Sep 10, 2019 at 4:32 PM R Smith <[email protected]> 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
