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:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Dominique Devienne
Gesendet: Dienstag, 10. September 2019 16:50
An: SQLite mailing list
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 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
___
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
