Thanks for the detailed explanation. I understand that I should not make assumptions about SQL engine internals for typical database usage.
I now also understand that by not using the ORDER BY clause, SQLite might one day decide to execute query plans that disrupt the order that I induce from the index that is traversed. But, basically, I use SQLite as a file format that allows me to access (ranges of) persistent BLOBs in O(log N) disk time. I require the B-tree functionality, and very little of the SQL functionality. The reason why I am reluctant to use the ORDER BY clause is that I absolutely want to exclude the possibility that all rows must be read into memory before they can be sorted and iterated. But I will add the ORDER BY clause, as it doesn't make a performance impact for me in the current version, and hope the query planner in future versions behaves the same :) -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: dinsdag 3 maart 2015 12:20 To: General Discussion of SQLite Database Subject: Re: [sqlite] full table scan ignores PK sort order? On 3 Mar 2015, at 10:59am, Jean-Christophe Deschamps <jcd at antichoc.net> wrote: > An SQL engine doesn't guarantee any row "order" unless you explicitely force > an ORDER BY clause. Think of row order as random, where rowid order is just a > possibility among zillions others. Of course neither SQLite nor other engines > willingly use random() to foil your expectations but you should never rely on > such an implementation detail. > > Also rowids are technically independant of insertion order: you may feed any > valid random literal rowids at insert time. Just to formalise this ... SQL defines a table as a set of rows. There is no order to a set: it's just a jumble of things like Scrabble tiles in a bag. You can't tell what order rows were added in. The most frequent error SQL users make is to assume that table rows are inherently ordered in primary key order. They're not. The primary key is just another unique index. To help users avoid incorrect assumptions about an inherent 'order of rows' SQLite provides PRAGMA reverse_unordered_selects = ON which can be useful for testing code which was hacked up in a hurry. Simon. _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
