On 2016/10/05 5:27 PM, Andrii Motsok wrote:
Date: Mon, 3 Oct 2016 16:25:09 +0200
Hi,
1) In case of ORDER BY if we choose ordering which does not follow "index
ordering" we will get performance degradation.
2) If we use ORDER BY can we be sure that order of rows is the same for two
subsequent calls (without any writes to database) for non UNIQUE index?
3) Just from curiosity - could you please provide any real world scenario which
shows how two similar subsequent calls can return different order of rows?
Regards,
Andrii
1) - How do you mean "follow index ordering"? If you order (Ascending or
descending) on fields that are contained in an index, you will get full
performance.
2) No you can't be sure, well, currently that should be the case because
the QP and sort orderer won't change algorithms between two sorts, but
this is a very unsafe assumption. Any DB update or insert or vacuum or
analyze might alter the row order produced where the ordering isn't
explicit.
3) We can show you what /might/ produce different row orders, but I
don't know how to show what will definitely guarantee a different order.
For the same reason it is unsafe to "assume" a static ordering, I can
also not "assume" a different ordering unless specifically asking the
sorter to adhere to another ordering. Point is, if you depend on the
order, you have to specify it precisely. You can always order by more
than 1 column, or even a function on a column. Why not simply Order by
your non-unique field AND then by row_id. This will guarantee the same
order.
SELECT * FROM t ORDER BY t.NonUniqueField, t.row_id
(That is, unless you are using WITHOUT ROWID tables, in which case use
the PK.)
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
